excel - Excel VBA 解析 XML 字符串按名称和返回属性获取所有节点

问题描述

我无法让这个工作。我向返回字符串（在某种程度上是 xml 形式）的 web api 发出 POST 请求。

我试图只<Row />从 xml 中检索节点并报告它们的“名称”和“值”属性。

我相信我的问题是当我尝试选择节点时（见行Set nodeXML = ....）。MsgBox（就在该行之后）返回 0 的长度。

有人能帮我吗？我究竟做错了什么？

Excel VBA代码：

Public Sub test()
   Dim url As String, resp As String
   Dim i As Integer, r As Integer
   Dim wb As Workbook
   Dim ws As Worksheet

   url = "........."   'Can't show this url - sorry

   
   resp = sendPost(url)

   MsgBox resp    'Yes! this returns the XML I need!

   Set xDoc = CreateObject("MSXML2.DOMDocument.6.0")
   xDoc.setProperty "SelectionNamespaces", "xmlns:r='http://www.regonline.com/api'"
   xDoc.async = False
   xDoc.LoadXML(resp)

   Set wb = ThisWorkbook
   Set ws = wb.Sheets("Sheet1")

   Set nodeXML = xDoc.SelectNodes("/r:System/r:Components/r:Component/r:ComponentData/r:Row")
   
   MsgBox nodeXML.Length  'NO! This returns 0       


   r = 4
   
   For i = 0 To nodeXML.Length - 1
        i = i + 1
        With ws.Rows(i)
            .Cells(1).Value = nodeXML(i).Attributes.getNamedItem("Name").Text
            .Cells(2).Value = nodeXML(i).Attributes.getNamedItem("Value").Text
        End With
    Next
End Sub


Private Function sendPost(url As String) As String

    Dim objXML As Object 

    Dim strData As String
    Dim strResponse As String

    strData = "Request"
    Set objXML = CreateObject("MSXML2.XMLHTTP")
    
    objXML.Open "POST", url & "?" & strData, False
    objXML.setRequestHeader "Content-Type", "text/xml"
    objXML.Send
    strResponse = objXML.responsetext
    
    Set objXML = Nothing
            
    sendPost = strResponse

End Function

POST 响应中的 XML 示例（以字符串形式）：

<System Id="94cc151f-d6fc-4001-85b6-069cdf0afdcb" Labels="" CurrencyCode="USD">
<SystemStreams>
    <SystemStream>
        <ComponentSection ProductSectionId="X2188921" ComponentId="98ab4072-d727-4944-a195-cecdea734991" />
        <ComponentSection ProductSectionId="X2189110" ComponentId="8a2067b0-cfce-4002-9e22-0c242c025d6e" />
        <ComponentSection ProductSectionId="X334226" ComponentId="68dbc455-b84b-43ac-ad25-f9626864b6e6" />
        <ComponentSection ProductSectionId="X0872412" ComponentId="01fe8931-5f41-49d5-b62a-b68c323716f3"  />
        <ComponentSection ProductSectionId="X9981120" ComponentId="2e7765c7-1390-4938-9997-e833cd8f1c71" />
        <ComponentSection ProductSectionId="X4201548" ComponentId="10d38878-9f8b-4207-b82d-bb611178356f"  />
        <ComponentSection ProductSectionId="X1884210" ComponentId="ff5404bd-b61c-44ad-be07-c63436867e72" />
    </SystemStream>
    <SystemStream>
        <ComponentSection ProductSectionId="X8721012" ComponentId="ff5404bd-b61c-44ad-be07-c63436867e72"  />
    </SystemStream>
</SystemStreams>
<Components>
    <Component Id="a1699fcb-330b-4cba-a69b-05808ebd6f8a">
        <Sections>
            <ComponentSection Id="1528e9d0-b0da-4d3d-9fd3-85dcc53c2c15" />
        </Sections>
        <ComponentData>
            <Row Name="muaairstream" Value="2" />
            <Row Name="dampersize" Value="0" />
            <Row Name="damperactuators" Value="1" />
            <Row Name="damperconstruction" Value="0" />
            <Row Name="airstream" Value="2" />
        </ComponentData>
    </Component>
    <Component Id="a9d0c504-c866-4327-9a63-0bed7704a63a">
        <Sections>
            <ComponentSection Id="4f19e1a2-b99c-46ec-a0e2-9917159797bf" />
        </Sections>
        <ComponentData>
            <Row Name="outdoortemperature" Value="2.72222222222223:Celsius" Description="Outside Air Temperature" />
            <Row Name="returntemp" Value="21.1111111111112:Celsius" Description="Return Air Temperature" />
            <Row Name="returnhum" Value="4.66571428571429:GramPerKilogram" Description="Return Air Humidity Ratio" />
            <Row Name="customairtemperature" Value="23.9:Celsius" Description="Custom Air Temperature" />
            <Row Name="customairhumidity" Value="9.28:GramPerKilogram" Description="Custom Air Humidity Ratio" />
            <Row Name="targettemp" Value="21.1111111111112:Celsius" Description="Target Temperature" />
            <Row Name="targethum" Value="6.32857142857143:GramPerKilogram" Description="Target Humidity Ratio" />
        </ComponentData>
    </Component>
    <Component Id="8a2067b0-cfce-4002-9e22-0c242c025d6e" >
        <Sections>
            <ComponentSection Id="3c3c4f5a-4337-4cd1-b292-0276ad4b8f35" />
        </Sections>
        <ComponentData>
            <Row Name="panelfiltergrade" Value="8" Description="MERV Type Filters" />
            <Row Name="filterdepth" Value="2" Description="Filter Size (2in / 4in)" />
            <Row Name="finalfilterexists" Value="0" Description="Has Final Filters? (Y/N)" />
            <Row Name="filterclogswitch" Value="0" Description="Clog Switch Indication (None, Single, Dual)" />
            <Row Name="size" Value="103" Description="Unit Size" />
            <Row Name="unitfilters" Value="10310" Description="Unit Filters (Box 3, 2in MERV-8, No Final, No Indication)" />
        </ComponentData>
    </Component>       
</Components>
<Pricings />
</System>

标签： excelxmlvbaxpath

excel - Excel VBA 解析 XML 字符串按名称和返回属性获取所有节点

问题描述

解决方案

推荐阅读