Output Access Table to XML with node

Question

I'm trying to output data from an Access table as XML.

The output looks like example A, what is required looks like example B.

My current code to do this is shown below at example C

If anyone could help it would be greatly appreciated.

Example A:

<inlineData inlineDataId="1">
   <date>24 AUG, 2009</date>
   <regis>123456</regis>
   <Field15>10000</Field15>
   <Field17>10000</Field17>
   <Field19>0</Field19>
   <Field21>0</Field21>
   <rego1>123123</rego1>
   <rego2>123124</rego2>
   <rego3>123125</rego3>
   <rego4/>
   <rego5/>
</inlineData>

Example B:

<inlineData inlineDataId="1">
   <date>24 AUG, 2009</date>
   <regis>123456</regis>
   <Field15>10000</Field15>
   <Field17>10000</Field17>
   <Field19>0</Field19>
   <Field21>0</Field21>
   <vehOwned>
     <rego1>123123</rego1>
     <rego2>123124</rego2>
     <rego3>123125</rego3>
     <rego4/>
     <rego5/>
   </vehOwned>
</inlineData>

Example C:

''// next we go through each record in the recordset, these elements are named 
''// according to the field name.  
''// note Nz(varItem.Value, "-") in the code - null values here can 
''// cause problems on occasion...  
rs.MoveFirst  
While rs.EOF = False  
    Dim counter  
    counter = 1  
    Do While Not rs.EOF  
        strXML = strXML & _  
                 "<dataSource>" & vbCrLf & _  
                 "<" & strRoot & counter & "'>" & vbCrLf  
        For Each varItem In rs.Fields  
            strXML = strXML & " <" & varItem.Name & ">" & _
            Trim(Nz(varItem.Value, "")) & "</" & varItem.Name & ">" & vbCrLf  
        Next varItem  
        strXML = strXML & "</inlineData>" & "</dataSource>" & vbCrLf  
        rs.MoveNext 
        counter = counter + 1  
    Loop  
Wend

Answer 1

From what I can tell, your code will not produce the results in either A or B since it doesn't handle empty (NULL) fields the way they're displayed in the examples you gave.

In any event, you will not be able to produce the results you want simply by looping through the fields in the records set because some of those fields are grouped together as nodes of a larger structure. Instead, you will need to separately write out the each field, open and closing parent nodes where necessary.

Something like:

while not rs.EOF

    strXML = strXML + "<field21>" + rs!Field21.Value + "</field21>" + vbCRLF
    strXML = strXML + "<vehOwned>"
    strXML = strXML + "<rego1>" + rs!rego1.Value + "</rego1>" + vbCRLF
    ... more fields ...
    strXML = strXML + "</vehOwned>"

wend

By the way, Windows makes available libraries that allow you to create the XML document by adding nodes, not just writing text, which will guarantee the document is at least well formed when you write it to disk.

Finally, there are rules in XML about how to represent, for instance, dates, and the examples you gave don't follow them.

Answer 2

You might like to consider:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adPersistXML = 1

Dim strCon, cn, rs

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
strCon = "Provider= Microsoft.Jet.OLEDB.4.0; Data Source=" & "C:\Docs\LTD.mdb"

cn.Open strCon
rs.Open "Select * from Table1", cn, adOpenStatic, adLockOptimistic

If Not rs.EOF Then
    rs.MoveFirst
    rs.Save "C:\Docs\Table1.xml", adPersistXML
End If

rs.Close
cn.Close

From: http://wiki.lessthandot.com/index.php/Output%5FAccess%5F/%5FJet%5Fto%5FXML