如何使用其baseName而不是Item（＃）来选择XML子节点？ [英] How to select XML child node using its baseName instead of Item(#)?

问题描述

我有点坚持以下几点：我试图从本地银行网站获得货币汇率到使用VBA的Excel - 主要用于XML解析实践，我会说这是我第一次认真的尝试。

经过几个小时的谷歌搜索和阅读相关的SO问题，我得到了或多或少的工作解决方案，但我想优化它，以更好的XML低估。到目前为止这么好，问题是：

 < LIST_RATE> 
< RATE ISO =EURCode =978> 
< TITLE>Евро< / TITLE> 
< CODE> 978< / CODE> 
< ISO> EUR< / ISO> 
< DATE>星期四，2013年1月31日09:00:00 GMT< / DATE> 
<买入> 11550.0000< / Buy> 
< SELL> 11820.0000< / SELL> 
&QUANTITY> 1< / QUANTITY> 
< / RATE> 
< RATE ISO =RUBCode =643> 
< TITLE>Российскийрубль< / TITLE> 
< CODE> 643< / CODE> 
< ISO> RUB< / ISO> 
< DATE>星期四，2013年1月31日09:00:00 GMT< / DATE> 
< BUY> 279.0000< / BUY> 
< SELL> 292.0000< / SELL> 
&QUANTITY> 1< / QUANTITY> 
< / RATE> 
< RATE ISO =USDCode =840> 
< TITLE>ДолларСША< / TITLE> 
< CODE> 840< / CODE> 
< ISO> USD< / ISO> 
< DATE>星期四，2013年1月31日09:00:00 GMT< / DATE> 
< BUY> 8570.0000< / BUY> 
< SELL> 8710.0000< / SELL> 
&QUANTITY> 1< / QUANTITY> 
< / RATE> 
< / LIST_RATE> 
  

对于上述XML部分（以防万一 - 这是到完整XML的链接： http://www.priorbank.by/CurratesExportXml.axd?channel=9 ）我'使用以下代码循环使用 RATE 节点 LIST_RATE 节点

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ RATE_Node.ChildNodes.Item（4）.Text，。，，））'BUY node
[其他代码]
下一个
pre>

代码正常工作，但我想选择 RATE node 子节点使用他们的名字而不是 Item（＃）。我试过 selectSingleNode ，但我完全不熟悉XPath，而我使用的符号总是返回第一个< RATE ISO = EURCode =978> 值。 getElementsByTagName 返回整个节点，等等。

我读了很多相关的SO问题，但是仍然卡住我很确定解决方案很简单 - 我只需要使所有的目标满足。任何对正确方向的建议或指导将受到高度评价。感谢提前！

解决方案

  Option Explicit 
 
 Private Const xml As String =< LIST_RATE> &安培; _ 
< RATE ISO ='EUR'代码='978'> &安培; _ 
< TITLE> ????< / TITLE> &安培; _ 
< CODE> 978< / CODE> &安培; _ 
< ISO> EUR< / ISO> &安培; _ 
< DATE>星期四，2013年1月31日09:00:00 GMT< / DATE> &安培; _ 
<买入> 11550.0000< / BUY> &安培; _ 
< SELL> 11820.0000< / SELL> &安培; _ 
< QUANTITY> 1< / QUANTITY> &安培; _ 
< / RATE> &安培; _ 
< / LIST_RATE> 
 
 Sub test（）
 Dim xmlDocument As MSXML2.DOMDocument60 
设置xmlDocument =新建DOMDocument60 
 
如果不是xmlDocument.LoadXML（xml）然后
 Err.Raise xmlDocument.parseError.ErrorCode，xmlDocument.parseError.reason 
 End If 
 
 Dim listRateNode As IXMLDOMNode 
 Dim rateNode As IXMLDOMNode 
 Dim isoNode As IXMLDOMNode 
对于每个listRateNode在xmlDocument.ChildNodes 
对于每个rateNode在listRateNode.ChildNodes 
设置isoNode = rateNode.SelectSingleNode（ISO）
下一个
下一个
 
设置isoNode = Nothing 
设置isoNode = xmlDocument.SelectSingleNode（/ LIST_RATE / RATE [ISO ='EUR'] / ISO）
 End Sub 
  

SelectSingleNode应该工作。如果在RATE_NODE上使用SelectSingleNode，则只需使用 rateNode.SelectSingleNode（ISO）。在xml文档中，您可以使用 xmlDocument.SelectSingleNode（/ LIST_RATE / RATE [ISO ='EUR'] / ISO）查找值为EUR的iso节点。这对你有帮助吗？

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!

解决方案

    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?

这篇关于如何使用其baseName而不是Item（＃）来选择XML子节点？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！