How to select XML child node using its baseName instead of Item(#)?

Question

I'm a little bit stuck with the following: I try to get currency rates from the local bank website to Excel using VBA - mostly for XML parsing practice, I'd say this is my first serious attempt.

After several hours of googling and reading related SO questions I got the more or less working solution, but I'd like to optimize it for better XML understating. So far so good, the question is:

<LIST_RATE>
    <RATE ISO="EUR" Code="978">
        <TITLE>Евро</TITLE>
        <CODE>978</CODE>
        <ISO>EUR</ISO>
        <DATE>Thu, 31 Jan 2013 09:00:00 GMT</DATE>
        <BUY>11550.0000</BUY>
        <SELL>11820.0000</SELL>
        <QUANTITY>1</QUANTITY>
    </RATE>
    <RATE ISO="RUB" Code="643">
        <TITLE>Российский рубль</TITLE>
        <CODE>643</CODE>
        <ISO>RUB</ISO>
        <DATE>Thu, 31 Jan 2013 09:00:00 GMT</DATE>
        <BUY>279.0000</BUY>
        <SELL>292.0000</SELL>
        <QUANTITY>1</QUANTITY>
    </RATE>
    <RATE ISO="USD" Code="840">
        <TITLE>Доллар США</TITLE>
        <CODE>840</CODE>
        <ISO>USD</ISO>
        <DATE>Thu, 31 Jan 2013 09:00:00 GMT</DATE>
        <BUY>8570.0000</BUY>
        <SELL>8710.0000</SELL>
        <QUANTITY>1</QUANTITY>
    </RATE>
</LIST_RATE>

For the above XML part (just in case - this is the link to full XML: http://www.priorbank.by/CurratesExportXml.axd?channel=9) I'm looping through RATE child nodes of LIST_RATE node using the following code:

For Each RATE_Node In LIST_RATE_Node.ChildNodes
    CurrencyCode = RATE_Node.ChildNodes.Item(2).Text 'ISO node
    RateValue = CSng(Replace(RATE_Node.ChildNodes.Item(4).Text, ".", ",")) 'BUY node
    [rest of code]
Next

The code is working fine, but I'd like to select RATE node child nodes somehow using their names instead of Item(#). I tried selectSingleNode, but I'm not at all familiar with XPath, and the notation that I used always returned the very first <RATE ISO="EUR" Code="978"> values. getElementsByTagName returns the whole bunch of nodes, and so on.

I read a lot of related SO questions, but still stuck. I'm pretty sure the solution is simple - I just need to make all the ends meet. Any advice or guidance to the right direction will be highly appreciated. Thanks in advance!

Solution

    Option Explicit

    Private Const xml As String = "<LIST_RATE>" & _
        "<RATE ISO='EUR' Code='978'>" & _
            "<TITLE>????</TITLE>" & _
            "<CODE>978</CODE>" & _
            "<ISO>EUR</ISO>" & _
            "<DATE>Thu, 31 Jan 2013 09:00:00 GMT</DATE>" & _
            "<BUY>11550.0000</BUY>" & _
            "<SELL>11820.0000</SELL>" & _
            "<QUANTITY>1</QUANTITY>" & _
        "</RATE>" & _
    "</LIST_RATE>"

    Sub test()
        Dim xmlDocument As MSXML2.DOMDocument60
        Set xmlDocument = New DOMDocument60

        If Not xmlDocument.LoadXML(xml) Then
            Err.Raise xmlDocument.parseError.ErrorCode, , xmlDocument.parseError.reason
        End If

        Dim listRateNode As IXMLDOMNode
        Dim rateNode As IXMLDOMNode
        Dim isoNode As IXMLDOMNode
        For Each listRateNode In xmlDocument.ChildNodes
            For Each rateNode In listRateNode.ChildNodes
                Set isoNode = rateNode.SelectSingleNode("ISO")
            Next
        Next

        Set isoNode = Nothing
        Set isoNode = xmlDocument.SelectSingleNode("/LIST_RATE/RATE[ISO='EUR']/ISO")
    End Sub

SelectSingleNode should work. If SelectSingleNode is used on RATE_NODE then use just rateNode.SelectSingleNode("ISO"). On the xml-document u can use xmlDocument.SelectSingleNode("/LIST_RATE/RATE[ISO='EUR']/ISO") to find iso node with value EUR. Is this helpfull for u?

Comments

[Peter L.]

I finished up with CurrencyCode = RATE_Node.SelectSingleNode("ISO").Text instead of initial CurrencyCode = RATE_Node.ChildNodes.Item(2).Text - that was exactly I was asking for, and now I also got the clue. Thanks very much for your time and clear code sample. Fairly deserved upvote & accept.