XML Primer for Access neophyte

Topic

New Reply

I am trying to create a single xml file with two tables as the source. One table has a single record, and the other table has lots of records. The table with the single record is to be the header record for the xml file, containing info about the data records in the second table. For example the header record will contain the number of records in the file, date and time created, and a couple of other fields.

I can create xml files separately successfully, but I can’t figure out how to combine them into a single xml file. If anyone knows, or know of good resource for me to research, I would be very appreciative.

Thanks!

Reply | Quote

Replies

How do you currently create the separate xml files?

I would do this job by writing to a text file using the Print Command.
Open a recordset that will provide the header, then write out its contents wrapped in XML tags.
Then open another recordset and loop thorugh all its records.

Here is a brief overview of File Input/Output.

Reply | Quote

Have you tried Application.ExportXML?

Reply | Quote

Have you tried Application.ExportXML?

Patt
My understanding is the Application.ExportXML will only output one item at a time.

Reply | Quote

John,

I have read the kewl File info, and have started playing with the code. However, I need to dim the intOutFile. What type of variable should it be?

thanks!

Ken

Reply | Quote

I use the ExportXML command in VBA to create the two files. Is there a way to find a specific line in a text file using the File Input/Output you mention? If so, I could create the large xml file of data using the ExportXML, and then just insert the necessary 6 lines below into that file. But that assumes I can move around within the text file.

Code:

20110912
D_F110912
8630
v2.3

I will read the File I/O link and see what shakes.

Thanks!
Ken

Reply | Quote

From what i understand from the Help on the ExportXML command you can specify 2 tables.

Reply | Quote

From what i understand from the Help on the ExportXML command you can specify 2 tables.

I have never used it, but looking again I see that you specify a primary table, but then you can add additional tables. Here is the example from Help.
You would have to try it out to see what the results looked like.

Code:

     [TABLE]
[TR]
[TD=”colspan: 2″][/TD]
[/TR]
[/TABLE]
Sub ExportCustomerOrderData()     
   Dim objOrderInfo As AdditionalData     
    Dim objOrderDetailsInfo As AdditionalData     
  Set objOrderInfo = Application.CreateAdditionalData    
‘ Add the Orders and Order Details tables to the data to be exported.     
  Set objOrderDetailsInfo = objOrderInfo.Add(“Orders”)     
  objOrderDetailsInfo.Add “Order Details”    
 ‘ Export the contents of the Customers table. The Orders and Order      
‘ Details tables will be included in the XML file.      
  Application.ExportXML ObjectType:=acExportTable, DataSource:=”Customers”, _                          
   DataTarget:=”Customer Orders.xml”, _                          
   AdditionalData:=objOrderInfo  
End Sub

Reply | Quote

That’s right John, i have coded it before in VBA using Print# but then found that ExportXML does the same thing. I tried it once in Northwinds and it does as it says.

Reply | Quote

Do the tables need to be related for this to work?

In this specific example, where a table with just one record is to become a Header in the XML it seems quite likely that there is no relationship between that table in the detail records. But I am just guessing.

How does Access decide what XML tags to put around the data?

Reply | Quote

Patt and John,

THANKS! ! ! Yes, you are able to “Add” multiple tables into the same XML file. Very sweet. I seem to always look for and find the hard way of doing things. That is why this is such a great place!

Thanks to both of you.

Ken

Reply | Quote