XML import issue

[Coach3131]

This XML file does not appear to have any style information associated with it. The document tree is shown below. I am getting this error above in the browser. When I import into an Access table, it is not importing any data, but it is creating a field (franchise) that is 16 rows of blanks. Any advice to point an XML newbie in the right direction will be quite helpful, thanks. Add the http:\\ in front of it football5.myfantasyleague.com/2011/export?TYPE=rosters&L=18391&W=1.xml

 

[Galaxiom]

Your data is attribute-centric xml.
Access works with element-centric xml.

 

[Coach3131]

So how would I get that into an Access table?

 

[Galaxiom]

Exactly what should the table look like after import?

 

[Coach3131]

It should be:

Franchise, Player, and Player Status in this particular table... 3 fields

 

[Coach3131]

Looking for someone to point me in the right direction. I am not familiar with XML.

 

[spikepl]

see if this is useful: http://support.microsoft.com/kb/285329

 

[Galaxiom]

I would use a VBA procedure to open the file (eg as a TextStreamObject) and parse the text into a recordset. I think that would be easier than do the XSLT transform.

Basically you read the textfile one line at a time.
Check if the line starts with "<Franchise=" and if so store the subsequent number in a variable.

Else check if it starts "<Player id" and if so extract the two quoted values to two variables. Then write a new record to the recordset using the three variables.

Instead of the recordset you can use an sql INSERT command but the recordset would be faster.

I would also change the representation of the status element to a numeric value and use a lookup to display it in Access.

 

[Coach3131]

Thanks Galaxiom! I will look into it tonight/this weekend and see if I can get it set up. I have used VBA a bit with Excel so it is just a matter of learning the objects in Access.

 

[gemma-the-husky]

Personally I think you will struggle trying to treat a xml file as text

If you don't understand xlslt files, you can use the MSXML library to walk the xml tree, and process the nodes (ie each tag pair) manually. it will take a fair bit of work and research though. you need to use recursion to do this really, so it helps if you are comfortable with using recursion.

the point is that some tags have children, and others don't (leaf nodes). in general what you need is to walk the tree, and process all the leaf nodes.

 

[stopher]

Here's some code (tested) to import your xml file. The code only works for the structure you have provided. I have imported your ID fields as text but you can easily change that.

The code uses the MSXML library as suggested by Dave. So you need to add the Microsoft XML library to your references.

Dim xmlDoc As MSXML2.DOMDocument
Dim franchiseNode As MSXML2.IXMLDOMNode
Dim playerNode As MSXML2.IXMLDOMNode
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("rosters")
Set xmlDoc = New MSXML2.DOMDocument

With xmlDoc

    .Load "C:\temp\myXMLfile.xml"

    If .parseError.errorCode <> 0 Then
        MsgBox "Error loading: " & .parseError.reason
        'need to exit gracefully here
    End If
    
    For Each franchiseNode In .firstChild.childNodes
    
        For Each playerNode In franchiseNode.childNodes
            rs.AddNew
            rs!franchise = franchiseNode.Attributes.getNamedItem("id").Text
            rs!player = playerNode.Attributes.getNamedItem("id").Text
            rs!status = playerNode.Attributes.getNamedItem("status").Text
            rs.Update
        Next playerNode
        
    Next franchiseNode

End With

Set xmlDoc = Nothing
Set franchiseNode = Nothing
Set playerNode = Nothing
Set rs = Nothing

hth
Chris

 

[Galaxiom]

Personally I think you will struggle trying to treat a xml file as text.

In general I would agree but that particular file looked fairly simple. However it did occur to me later that the xml is only displayed as though it has one line per record. In reality I think it does not actually have line breaks so reading it as a text file would indeed be very clumsy.

Anyway Dave's suggestion as detailed by Chris is definitely the way to go.

I love this place. Always learning something new.

 

[gemma-the-husky]

out of interest, i put a code sample up some time ago - that analyses an xml file with msxml, to shows all the different nodes in the tree.

a problem that can occur with complex xml files, is if the same node tag is repeated in different places - eg - several <address>, or several <reference> tags, that depend on the parent tag(s) to determine exactly what data the node contains.

 

[gemma-the-husky]

Here's some code (tested) to import your xml file. The code only works for the structure you have provided. I have imported your ID fields as text but you can easily change that.

The code uses the MSXML library as suggested by Dave. So you need to add the Microsoft XML library to your references.

Dim xmlDoc As MSXML2.DOMDocument
Dim franchiseNode As MSXML2.IXMLDOMNode
Dim playerNode As MSXML2.IXMLDOMNode
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("rosters")
Set xmlDoc = New MSXML2.DOMDocument

With xmlDoc

    .Load "C:\temp\myXMLfile.xml"

    If .parseError.errorCode <> 0 Then
        MsgBox "Error loading: " & .parseError.reason
        'need to exit gracefully here
    End If
    
    For Each franchiseNode In .firstChild.childNodes
    
        For Each playerNode In franchiseNode.childNodes
            rs.AddNew
[COLOR="Red"]            
            rs!franchise = franchiseNode.Attributes.getNamedItem("id").Text
            rs!player = playerNode.Attributes.getNamedItem("id").Text
            rs!status = playerNode.Attributes.getNamedItem("status").Text[/COLOR]
            rs.Update
        Next playerNode
        
    Next franchiseNode

End With

Set xmlDoc = Nothing
Set franchiseNode = Nothing
Set playerNode = Nothing
Set rs = Nothing

hth
Chris

Chris

that's realy useful. When I was puzzling this out for myself, I struggled to dissect the attributes for a node. (and didn't do it in this simple way!)

It shows that you need to examine and understand the xml - because a normal tree walk, and examination of either node.xml or node.text doesn't pick up the attributes!

I struggle to understand the purpose of attributes to be honest - surely the data could just as easily be coded as subtags within the player tag - and then it wouldn't need complex coding to extract the data.

And I don't know how to write/use XLSLT files, so a primer on those would be very useful.

 

[Galaxiom]

I struggle to understand the purpose of attributes to be honest - surely the data could just as easily be coded as subtags within the player tag - and then it wouldn't need complex coding to extract the data.

That would make it Element Centric XML. I suspect the Attribute Centric form was an early attempt before they had thought about it enough.

I don't know how to write/use XLSLT files, so a primer on those would be very useful.

I did some work with XSLT a few years ago where I managed the set up of a business system that could export any report as element centric XML fed through an XSLT file. Nice.

It is a very simple, versatile, loop driven language structure with a substantial vocabulary that covers anything a developer could possibly want to say to transform the data.

We used it to generate custom header information for email filters, SQL to update the webshop database, pricelists etc. Basically you can make any text based output quite efficiently including scripts.

It supports references to the previous record which, as you know, is a pain in SQL.

I found the book "Sam's Teach Yourself XML in 24 Hours" was a good primer that went into a lot of detail.

 

[gemma-the-husky]

That would make it Element Centric XML. I suspect the Attribute Centric form was an early attempt before they had thought about it enough.



I did some work with XSLT a few years ago where I managed the set up of a business system that could export any report as element centric XML fed through an XSLT file. Nice.

It is a very simple, versatile, loop driven language structure with a substantial vocabulary that covers anything a developer could possibly want to say to transform the data.

We used it to generate custom header information for email filters, SQL to update the webshop database, pricelists etc. Basically you can make any text based output quite efficiently including scripts.

It supports references to the previous record which, as you know, is a pain in SQL.

I found the book "Sam's Teach Yourself XML in 24 Hours" was a good primer that went into a lot of detail.

thanks for that - something else to investigate when time permits!