Must Export from Access to XML and Include One-To-Many Relationships

[Ross1]

I need to export data from an Access (or SQL Server) database into an XML file with very specific syntax.
The reason for the exact syntax is that the folks receiving the XML file will use it to upload data into their system and the syntax must match exactly.

I have a table called FD. FD stands for Facilities Data, and this is the parent, or one side, of a one-to-many. I also have a table call ProductTypes, which contains the products that can be related to each facility in the table FD. Each Facility in FD can have one or more products in the ProductTypes table.

I need to export this data into XML and it needs to look just like this:
<?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?>
<FacilityData BranchCode=&quot;ANT&quot; NumberRows=&quot;1&quot; ExportDate=&quot;2003-01-01&quot;>
<FD>
<FacilityID>fac12345</FacilityID>
...
<ProductTypeList>
<ProductType>LVNR</ProductType>
<ProductType>LVRV</ProductType>
<ProductType>LFNR</ProductType>
<ProductType>ABCD</ProductType>
</ProductTypeList>
...
</FD>
</FacilityData>

How do I setup my data in ACCESS to get the ProductType tags to nest under the ProductTypeList tags (as shown above) when I export it?

If necessary, I can move my data over to SQLServer2000 if that would make this transfer easier.

Thanks for any help you can provide.

 

[Bastien]

I would write a procedure in VBA to open the db and create the file on the fly...You can create the basic query and then walk thru the result set and write out what you need to the XML file as you go

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[Ross1]

Thanks, but I think I'm going to upsize to SQLServer2000 and use a method described in this article.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexxml/html/xml07162001.asp

I was already snatching some of my data off of an SQLServer2000 database anyway.

This reference was supplied to me by 'jel' on the XML forum.

Thanks 'jel'