How do I select an XML node file using its baseName instead of Item (#)?
I am a bit stuck on the following: I am trying to get currency rates from a local bank site in Excel using VBA - mainly for XML parsing practice, I would say this is my first serious attempt.
After a few hours of doing search engines and SO related readings, I ended up with a more or less working solution, but I would like to optimize it for a better understanding of XML. 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 a link to the full XML: http://www.priorbank.by/CurratesExportXml.axd?channel=9 ) I loop through the RATE
child nodes LIST_RATE
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 works fine, but I would like to select RATE
node child nodes using their names instead Item(#)
. I tried selectSingleNode
, but I am not at all familiar with XPath and the notation I used always returned the very first values <RATE ISO="EUR" Code="978">
. getElementsByTagName
returns the entire chain of nodes, etc.
I have read many related questions but am still stuck. I'm sure the solution is simple - I just need to make ends meet. Any advice or guidance in the right direction would be much appreciated. Thanks in advance!
source to share
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 in RATE_NODE, use only rateNode.SelectSingleNode("ISO")
. In xml document, you can use xmlDocument.SelectSingleNode("/LIST_RATE/RATE[ISO='EUR']/ISO")
iso node with value EUR to search. Is this helpful for u?
source to share