Getting XML Attributes using VBA in Excel

Question

I am fairly new to reading an XML response using VBA (pardon my ignorance). There are several posts out there that explain it but I couldn't get it to replicate on my response. I am trying to get the title "H1" and the requiredID "22". Any ideas where am I going wrong?

Below is the xml response:

<?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>

And below is the VBA script:

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

One problem is that it does not go into the For loop. So I think I have an issue with the path in Set list. — M. Marzouk
– M. Marzouk, Commented Jan 22, 2018 at 14:59

Parfait · Accepted Answer · 2018-01-22 15:37:00Z

4

Essentially, your challenge is the multiple namespaces in XML response, one of which is a default namespace without any prefix. Consider defining all needed namespaces in the SelectionNamespaces property, and then in XPath walk down each node specifying each prefix accordingly. Any node without a defined prefix will use the default namespace mapped to sp.

Below will print the same @Title and @RequiredID values in scalar and loop format in Immediate Window (Ctrl + G) using Debug.Print:

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

Output

answered Jan 22, 2018 at 15:37

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

M. Marzouk Over a year ago

Good stuff. The output from the For Loop worked perfectly. Really appreciate your help.

Parfait Over a year ago

Great! Glad to help. Namespace navigation in XML is always tricky! Happy coding.

Collectives™ on Stack Overflow

Getting XML Attributes using VBA in Excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related