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.

 

[jel]

SQL2000 can do a lot.
Check this out:

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

 

[jel]

Of course, you can do it yourself programmatically.
If you use ADODB to get all your data in 1 recordset (select * from parent join child on parent.PKfield = child.FKfield)
you could load the data from recordset directly into a MSXML.DOMDocument. Use
RS.Save objDOMdocument, adPersistXML
to do this.
Then transform it in any structure you like with xsl.
I wrote (just for practice) the xsl that you could use:

<xsl:stylesheet version=&quot;1.0&quot; xmlns:xsl=&quot;

http://www.w3.org/1999/XSL/Transform&quot;

xmlns:s=&quot;uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882&quot; xmlns:dt=&quot;uuid:C2F41010-65B3-11d1-A29F-00AA00C14882&quot; xmlns:rs=&quot;urn:schemas-microsoft-com:rowset&quot; xmlns:z=&quot;#RowsetSchema&quot;>
<xslutput method=&quot;xml&quot; version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; indent=&quot;yes&quot;/>

<xsl:template match=&quot;/&quot;>
<xsl:apply-templates/>
</xsl:template>

<xsl:template match=&quot;rs:data&quot;>
<xsl:element name=&quot;rootname&quot;>
<xsl:attribute name=&quot;numberrows&quot;>
<xsl:value-of select=&quot;count(z:row[not (@PKfield=preceding-sibling::z:row/@PKfield)])&quot;/>
</xsl:attribute>
<xsl:for-each select=&quot;z:row[not (@PKfield = preceding-sibling::z:row/@PKfield)]&quot;>
<xsl:call-template name=&quot;makenodes&quot;>
<xsl:with-param name=&quot;key&quot; select=&quot;@PKfield&quot;/>
</xsl:call-template>
</xsl:for-each>
</xsl:element>
</xsl:template>

<xsl:template name=&quot;makenodes&quot;>
<xslaram name=&quot;key&quot;/>
<xsl:element name=&quot;parentname&quot;>
<xsl:element name=&quot;PKfieldname&quot;>
<xsl:value-of select=&quot;@PKfield&quot;/>
</xsl:element>
<!-- same for all parent-fields -->
<xsl:for-each select=&quot;../z:row[@PKfield = $key]&quot;>
<xsl:element name=&quot;childfieldname&quot;>
<xsl:value-of select=&quot;@childfield&quot;/>
</xsl:element>
<!-- same for all child-fields -->
</xsl:for-each>
</xsl:element>
</xsl:template>

</xsl:stylesheet>

 

[jel]

And ok, why not, you can just write VBA in Access to write text to file. Reference to Microsoft Scripting Runtime. (i'm kinda stuck with my own work...)

Public Sub ExportXMLstring(ByVal strFileName As String)

Dim FSO As Scripting.FileSystemObject
Dim File As Scripting.TextStream
Dim rsParent As Recordset
Dim rsChild As Recordset

Set FSO = New Scripting.FileSystemObject
Set File = FSO.CreateTextFile(strFileName)
Set rsParent = CurrentDb.OpenRecordset(&quot;SELECT * FROM Parent&quot;, DB_READONLY)
rsParent.MoveLast
File.WriteLine &quot;<root rownumbers = &quot;&quot;&quot; & rsParent.RecordCount & &quot;&quot;&quot;>&quot;
Do Until rsParent.BOF
File.WriteLine vbTab & &quot;<parentrecord>&quot;
File.WriteLine vbTab & vbTab & &quot;<parentfieldname>&quot; & rsParent!Field & &quot;</parentfieldname>&quot;
Set rsChild = CurrentDb.OpenRecordset(&quot;SELECT * FROM Child WHERE Child.FK = &quot; & rsParent!PK, DB_READONLY)
Do Until rsChild.EOF
File.WriteLine vbTab & vbTab & &quot;<childrecord>&quot;
File.WriteLine vbTab & vbTab & vbTab & &quot;<childfieldname>&quot; & rsChild!Field & &quot;</childfieldname>&quot;
File.WriteLine vbTab & vbTab & &quot;</childrecord>&quot;
rsChild.MoveNext
Loop
File.WriteLine vbTab & &quot;</parentrecord>&quot;
rsParent.MovePrevious
Loop
File.WriteLine &quot;</root>&quot;

File.Close
rsParent.Close
rsChild.Close

Set File = Nothing
Set FSO = Nothing
Set rsParent = Nothing
Set rsChild = Nothing
End Sub

 

[Ross1]

Thanks jel

This is my first forray into using XML and the article you pointed me to was most helpful.

Have a great day and God bless.
Ross

 

[Ross1]

By the way 'jel', do you recall what you searched for when you discovered this article?
I tried to search msdn before I posted my question, but I was probably not using the appropriate XML terminology.

Thanks again,
Ross

 

[jel]

Uh sorry, something like SQL2000 + xml I guess...
I bookmarked it some time ago, daydreaming about how easy things would be if I didn't have to implement the other solutions I posted...

 

[Ross1]

that's affirmative on the difficulty of the other solutions you posted

 

[jel]

Actually, I've been working for some time on an app that implements both program-based solutions (and a few more, which I dare not post for fear of being banned for using obscene language).
The coding itself isn't the difficult part, you can easily code a routine or stylesheet that parses any field.
However, all hell breaks loose if you want to add relationships, it seems impossible to use one simple generic routine.
The closest I came was: parse parent and child-table seperatly using one of these methods, then write one (generic) procedure to add childnodes to parentnodes using two MSXML.DOMdocuments. That works, but you can understand that I'm not too happy about it.