Import XML file to ACCESS

You've tried ?

http://msdn2.microsoft.com/en-us/library/aa167823(office.11).aspx

I'm not sure I understand that document. It looks to me that I need a transform file in place first? How do I build that tranform file????

thanks!

Yea, that was my problem too. Sometimes MS documentation isn't as helpful as it should be. My problem is I can't test with you because I'm using A2000. I do have a question though....if you CAN import it using excel the way you want it, you can then import from excel? Sounds like a workaround, but if it works...I'm sure there are other Experts here that have more experience than I do directly importing XML...hopefully they'll join the thread.
J

Yeah if I can't figure out any other way I can always open the XML in excel and then import to Access. But I want to automate the whole process and would prefer to go straight to Access. Looks to me like the TransformXML method is what I want. I just need to understand how to build the datatransform (XSL) file to use.

Thanks for your help!! Maybe somebody else will show us both how to do it.

Well it's a holiday - so I might as well pop in.
Access is a relational database application development environment.
It's doing you the favour of importing your data as separate entities.  IMHO you should let it.
If, for whatever reason, you decide to then batch it all together in one table (I don't see the value in doing so) then you can run an action query on the resulting tables to do so.
Excel "flattens" the data because it has little choice.  (It *could* use separate sheets - but wouldn't be especially helpful).

You could, of course, parse the XML yourself.  Which is much more work - but you'd gain control.
However I really can't see much point in doing all that - only to dump it all into a single table.

Use code - like this simple function:

Public Function ImportXML()
  Application.ImportXML "d:\path\sample.xml", acStructureAndData
End Function

That will create four tables:

  event
  race
  runner
  line

This is runner from your sample data:
post      dog      starts      first      second      third      fourth
 1      A Bar Trey       16       2       3       0       1

/gustav

LPurvis - I would have no problem letting Access do it's thing. The problem is when Access does that there are no fields common in the tables that I can later JOIN on. I get tables for event,race,runner, and lines but without something to tie the tables together they are useless.

cactus_data --
That will create four tables:

  event
  race
  runner
  line

This is runner from your sample data:
post      dog      starts      first      second      third      fourth
 1      A Bar Trey       16       2       3       0       1

Yes I get 4 tables when I do a standard import of the XML into Access. But those 4 tables need to be JOINED in order to use the data. And there is no common field between them.

Re: "no fields common in the tables"

Yeah - I'd wondered if that might be the problem actually.
You're going to have to parse the XML yourself manually.
Can you provide a more full example of your XML?
Perhaps as an attachment file?
www.ee-stuff.com

LPurvis --- An example file is only about 700k, but I don't see where I can add attachments???

The above link - you log in with your EE UN and PW.
Then post the attachment at the URL of this question
https://www.experts-exchange.com/questions/22498730/Import-XML-file-to-ACCESS.html

Ahhhh, thanks for the info on attachments

https://filedb.experts-exchange.com/incoming/ee-stuff/3124-test.txt

I believe this is a limitation of the XML import function of Access:

<quote>
Access 2003 (or 2002 for that matter) does not create relationships between
tables when importing from XML. As Joe suggested, when importing arbitrary
XML, Access makes a best guess as to how many tables should be created from
the XML. When importing Access created XML, you should get back the exact
same table structure that was used to export to XML, but the relationships
will be lost.

Regards,
Mike Wachal
Microsoft Corporation
</quote>

Source:
http://groups.google.com/group/microsoft.public.access.externaldata/browse_thread/thread/f42d1eeacabc85a6/f7d4c92759799877%23f7d4c92759799877

There are many ways and tools to read XML data. One that might be of value for you is the free XML component from Chilkat:
http://www.chilkatsoft.com/xml-activex.asp

/gustav

First chance to look at this for a while (though to be fair I'm not here at all at the mo ;-)
If you add the tables to your db and then the appropriate PK's and FK's then you could go with code like the following.  (It could be split off into separate functions for greater readability and efficiency).

Sub ParseXML()

    Dim xmlDOM              As DOMDocument
    Dim objEvents           As IXMLDOMNodeList
    Dim objEvent            As IXMLDOMNode
    Dim objRaces            As IXMLDOMNodeList
    Dim objRace             As IXMLDOMNode
    Dim objRunners          As IXMLDOMNodeList
    Dim objRunner           As IXMLDOMNode
    Dim objLines            As IXMLDOMNodeList
    Dim objLine             As IXMLDOMNode
    Dim objEventDetail      As IXMLDOMNode
    Dim objRaceDetail       As IXMLDOMNode
    Dim objRunnerDetail     As IXMLDOMNode
    Dim objLineDetail       As IXMLDOMNode
    Dim db                  As DAO.Database
    Dim rstEvent            As DAO.Recordset
    Dim rstRace             As DAO.Recordset
    Dim rstRunner           As DAO.Recordset
    Dim rstLine             As DAO.Recordset
    Dim lngEvent            As Long
    Dim lngRace             As Long
    Dim lngRunner           As Long
   
    Set xmlDOM = New DOMDocument
    xmlDOM.Load CurrentProject.Path & "\Example XML.xml"
   
    Set objEvent = xmlDOM.documentElement 'Get event node
   
    Set db = CurrentDb
    Set rstEvent = db.OpenRecordset("event", dbOpenDynaset)
    Set rstRace = db.OpenRecordset("race", dbOpenDynaset)
    Set rstRunner = db.OpenRecordset("runner", dbOpenDynaset)
    Set rstLine = db.OpenRecordset("line", dbOpenDynaset)
   
    rstEvent.AddNew
        For Each objEventDetail In objEvent.childNodes
            If objEventDetail.nodeName <> "races" Then
                rstEvent.Fields(objEventDetail.nodeName) = objEventDetail.Text
            Else
                lngEvent = rstEvent.Fields("eventID")
                Set objRaces = objEventDetail.childNodes
                For Each objRace In objRaces
                    rstRace.AddNew
                        rstRace.Fields("eventID") = lngEvent
                        For Each objRaceDetail In objRace.childNodes
                            If objRaceDetail.nodeName <> "runners" Then
                                rstRace.Fields(objRaceDetail.nodeName) = objRaceDetail.Text
                            Else
                                lngRace = rstRace.Fields("raceID")
                                Set objRunners = objRaceDetail.childNodes
                                For Each objRunner In objRunners
                                    rstRunner.AddNew
                                        rstRunner.Fields("raceID") = lngRace
                                        For Each objRunnerDetail In objRunner.childNodes
                                            If objRunnerDetail.nodeName <> "lines" Then
                                                rstRunner.Fields(objRunnerDetail.nodeName) = objRunnerDetail.Text
                                            Else
                                                lngRunner = rstRunner.Fields("runnerID")
                                                Set objLines = objRunnerDetail.childNodes
                                                For Each objLine In objLines
                                                    rstLine.AddNew
                                                        rstLine.Fields("runnerID") = lngRunner
                                                        For Each objLineDetail In objLine.childNodes
                                                            rstLine.Fields(objLineDetail.nodeName) = objLineDetail.Text
                                                        Next
                                                    rstLine.Update
                                                Next
                                            End If
                                        Next
                                    rstRunner.Update
                                Next
                            End If
                        Next
                    rstRace.Update
                Next
            End If
        Next
    rstEvent.Update

   
exitHere:
    rstLine.Close
    rstRunner.Close
    rstRace.Close
    rstEvent.Close
   
End Sub

Example file using above.
https://filedb.experts-exchange.com/incoming/ee-stuff/3141-XMLImport.zip

But this requires that new fields for IDs have been appended to existing tables, and still no relations are created.

My understanding is, that the questioneer is looking for an automatic method that will read the XML file as is, create the tables, and establish relations as expressed by the tree structure of the XML file.

/gustav

LPurvis -- You got it !!!! Yes I could have created the tables myself before hand, but your code does everything for me!! I need to study it more so I understand how it works. With the relationships already created I will just create a query that joins the tables together in the form I need and append the data to my "historical" table.

Thanks again!!!

Welcome.  Glad you're sorted.

Leigh,
All that for free!! Wow...you'll get a reputation!