Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-05-27
2
Medium Priority
?
638 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 2000 total points
ID: 39200485
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
ID: 39200951
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

618 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