XML question..

[CrystalSurfer]

Can Access produce an XML file similar to the following example?

<Company>
	<registrationNo>178560</registrationNo>
	<tradingName>Widgets Ltd</tradingName>
	<SICCodeList>
		<SICCode>55.50</SICCode>
		<SICCode>55.60</SICCode>
		<SICCode>55.70</SICCode>
	</SICCodeList>
</Company>

where 'Company' is a table and 'SICCodeList' as a related table that has CompanyID as a foreign key. ie: Company can have many SICCodes.

I do not want the actual keys showing up, just the values.
I have tried the normal export as XML file in Access and programatically using the ExportXML method (incl. CreateAdditionalData) but I cannot get it to produce to this format.

I am beginning to think that Access cannot do it as I have exhausted every possible combination I can think of.

 

[John.Woody]

You could try using the print #1 command and programmically create and then add the text to the file. When you create it give it a .xml file extension.

Open "FileName.xml" For Output As #1 ' creates the file
Print #1 "your text" ' for each line you want to add
close #1 ' closes the file

hth
John

 

[CrystalSurfer]

Thanks for that John.
I did suspect that that would be a possible solution although I really don't want to go down that route as I'm new to VBA and we need a quick result (by 31st March!).
The data I've posted is the least of it, since we need to include multiple customer records with multiple 'embedded' detail records for each. Access unfortunatley, includes the table name AND key value for EACH related record in the XML and this is not what I want.

Thanks.

 

[John.Woody]

you can write what you want using the above method by building your information into a string i.e
Print #1 strMyString ' for each line you want to add

I doubt you're going to get the output you want without using VBA though.