excel - 为什么使用 MSXML v3.0 解析 XML 文档可以，而 MSXML v6.0 却不行

Question

因此，我正在从事一个项目，根据每个来源的特点，使用许多不同的方法从互联网上的许多不同来源抓取和收集数据。

最近添加的是一个网站 API调用返回以下 XML作为回应:

<?xml version="1.0"?>
<Publication_MarketDocument xmlns="urn:iec62325.351:tc57wg16:451-3:publicationdocument:7:0">
    <mRID>29b526a69b9445a7bb507ba446e3e8f9</mRID>
    <revisionNumber>1</revisionNumber>
    <type>A44</type>
    <sender_MarketParticipant.mRID codingScheme="A01">10X1001A1001A450</sender_MarketParticipant.mRID>
    <sender_MarketParticipant.marketRole.type>A32</sender_MarketParticipant.marketRole.type>
    <receiver_MarketParticipant.mRID codingScheme="A01">10X1001A1001A450</receiver_MarketParticipant.mRID>
    <receiver_MarketParticipant.marketRole.type>A33</receiver_MarketParticipant.marketRole.type>
    <createdDateTime>2019-09-19T11:28:51Z</createdDateTime>
    <period.timeInterval>
        <start>2019-09-18T22:00Z</start>
        <end>2019-09-19T22:00Z</end>
    </period.timeInterval>
    <TimeSeries>
        <mRID>1</mRID>
        <businessType>A62</businessType>
        <in_Domain.mRID codingScheme="A01">10YCS-SERBIATSOV</in_Domain.mRID>
        <out_Domain.mRID codingScheme="A01">10YCS-SERBIATSOV</out_Domain.mRID>
        <currency_Unit.name>EUR</currency_Unit.name>
        <price_Measure_Unit.name>MWH</price_Measure_Unit.name>
        <curveType>A01</curveType>
        <Period>
            <timeInterval>
                <start>2019-09-18T22:00Z</start>
                <end>2019-09-19T22:00Z</end>
            </timeInterval>
            <resolution>PT60M</resolution>
            <Point>
                <position>1</position>
                <price.amount>44.08</price.amount>
            </Point>
            <Point>
                <position>2</position>
                <price.amount>37.14</price.amount>
            </Point>
            <Point>
                <position>3</position>
                <price.amount>32.21</price.amount>
            </Point>
            <Point>
                <position>4</position>
                <price.amount>31.44</price.amount>
            </Point>
            <Point>
                <position>5</position>
                <price.amount>32.48</price.amount>
            </Point>
            <Point>
                <position>6</position>
                <price.amount>45.52</price.amount>
            </Point>
            <Point>
                <position>7</position>
                <price.amount>56.05</price.amount>
            </Point>
            <Point>
                <position>8</position>
                <price.amount>74.96</price.amount>
            </Point>
            <Point>
                <position>9</position>
                <price.amount>74.08</price.amount>
            </Point>
            <Point>
                <position>10</position>
                <price.amount>69.03</price.amount>
            </Point>
            <Point>
                <position>11</position>
                <price.amount>72.89</price.amount>
            </Point>
            <Point>
                <position>12</position>
                <price.amount>68.91</price.amount>
            </Point>
            <Point>
                <position>13</position>
                <price.amount>74.95</price.amount>
            </Point>
            <Point>
                <position>14</position>
                <price.amount>72.91</price.amount>
            </Point>
            <Point>
                <position>15</position>
                <price.amount>75.97</price.amount>
            </Point>
            <Point>
                <position>16</position>
                <price.amount>76.49</price.amount>
            </Point>
            <Point>
                <position>17</position>
                <price.amount>59.08</price.amount>
            </Point>
            <Point>
                <position>18</position>
                <price.amount>60.19</price.amount>
            </Point>
            <Point>
                <position>19</position>
                <price.amount>64.69</price.amount>
            </Point>
            <Point>
                <position>20</position>
                <price.amount>69.18</price.amount>
            </Point>
            <Point>
                <position>21</position>
                <price.amount>64.97</price.amount>
            </Point>
            <Point>
                <position>22</position>
                <price.amount>63.38</price.amount>
            </Point>
            <Point>
                <position>23</position>
                <price.amount>52.92</price.amount>
            </Point>
            <Point>
                <position>24</position>
                <price.amount>48.08</price.amount>
            </Point>
        </Period>
    </TimeSeries>
</Publication_MarketDocument> 

使用 Microsoft XML, v6.0 成功处理了类似情况我尝试了以下方法:

Dim respXML As New MSXML2.DOMDocument60
respXML.LoadXML (ThisWorkbook.Worksheets("Sheet2").Range("A1")) 'for the sake of the post's simplicity I'm loading the xml from excel
Debug.Print respXML.getElementsByTagName("price.amount").Length

这应该返回 24但它返回 0 .确实是以下内容:

Debug.Print respXML.getElementsByTagName("price.amount")(1) Is Nothing

返回 True ，这意味着 <price.amount></price.amount>没有找到元素。然而，Debug.Print respXML.XML产生了预期的结果。

我在某处读到早期绑定(bind)可能会导致问题，所以我也尝试了以下方法:

Dim respXML As Object
Set respXML = CreateObject("MSXML2.DOMDocument.6.0")
respXML.LoadXML (ThisWorkbook.Worksheets("Sheet2").Range("A1"))
Debug.Print respXML.getElementsByTagName("price.amount").Length
Debug.Print respXML.getElementsByTagName("price.amount")(1) Is Nothing

结果还是一样。

切换到 Microsoft XML, v3.0彻底解决问题。

但是，我更愿意坚持使用 v6.0，因为它得到了更积极的维护和支持。

为什么会这样？它与 XML 本身有关吗？它与我的代码有关吗？我错过了什么吗？有没有办法让它与 Microsoft XML, v6.0 一起工作？ ？

如有任何意见，我们将不胜感激。

Answer 1

扩展@CindyMeister 的 answer ，问题似乎是使用 getElementsByTagName() 的 MSXML 版本之间的命名空间处理。具体来说，您的 XML 维护一个没有冒号标识前缀的 xmlns 属性，这需要 DOM 库在解析内容时分配一个前缀:

<Publication_MarketDocument xmlns="urn:iec62325.351:tc57wg16:451-3:publicationdocument:7:0" ...

但是，使用 SelectionNamespaces + SelectNodes 为默认 namespace 前缀定义一个临时别名，例如 doc，两个库都打印出预期的结果结果。和 MS docs甚至建议后一种方法(强调):

The getElementsByTagName method simulates the matching of the provided argument against the result of the tagName property of IXMLDOMElement. When executed, it does not recognize or support namespaces. Instead, you should use the selectNodes method, which is faster in some cases and can support more complex searches.

MXSML v3.0 (打印意外的 getElementsByTagName 结果)

Sub ParseXMLv3()
    Dim respXML As New MSXML2.DOMDocument30

    respXML.Load "C:\Path\To\Input.xml"
    respXML.setProperty "SelectionLanguage", "XPath"
    respXML.setProperty "SelectionNamespaces", "xmlns:doc='urn:iec62325.351:tc57wg16:451-3:publicationdocument:7:0'"

    Debug.Print respXML.SelectNodes("//doc:price.amount").Length       ' PRINTS 24
    Debug.Print respXML.SelectNodes("//price.amount").Length           ' PRINTS 0
    Debug.Print respXML.getElementsByTagName("price.amount").Length    ' PRINTS 24

    Set respXML = Nothing
End Sub

MSXML v6.0

Sub ParseXMLv6()
    Dim respXML As New MSXML2.DOMDocument60

    respXML.Load "C:\Path\To\Input.xml"
    respXML.setProperty "SelectionLanguage", "XPath"
    respXML.setProperty "SelectionNamespaces", "xmlns:doc='urn:iec62325.351:tc57wg16:451-3:publicationdocument:7:0'"

    Debug.Print respXML.SelectNodes("//doc:price.amount").Length       ' PRINTS 24
    Debug.Print respXML.SelectNodes("//price.amount").Length           ' PRINTS 0
    Debug.Print respXML.getElementsByTagName("price.amount").Length    ' PRINTS 0

    Set respXML = Nothing
End Sub