VBA - Loop specific childnodes from XML code

Question

I'm trying to scrape the following Xml into an Excel sheet. However, I only want to loop through specific childnodes to show the Name, PriceEffectiveStart, PriceEffectiveEnd, Price, and Currency for each index summary.

XML Code

<indexPrices>
     <indexPriceSummary>
         <id>1</id>
         <uri>www.example.com</uri>
      <index>
        <id>3</id>
        <name>Same Day Index</name>
        <uri>www.example.com.xml</uri>
      </index>
      <priceEffectiveStart>2015-06-26</priceEffectiveStart>
      <priceEffectiveEnd>2015-06-26</priceEffectiveEnd>
      <price>
         <amount>2.4806</amount>
         <currency>CAD</currency>
      </price>
      <duration>1</duration>
      <quantityTraded>
        <amount>474</amount>
        <unit>GJ</unit>
        <contractUnit>Day</contractUnit>
      </quantityTraded>
      <numberOfTrades>7</numberOfTrades>
      <settlementState>Settled</settlementState>
      <lastUpdateDate>2015-06-27T02:15:01-06:00</lastUpdateDate>
    </indexPriceSummary>
    <indexPriceSummary>
        <id>1</id>
        <uri>www.example.com.xml</uri>
     <index>
      <id>1</id>
      <name>Same Day Index </name>
      <uri>www.example.com.xml</uri>
     </index>
     <priceEffectiveStart>2015-06-27</priceEffectiveStart>
     <priceEffectiveEnd>2015-06-27</priceEffectiveEnd>
     <price>
         <amount>2.516</amount>
         <currency>CAD</currency>
     </price>
     <duration>1</duration>
     <quantityTraded>
        <amount>251</amount>
        <unit>GJ</unit>
        <contractUnit>Day</contractUnit>
     </quantityTraded>
     <numberOfTrades>50</numberOfTrades>
     <settlementState>Settled</settlementState>
     <lastUpdateDate>2015-06-28T02:15:00-06:00</lastUpdateDate>
   </indexPriceSummary>
</IndexPrices>

VBA Code

Dim xDoc As DOMDocument
Set xDoc = New DOMDocument

xDoc.LoadXML objHTTP.responseText

Dim i As Integer
Dim list As IXMLDOMNodeList
Set list = xDoc.SelectNodes("//indexPrices/indexPriceSummary")

Dim node As IXMLDOMNode
Dim childNode As IXMLDOMNode
Dim price As IXMLDOMNode

For Each node In list
    i = i + 1

    If (node.HasChildNodes) Then
        For Each childNode In node.ChildNodes
             i = i + 1
            Debug.Print childNode.BaseName & " " & childNode.Text
             Worksheets("Sheet1").Cells(i, 1) = childNode.BaseName
             Worksheets("Sheet1").Cells(i, 2) = childNode.Text
        Next childNode
    End If


  Next node

The current VBA shows all nodes in the output. I would like it to show only Name, PriceEffectiveStart, PriceEffectiveEnd, Price, and Currency for each index summary.

Solution

You can use xpath on each indexPriceSummary node to get at the child elements directly:

Sub Tester()
    Dim xDoc As DOMDocument
    Set xDoc = New DOMDocument

    ''more code here


    xDoc.LoadXML objHTTP.responseText

    Dim i As Integer
    Dim list As IXMLDOMNodeList
    Set list = xDoc.SelectNodes("//indexPrices/indexPriceSummary")

    Dim node As IXMLDOMNode, nd As IXMLDOMNode
    Dim childNode As IXMLDOMNode
    Dim price As IXMLDOMNode

    i = 4
    For Each node In list
        i = i + 1

        With Sheet1.Rows(i)
            .Cells(1).Value = GetNodeValue(node, "index/name")
            .Cells(2).Value = GetNodeValue(node, "priceEffectiveStart")
            .Cells(3).Value = GetNodeValue(node, "priceEffectiveEnd")
            .Cells(4).Value = GetNodeValue(node, "price/amount")
            .Cells(5).Value = GetNodeValue(node, "price/currency")
        End With

    Next node

End Sub

Function GetNodeValue(node As IXMLDOMNode, xp As String)
    Dim n As IXMLDOMNode, nv
    Set n = node.SelectSingleNode(xp)
    If Not n Is Nothing Then nv = n.nodeTypedValue
    GetNodeValue = nv
End Function