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.

Answer 1

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