xml - 在 Excel 中使用 VBA 获取 XML 属性

Question

我对使用 VBA 读取 XML 响应还很陌生(请原谅我的无知)。有几篇文章解释了它，但我无法在我的回复中复制它。我正在尝试获取标题“H1”和 requiredID“22”。任何想法我哪里出错了？

以下是xml响应:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <GetListItemsResponse xmlns="http://schemas.microsoft.com/sharepoint/soap/">
            <GetListItemsResult>
                <listitems xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
                    <rs:data ItemCount="1">
                        <z:row Title="H1" 
                        RequiredID="22"/>
                    </rs:data>
                </listitems>
            </GetListItemsResult>
        </GetListItemsResponse>
    </soap:Body>
</soap:Envelope>

下面是 VBA 脚本:

Sub GetXML_Values()
Dim listNode As MSXML2.IXMLDOMNode
Dim XMLDOC As MSXML2.DOMDocument
Set XMLDOC = New MSXML2.DOMDocument
XMLDOC.async = False
XMLDOC.Load ("File Location")
XMLDOC.setProperty "SelectionLanguage", "XPath"
XMLDOC.setProperty "SelectionNamespaces", "xmlns:sp=""http://schemas.microsoft.com/sharepoint/soap/"""
Set lists = XMLDOC.SelectNodes("//soap:Envelope/soap:Body/sp:GetListItemsResponse/sp:GetListItemsResult/sp:listitems/sp:data")

txt = XMLDOC.SelectSingleNode("//data/row").Attributes.getNamedItem("Title").Text

txt = XMLDOC.getElementsByTagName("soap:Envelope")(0).Text

For Each listNode In lists
    Text = listNode.ChildNodes(0).Text
Next

Set XMLDOC = Nothing

End Sub

Answer 1

本质上，您面临的挑战是 XML 响应中的多个命名空间，其中之一是没有任何前缀的默认命名空间。考虑在 SelectionNamespaces 属性中定义所有需要的命名空间，然后在 XPath 中遍历每个节点，相应地指定每个前缀。任何没有定义前缀的节点都将使用映射到 sp 的默认命名空间。 .

下面将打印相同的 @Title和 @RequiredID使用 Debug.Print 在立即窗口 (Ctrl + G) 中使用标量和循环格式的值:

Sub GetXML_Values()
    Dim listNode As MSXML2.IXMLDOMNode, lists As MSXML2.IXMLDOMNodeList
    Dim txt As String, Text As String
    Dim i As Integer
    Dim XMLDOC As MSXML2.DOMDocument

    Set XMLDOC = New MSXML2.DOMDocument

    XMLDOC.async = False
    XMLDOC.Load ("G:\Sandbox\TitledRequiredXPath.xml")
    XMLDOC.setProperty "SelectionLanguage", "XPath"
    XMLDOC.setProperty "SelectionNamespaces", "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' " & _
                                              "xmlns:sp='http://schemas.microsoft.com/sharepoint/soap/' " & _
                                              "xmlns:rs='urn:schemas-microsoft-com:rowset'  " & _
                                              "xmlns:z='#RowsetSchema'"

    Set lists = XMLDOC.SelectNodes("//soap:Envelope/soap:Body/sp:GetListItemsResponse/sp:GetListItemsResult/sp:listitems/rs:data")

    txt = XMLDOC.SelectSingleNode("//rs:data/z:row").Attributes.getNamedItem("Title").Text
    Debug.Print txt

    txt = XMLDOC.SelectSingleNode("//rs:data/z:row").Attributes.getNamedItem("RequiredID").Text
    Debug.Print txt

    For Each listNode In lists
        For i = 0 To listNode.ChildNodes(0).Attributes.Length - 1
            Debug.Print listNode.ChildNodes(0).Attributes(i).Text
        Next i
    Next listNode

    Set lists = Nothing
    Set XMLDOC = Nothing

End Sub

输出

H1
22
H1
22