Solved

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

Posted on 2013-05-27
2
627 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
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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