Solved

Unable to read the attributes of child nodes using XML DOM in Access 2003

Posted on 2013-05-27
2
625 Views
Last Modified: 2013-05-28
I have searched the web for perhaps 8 hours including Experts Exchange and Stackoverflow and have found others with the same issue but I've not found a solution to this issue.  

I'm attaching an XML file which uses Attributes to store the data in a parent node and three dependent child nodes.  All of the Attributes are uniquely named.  the XML opens in all browsers and I'm fairly certain that it is "well-formed".  I have successfully written code to read the attributes of the parent node but every time I attempt to write code to read the attributes of the child nodes, I get a run time error 91 or no error at all.  

<Donors>
- <Donor DonorID="34224" Email="tsmith@gmail.com" DonorFirstName="Tom" DonorMiddleName="" DonorLastName="Smith" DonorAddress1="2052 Main Street" DonorAddress2="" DonorCity="New York" DonorStateProv="New York" DonorPostalCode="10019" DonorPhoneHome="2125298624" DonorPhoneWork="" DonorPhoneWorkExt="" DonorPhoneMobile="" DonorEmailAlternate="">
- <PageTypes>
  <PageType OnlinePageType="Product Purchase" />
  </PageTypes>
- <Transaction>
  <Transactions TransactionID="194" CCTransactionID="-999" OrderTimeStamp="2013-05-24T07:16:37.333" OrderTotal="110.0000" />
  </Transaction>
- <Products>
  <Product ProductGroupName="First Pitch Tickets" ProductName="Single" ProductDescription="1 Ticket for $10" ClientCode="I000001351" ProductCount="1" TotalFees="0.0000" TotalAmount="10.0000" />
  <Product ProductGroupName="First Pitch Tickets" ProductName="12 Tickets" ProductDescription="12 tickets for $100" ClientCode="I000001352" ProductCount="1" TotalFees="0.0000" TotalAmount="100.0000" />
  </Products>
  </Donor>
</Donors>

I've tried MANY permutations of the following code without success.  I welcome any suggestions on how to cycle through this XML so that I can process and store the data into two related tables.  

Function ReadAttributes(ByVal strXML As String)
    Dim xmldoc  As New DOMDocument
    Dim iNode As MSXML2.IXMLDOMNode
    Dim iNode2 As MSXML2.IXMLDOMNode
    Dim DonorNodeList As IXMLDOMNodeList
    Dim iAtt As IXMLDOMAttribute
    Dim iAtt2 As IXMLDOMAttribute
 
    On Error GoTo ReadAttributes_OnError
   
    xmldoc.async = False
    xmldoc.loadXML strXML
    If xmldoc.parseError.errorCode <> 0 Then
        MsgBox "Invalid XML, Load Failed"
        GoTo ReadAttributes_OnError
    End If
   
    Set DonorNodeList = xmldoc.getElementsByTagName("Donor")
   
    For Each iNode In DonorNodeList
        For Each iAtt In iNode.Attributes
            MsgBox iAtt.Name & ": " & iAtt.nodeTypedValue
        Next
'        Set iNode2 = iNode.childNodes(0)
        Set iNode2 = iNode.firstChild
        MsgBox iNode2.nodeName
        For Each iAtt2 In iNode2.Attributes
            MsgBox iAtt.Name & ": " & iAtt.nodeTypedValue
        Next
    Next
    Exit Function
 
ReadAttributes_OnError:
    MsgBox Err.Number & " - " & Err.Description
    Exit Function
End Function
0
Comment
Question by:CharlieF2
2 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
Comment Utility
The second level node for which the name is shown with the line
MsgBox iNode2.nodeName

Open in new window

is "PageTypes". That node, and in fact all second level nodes (Transaction and Products as well), don't have any attributes. You need to process the level below that. I have tested in Excel VBA because I don't have Access available now. I changed MsgBox to Debug.Print so I can look at the output more easily (in the "Immediate" window). Here's the changed function code:
Function ReadAttributes(ByVal strXML As String)
    Dim xmldoc  As New DOMDocument
    Dim iNode As MSXML2.IXMLDOMNode
    Dim iNode2 As MSXML2.IXMLDOMNode
    Dim DonorNodeList As IXMLDOMNodeList
    Dim iAtt As IXMLDOMAttribute
    'Dim iAtt2 As IXMLDOMAttribute
    Dim iNode3 As MSXML2.IXMLDOMNode
    Dim iAtt3 As IXMLDOMAttribute

    On Error GoTo ReadAttributes_OnError
    
    xmldoc.async = False
    xmldoc.LoadXML strXML
    If xmldoc.parseError.ErrorCode <> 0 Then
        Debug.Print "Invalid XML, Load Failed"
        GoTo ReadAttributes_OnError
    End If
    
    Set DonorNodeList = xmldoc.getElementsByTagName("Donor")
    
    For Each iNode In DonorNodeList
        For Each iAtt In iNode.Attributes
            Debug.Print "[level 1 attr] " & iAtt.Name & ": " & iAtt.nodeTypedValue
        Next
        For Each iNode2 In iNode.ChildNodes
            Debug.Print "[level 2 node] " & iNode2.nodeName ' will show PageTypes,
            For Each iNode3 In iNode2.ChildNodes
                Debug.Print "[level 3 node] " & iNode3.nodeName
                For Each iAtt3 In iNode3.Attributes
                    Debug.Print "[level 3 attr] " & iAtt3.Name & ": " & iAtt3.nodeTypedValue
                Next
            Next
        Next
    Next
    Exit Function
  
ReadAttributes_OnError:
    Debug.Print Err.Number & " - " & Err.Description
    Exit Function
End Function

Open in new window

Output:
[level 1 attr] DonorID: 34224
[level 1 attr] Email: tsmith@gmail.com
[level 1 attr] DonorFirstName: Tom
[level 1 attr] DonorMiddleName: 
[level 1 attr] DonorLastName: Smith
[level 1 attr] DonorAddress1: 2052 Main Street
[level 1 attr] DonorAddress2: 
[level 1 attr] DonorCity: New York
[level 1 attr] DonorStateProv: New York
[level 1 attr] DonorPostalCode: 10019
[level 1 attr] DonorPhoneHome: 2125298624
[level 1 attr] DonorPhoneWork: 
[level 1 attr] DonorPhoneWorkExt: 
[level 1 attr] DonorPhoneMobile: 
[level 1 attr] DonorEmailAlternate: 
[level 2 node] PageTypes
[level 3 node] PageType
[level 3 attr] OnlinePageType: Product Purchase
[level 2 node] Transaction
[level 3 node] Transactions
[level 3 attr] TransactionID: 194
[level 3 attr] CCTransactionID: -999
[level 3 attr] OrderTimeStamp: 2013-05-24T07:16:37.333
[level 3 attr] OrderTotal: 110.0000
[level 2 node] Products
[level 3 node] Product
[level 3 attr] ProductGroupName: First Pitch Tickets
[level 3 attr] ProductName: Single
[level 3 attr] ProductDescription: 1 Ticket for $10
[level 3 attr] ClientCode: I000001351
[level 3 attr] ProductCount: 1
[level 3 attr] TotalFees: 0.0000
[level 3 attr] TotalAmount: 10.0000
[level 3 node] Product
[level 3 attr] ProductGroupName: First Pitch Tickets
[level 3 attr] ProductName: 12 Tickets
[level 3 attr] ProductDescription: 12 tickets for $100
[level 3 attr] ClientCode: I000001352
[level 3 attr] ProductCount: 1
[level 3 attr] TotalFees: 0.0000
[level 3 attr] TotalAmount: 100.0000

Open in new window

So, to be honest I'm not sure if this is the best way to convert this info to database records but at least it shows how to get to the info you asked for.
0
 
LVL 1

Author Closing Comment

by:CharlieF2
Comment Utility
Robert!

Thank you so much for your assistance.  I knew it had to be something like this - it wasn't making any sense.  I am fully able to take it from here and get the data written to the database tables.  Given how hard I searched for this answer, it will be good to have this question and answer in the knowledgebase for others to find.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now