XML in excel

[Mike Archer]

Hello - I am pretty comfortable with vba in excel. However, I don't know
anything about xml. I am trying to query an xml file. Here is a piece of
the xml:

<?xml version="1.0" encoding="UTF-8" ?>
- <GetCategoriesResponse xmlns="urn:ebay:apis:eBLBaseComponents">
<Timestamp>2006-04-28T14:27:28.172Z</Timestamp>
<Ack>Success</Ack>
<Version>457</Version>
<Build>e457_core_Bundled_2818483_R1</Build>
- <CategoryArray>
- <Category>
<BestOfferEnabled>true</BestOfferEnabled>
<AutoPayEnabled>true</AutoPayEnabled>
<CategoryID>20081</CategoryID>
<CategoryLevel>1</CategoryLevel>
<CategoryName>Antiques</CategoryName>
<CategoryParentID>20081</CategoryParentID>
<Expired>false</Expired>
<IntlAutosFixedCat>false</IntlAutosFixedCat>
<LeafCategory>false</LeafCategory>
<Virtual>false</Virtual>
<ORPA>false</ORPA>
<LSD>false</LSD>
</Category>
- <Category>
<BestOfferEnabled>true</BestOfferEnabled>
<AutoPayEnabled>true</AutoPayEnabled>
<CategoryID>37903</CategoryID>
<CategoryLevel>2</CategoryLevel>
<CategoryName>Antiquities (Classical, Amer.)</CategoryName>
<CategoryParentID>20081</CategoryParentID>
<Expired>false</Expired>
<IntlAutosFixedCat>false</IntlAutosFixedCat>
<LeafCategory>false</LeafCategory>
<Virtual>false</Virtual>
<ORPA>false</ORPA>
<LSD>false</LSD>
</Category>
- <Category>
<BestOfferEnabled>true</BestOfferEnabled>
<AutoPayEnabled>true</AutoPayEnabled>
<CategoryID>37905</CategoryID>
<CategoryLevel>3</CategoryLevel>
<CategoryName>Egyptian</CategoryName>
<CategoryParentID>37903</CategoryParentID>
<Expired>false</Expired>
<IntlAutosFixedCat>false</IntlAutosFixedCat>
<LeafCategory>true</LeafCategory>
<Virtual>false</Virtual>
<ORPA>false</ORPA>
<LSD>false</LSD>
</Category>

I need to be able to filter by CategoryParentID and return an array of
CategoryNames. I know that I need to utilize XPath, but there is really no
documentation in the excel help files. This is what I have tried so far:

Dim xmlDoc As New Msxml2.DOMDocument40
Dim objNodeList As IXMLDOMNodeList
xmlDoc.async = False
xmlDoc.Load ("G:\CatTree.xml")
xmlDoc.setProperty "SelectionLanguage", "XPath"
Set objNodeList = xmlDoc.selectNodes("//Category")
' I get nothing returned for objNodeList
MsgBox objNodeList.Length ' this is 0
For Each x In objNodeList ' skips this loop (because it is empty)
MsgBox x.XML
Next
End Sub

I think that I need to utilize selectionNameSpaces (but i'm not sure how -
I'm not even completely sure I understand what a namespace is).

If someone could point me towards some good examples of querying xml from
vba in excel, that would be great. Specifically, the above example is the
ebay category tree. So examples of using ebay's CatTree.xml would be even
better. I can't find anything out there in vba (and I have really tested the
limits of google's computing abilities).

 

[Tim Williams]

This worked for me:

'#############################
Sub Tester()
Dim xmlDoc As New MSXML2.DOMDocument40
Dim objNodeList As IXMLDOMNodeList
Dim x As Object

xmlDoc.async = False
xmlDoc.loadXML Sheet1.Range("A1").Value

If xmlDoc.parseError.errorCode <> 0 Then

Debug.Print "Error!" & vbCrLf & _
" Line: " & xmlDoc.parseError.Line & vbCrLf & _
" Text:" & xmlDoc.parseError.srcText & vbCrLf & _
" Reason: " & xmlDoc.parseError.reason

Else

Set objNodeList = xmlDoc.selectNodes("//Category")
Debug.Print "Count: " & objNodeList.Length

For Each x In objNodeList
Debug.Print x.XML
Next

End If

End Sub
'####################################
Note I have a different xml version

Tim.