Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    XML Primer for Access neophyte

    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!

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  3. The Following User Says Thank You to johnhutchison For This Useful Post:

    kwvh (2011-09-12)

  4. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Have you tried Application.ExportXML?

  5. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by patt View Post
    Have you tried Application.ExportXML?
    Patt
    My understanding is the Application.ExportXML will only output one item at a time.
    Regards
    John



  6. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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:
    <tmpHeader>
    <File_x0020_Transmission_x0020_Date>20110912</File_x0020_Transmission_x0020_Date>
    <File_x0020_Transmission_x0020_ID>D_F110912</File_x0020_Transmission_x0020_ID>
    <CountOfPatient_x0020_ID>8630</CountOfPatient_x0020_ID>
    <XML_x0020_File_x0020_Version>v2.3</XML_x0020_File_x0020_Version>
    </tmpHeader>



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

    Thanks!
    Ken
    Last edited by kwvh; 2011-09-12 at 19:15.

  7. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    From what i understand from the Help on the ExportXML command you can specify 2 tables.

  8. The Following User Says Thank You to patt For This Useful Post:

    kwvh (2011-09-12)

  9. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by patt View Post
    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:
         No code has to be inserted here.
    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
    Last edited by johnhutchison; 2011-09-12 at 19:53. Reason: Code tag did not work as expected first time
    Regards
    John



  10. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    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.

  11. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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?
    Regards
    John



  12. #10
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •