export nested XML file from access. need XML file with nodes [duplicate]

Question

I need to create an XML file from Access. It has to have a relational node type format.

For instance:

<Item>
   <description></description>
   <colors>
         <blue>
         <green>
   </colors>
</item>

The data for the items is in a table. The colors are in another. I have reference IDs so I can join them.

How/can this be done. I have looked all over and see how to export a table, but not the nested type of file.

Answer 1

Create a SELECT query which uses the reference ID to join your items table with the colors table.

Once you have a query which gathers the information correctly, export its data to XML from the Access user interface.

If that gives you the XML you want, you can automate the export operation from VBA using the Application.ExportXML Method. Notice that method offers a host of options to tweak the XML. But the export might be as simple as this ...

Application.ExportXML acExportQuery, "YourQuery", _
    "C:\SomeFolder\YourQuery.xml"

Answer 2

Below is a sample of what I use to query for data and then export the results to a flat file. I have adapted it somewhat to what you may need.

 On Error GoTo Err_My_Click
 Dim rs As DAO.Recordset
 Set rs = CurrentDb.OpenRecordset("SELECT * FROM MyTable", dbOpenDynaset)

 If (rs.RecordCount <> 0) Then
   rs.MoveFirst

   Open "C:\Export\XML__MyFile.xml" For Output As #1

   Do While rs.EOF = False
     TempExportCount = TempExportCount + 1

     Print #1, "<Item>"
     Print #1, "    <description>" & rs.Fields("[Description]").value & "</description>"
     Print #1, "</Item>"

     rs.MoveNext
   Loop
 End If

Exit_My_Click:
  On Error Resume Next
  rs.Close
  Set rs = Nothing
  Close 1#
  Exit Sub
Err_My_Click:
  If (Err.Number = 76) Then
    MsgBox ("The program could not save the txt file." & vbNewLine & vbNewLine & _
            "Make sure you have the following folder created: C:\Export\")
  Else
    MsgBox (Err.Description)
  End If
  Resume Exit_My_Click