CharlieF2
asked on
Unable to read the attributes of child nodes using XML DOM in Access 2003
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-24 T07: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.errorCod e <> 0 Then
MsgBox "Invalid XML, Load Failed"
GoTo ReadAttributes_OnError
End If
Set DonorNodeList = xmldoc.getElementsByTagNam e("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
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
- <PageTypes>
<PageType OnlinePageType="Product Purchase" />
</PageTypes>
- <Transaction>
<Transactions TransactionID="194" CCTransactionID="-999" OrderTimeStamp="2013-05-24
</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.errorCod
MsgBox "Invalid XML, Load Failed"
GoTo ReadAttributes_OnError
End If
Set DonorNodeList = xmldoc.getElementsByTagNam
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.