VBA - SelectSingleNode retrieves only first node from the list

I am using VBA to manipulate XML file. MS Offfice - 2013. I added library - Microsoft XML Version6.0. Please find below the xml as well as the code. I am trying to retrieve all the TID's in the xml and write on sheet. But selectsinglenode() is retrieving only the first node. What am I doing wrong? Is there a way to use selectsinglenode to return each TID? Please suggest. Let me know if you need additional information.

<NDA xmlns="http://www.example.com">
  <FileHeader>
    <Form>7</Form>
    <SID>1521</SID> 
  </FileHeader>
  <Subdivision>
    <SID>1521</SID>
    <CID>200</CID>
    <Version>1</Version>
  </Subdivision>
  <BC>
    <BF>
        <BElem>
            <BFountain>
                <BFeature>
                  <Start>0</Start>
                  <End>279</End>
                  <TType>2</TType>
                  <SLimit>25</SLimit>
                  <SIT>3</SIT>
                  <RBTField>0</RBTField>
                  <RType>1</RType>
                  <RParam>0</RParam>
                  <RParamOp>5</RParamOp>
                  <TID>4015100639</TID>
                </BFeature>
                <BFeature>
                  <Start>0</Start>
                  <End>279</End>
                  <TType>1</TType>
                  <SLimit>50</SLimit>
                  <SIT>3</SIT>
                  <RBTField>0</RBTField>
                  <RType>2</RType>
                  <RParam>0</RParam>
                  <RParamOp>5</RParamOp>
                  <TID>2850474662</TID>
                </BFeature>
                <BFeature>
                  <Start>0</Start>
                  <End>279</End>
                  <TType>1</TType>
                  <SLimit>25</SLimit>
                  <SIT>3</SIT>
                  <RBTField>0</RBTField>
                  <RType>1</RType>
                  <RParam>0</RParam>
                  <RParamOp>5</RParamOp>
                  <TID>2563719215</TID>
                </BFeature>
                <BFeature>
                  <Start>0</Start>
                  <End>279</End>
                  <TType>3</TType>
                  <SLimit>25</SLimit>
                  <SIT>3</SIT>
                  <RBTField>0</RBTField>
                  <RType>1</RType>
                  <RParam>0</RParam>
                  <RParamOp>5</RParamOp>
                  <TID>1962204848</TID>
            </BFeature>
          </BFountain>
        </BElem>
    </BF>
    </BC>  
</NDA>

Following is the code:

Dim xDoc1 As MSXML2.DOMDocument60
Dim xNodeList1 As MSXML2.IXMLDOMNodeList
Dim xNode1 As MSXML2.IXMLDOMNode
Dim xChildNode1 As MSXML2.IXMLDOMNode
Dim xpathToExtractRow1 As String, XMLNamespaces1 As String
Dim wCompareWorksheet As Excel.Worksheet
Dim sFoundNode As MSXML2.IXMLDOMNode

Set xDoc1 = New MSXML2.DOMDocument60
xDoc1.async = False
xDoc1.validateOnParse = False
XMLNamespaces1 = "xmlns:r='http://www.example.com"

xDoc1.Load ("ABCD.xml")
xDoc1.setProperty "SelectionNamespaces", XMLNamespaces1
xDoc1.setProperty "SelectionLanguage", "XPath"

If xDoc1.parseError.ErrorCode <> 0 Then
  Set oErr1 = xDoc1.parseError
  Debug.Print oErr1.reason
End If

Set xNodeList1 = xDoc1.SelectNodes("/r:NDA/r:BC/r:BF/r:BElem/r:BFountain/r:BFeature")

z=1
For x = 0 To xNodeList1.Length - 1
    bFirstChild = True
    If xNodeList1.Item(x).HasChildNodes Then
        For i = 0 To xNodeList1.Item(x).ChildNodes.Length - 1
            Set sFoundNode = xNodeList1.Item(x).ChildNodes(i).SelectSingleNode("/r:NDA/r:BC/r:BF/r:BElem/r:BFountain/r:BFeature/r:TID")
            If Not sFoundNode Is Nothing Then
                wCompareWorksheet.Cells(z, 1) = x & "," & i

                wCompareWorksheet.Cells(z, 4) = sFoundNode.nodeName
                wCompareWorksheet.Cells(z, 6) = sFoundNode.Text
                z = z + 1
            Else
                Debug.Print "sFound is nothing"
            End If
        Next
    End If
Next

Solution

Your code uses an absolute location path (it's an absolute location path because it starts with a /):

.selectSingleNode("/r:NDA/r:BC/r:BF/r:BElem/r:BFountain/r:BFeature/r:TID")

Specifically, /r:NDA means start this location path from the document element called "NDA" in the namespace denoted by "r". Changing the element that you call selectSingleNode on won't alter the results of this location path. In Excel terms, it's like using $A$1 in a formula - no matter where you copy this formula, it will always refer to cell A1.

You need to either use selectNodes (as suggested in comments) or use selectSingleNode with a relative location path, like this:

Set sFoundNode = xNodeList1.Item(x).ChildNodes(i).selectSingleNode("self::r:TID")

The result of this location path depends on the context node - childNodes(i). It checks whether the context node itself is called "TID" in the namespace denoted by "r" and outputs the details accordingly.

This is not a very sensible way to achieve this result. You could just check the nodeName attribute of each child node directly. Better still, you could use selectNodes to find all of the r:TID nodes at once and then perform whatever manipulation you need on them