Import XML data into excel using VBA, selectnodes is not working (returns nothing)

Question

I have the below XML

<cqresponse xmlns="http://ibm.com/rational/cqweb/v7.1">
<rows>
    <row>
        <id>
            <![CDATA[ ABC00082474 ]]>
        </id>
        <Short_Desc>
            <![CDATA[
            Some Description 1
            ]]>
        </Short_Desc>
        <State>
            <![CDATA[ Some State 1 ]]>
        </State>
     </row>
     <row>
        <id>
            <![CDATA[ ABC00082475 ]]>
        </id>
        <Short_Desc>
            <![CDATA[
            Some Description 2
            ]]>
        </Short_Desc>
        <State>
            <![CDATA[ Some State 2 ]]>
        </State>
    </row>
</rows>
</cqresponse>

I want to import this into Excel using VB script. I had the below code, but the SelectNodes is not returning anything. It just returns Nothing

Sub Test()
    Dim nodeList As IXMLDOMNodeList
    Dim nodeRow As IXMLDOMNode
    Dim nodeCell As IXMLDOMNode
    Dim rowCount As Integer
    Dim cellCount As Integer
    Dim rowRange As Range
    Dim cellRange As Range
    Dim sheet As Worksheet
    Dim xpathToExtractRow As String
    Dim dom As DOMDocument60

    xpathToExtractRow = "/cqresponse/rows/row"

    Set dom = New DOMDocument60
    dom.Load ("C:\ABC.xml") ' this file contains the same xml data as mentioned above
    Set sheet = ActiveSheet
    Set nodeList = dom.SelectNodes(xpathToExtractRow)

    rowCount = 0
    For Each nodeRow In nodeList
        rowCount = rowCount + 1
        cellCount = 0
        For Each nodeCell In nodeRow.ChildNodes
            cellCount = cellCount + 1
            Set cellRange = sheet.Cells(rowCount, cellCount)
            cellRange.Value = nodeCell.Text
        Next nodeCell
    Next nodeRow
End Sub

Is there something wrong in this or do I need to take a different approach?

Answer 1

An often made mistake with XML parsing is the undeclared namespace in the root. As a result, you have to assign a namespace prefix during parsing of the document and use such a prefix in the XPath expression. Below, doc is assigned:

...
Dim xpathToExtractRow As String, XMLNamespaces As String
Dim dom As DOMDocument60

xpathToExtractRow = "/doc:cqresponse/doc:rows/doc:row"
XMLNamespaces = "xmlns:doc='http://ibm.com/rational/cqweb/v7.1'"

Set dom = New DOMDocument60
dom.Load ("C:\ABC.xml")
dom.setProperty "SelectionNamespaces", XMLNamespaces
...