Parsing XML using Excel VBA

Question

I'm trying to parse an XML response from the Sharepoint REST API using Excel VBA. This is an extract of the XML code. I changed some values for confidentiality reasons

<?xml version="1.0" encoding="utf-8"?>
<feed xml:base="https://company/sites/subsite/_api/" xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:georss="http://www.georss.org/georss" xmlns:gml="http://www.opengis.net/gml">
    <id>4db71c92-9576-4c59-bb85-89d27459139e
    </id>
    <title />
    <updated>2022-04-13T20:17:19Z
    </updated>
    <entry>
        <id>https://company.sharepoint.com/sites/subsite/_api/Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)
        </id>
        <category term="SP.RoleAssignment" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
        <link rel="edit" href="Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)" />
        <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Member" type="application/atom+xml;type=entry" title="Member" href="Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)/Member">
            <m:inline>
                <entry>
                    <id>https://lionbridge.sharepoint.com/sites/LIOXSalesOpsFinancialsDEV/_api/Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)/Member
                    </id>
                    <category term="SP.Group" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
                    <link rel="edit" href="Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)/Member" />
                    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Owner" type="application/atom+xml;type=entry" title="Owner" href="Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)/Member/Owner" />
                    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Users" type="application/atom+xml;type=feed" title="Users" href="Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)/Member/Users" />
                    <title />
                    <updated>2022-04-13T20:17:19Z
                    </updated>
                    <author>
                        <name />
                    </author>
                    <content type="application/xml">
                        <m:properties>
                            <d:Id m:type="Edm.Int32">3
                            </d:Id>
                            <d:IsHiddenInUI m:type="Edm.Boolean">false
                            </d:IsHiddenInUI>
                            <d:LoginName>John Smith
                            </d:LoginName>
                            <d:Title>John Smith
                            </d:Title>
                            <d:PrincipalType m:type="Edm.Int32">8
                            </d:PrincipalType>
                            <d:AllowMembersEditMembership m:type="Edm.Boolean">false
                            </d:AllowMembersEditMembership>
                            <d:AllowRequestToJoinLeave m:type="Edm.Boolean">false
                            </d:AllowRequestToJoinLeave>
                            <d:AutoAcceptRequestToJoinLeave m:type="Edm.Boolean">false
                            </d:AutoAcceptRequestToJoinLeave>
                            <d:Description m:null="true" />
                            <d:OnlyAllowMembersViewMembership m:type="Edm.Boolean">false
                            </d:OnlyAllowMembersViewMembership>
                            <d:OwnerTitle>John Smith
                            </d:OwnerTitle>
                            <d:RequestToJoinLeaveEmailSetting>
                            </d:RequestToJoinLeaveEmailSetting>
                        </m:properties>
                    </content>
                </entry>
            </m:inline>
        </link>
        <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/RoleDefinitionBindings" type="application/atom+xml;type=feed" title="RoleDefinitionBindings" href="Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)/RoleDefinitionBindings">
            <m:inline>
                <feed>
                    <id>db3f92c8-3b3e-40eb-99de-00be4a313e5c
                    </id>
                    <title />
                    <updated>2022-04-13T20:17:19Z
                    </updated>
                    <entry>
                        <id>https://company.sharepoint.com/sites/subsite/_api/Web/RoleDefinitions(1073741829)
                        </id>
                        <category term="SP.RoleDefinition" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
                        <link rel="edit" href="Web/RoleDefinitions(1073741829)" />
                        <title />
                        <updated>2022-04-13T20:17:19Z
                        </updated>
                        <author>
                            <name />
                        </author>
                        <content type="application/xml">
                            <m:properties>
                                <d:BasePermissions m:type="SP.BasePermissions">
                                    <d:High m:type="Edm.Int64">2147483647
                                    </d:High>
                                    <d:Low m:type="Edm.Int64">4294967295
                                    </d:Low>
                                </d:BasePermissions>
                                <d:Description>Has full control.
                                </d:Description>
                                <d:Hidden m:type="Edm.Boolean">false
                                </d:Hidden>
                                <d:Id m:type="Edm.Int32">1073741829
                                </d:Id>
                                <d:Name>Full Control
                                </d:Name>
                                <d:Order m:type="Edm.Int32">1
                                </d:Order>
                                <d:RoleTypeKind m:type="Edm.Int32">5
                                </d:RoleTypeKind>
                            </m:properties>
                        </content>
                    </entry>
                </feed>
            </m:inline>
        </link>
        <title />
        <updated>2022-04-13T20:17:19Z
        </updated>
        <author>
            <name />
        </author>
        <content type="application/xml">
            <m:properties>
                <d:PrincipalId m:type="Edm.Int32">3
                </d:PrincipalId>
            </m:properties>
        </content>
    </entry>
    <entry>
...

I would like to fetch the ID and LoginName (in that example, John Smith)

The VBA code I use is as follows:

    'Declare variables
    Dim xml_obj As MSXML2.XMLHTTP60
    
    'Create a reference to the Microsoft XML library
    Set xml_obj = New MSXML2.XMLHTTP60

    'Define URL Components
    base_url = "https://lionbridge.sharepoint.com/sites/lioxsalesopsfinancialsDEV/_api/web"
    endpoint = "/GetFolderByServerRelativeUrl('/sites/lioxsalesopsfinancialsDEV/Shared%20Documents/LGS/Forecast')/ListItemAllFields/RoleAssignments?"
    
    param_1 = "$expand="
    param_1_val = "Member,RoleDefinitionBindings"
    
    'Combine all the different components into a single URL
    api_url = base_url + endpoint + _
              param_1 + param_1_val
    
    Debug.Print api_url
        
    'Open a new request, specify the method and the URL
    xml_obj.Open bstrMethod:="GET", bstrURL:=api_url
    
    'Send the request
    xml_obj.send
    
    'Print the status code, it should be "OK"
    Debug.Print "The Request was " + xml_obj.statusText

    'To parse the info that is sent back, we will store it in a "document" which will leverage a document object model.
    'This model has
    Dim xDoc As MSXML2.DOMDocument60
    Dim xNodes As MSXML2.IXMLDOMNodeList
    Dim xNode As MSXML2.IXMLDOMNode
    
    'First create a new document.
    Set xDoc = New MSXML2.DOMDocument60
    
    'Laod the response text into our document.
    xDoc.LoadXML (xml_obj.responseText)
    
    xDoc.SetProperty "SelectionNamespaces", "xmlns:d='http://schemas.microsoft.com/ado/2007/08/dataservices' xmlns:m='http://schemas.microsoft.com/ado/2007/08/dataservices/metadata'"
    
    Set xNodes = xDoc.getElementsByTagName("m:properties")
    
    For Each xNode In xNodes
        If xNode.ChildNodes.Length <> 1 Then
            Debug.Print xNode.SelectSingleNode("d:Id").Text, xNode.SelectSingleNode("d:Title").Text
        End If
    Next

If I use the getElementsByTagName method, I get pretty much what I need but I also get nodes that I don't want

I'd like to use the following method instead:

Set xNodes = xDoc.SelectNodes("/feed/entry/link[2]/m:inline/entry/content/m:properties")

However, xNodes.Length returns 0

What am I missing?

Tim Williams · Accepted Answer · 2022-04-15 17:55:52Z

6

In the root feed element there are a few namespaces declared:

<feed xml:base="https://company/sites/subsite/_api/" 
         xmlns="http://www.w3.org/2005/Atom" 
         xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" 
         xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" 
         xmlns:georss="http://www.georss.org/georss" 
         xmlns:gml="http://www.opengis.net/gml">

Note one of those does not have an alias - that means it's the default namespace. To query nodes using the default, you still need to add it using SetProperty "SelectionNamespaces", and give it an alias (I used "xxx" below) so you can use it in your xpath query:

Sub tester()

    Dim xDoc As MSXML2.DOMDocument60
    Dim xNodes As MSXML2.IXMLDOMNodeList
    Dim xNode As MSXML2.IXMLDOMNode
    
    Set xDoc = New MSXML2.DOMDocument60
    
    xDoc.validateOnParse = True
    
    xDoc.Load "C:\Temp\tmp.xml" 'loading from a file for testing
    
    xDoc.SetProperty "SelectionNamespaces", _
       "xmlns:xxx='http://www.w3.org/2005/Atom' " & _
       "xmlns:d='http://schemas.microsoft.com/ado/2007/08/dataservices' " & _
       "xmlns:m='http://schemas.microsoft.com/ado/2007/08/dataservices/metadata'"
    
    Set xNodes = xDoc.SelectNodes("/xxx:feed/xxx:entry/xxx:link[2]/m:inline/xxx:entry/xxx:content/m:properties")
    
    Debug.Print xNodes.Length '1 for my sample XML
    
End Sub

edited Apr 15, 2022 at 17:55

answered Apr 14, 2022 at 3:26

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

3 Comments

Nico Over a year ago

Thanks, I would have never guessed that. I will give it a try tomorrow. Quick question: what is this command for? Is it necessary? xDoc.validateOnParse = True

Tim Williams Over a year ago

I don't think you need the validateOnParse - default is True anyway

smartini Over a year ago

To set the [SetProperty] for xDoc is a very useful piece of information in the VBA XML parsing world, I barely stumbled across it when watching tutorials. I faced the same problem like OP and it worked.

Collectives™ on Stack Overflow

Parsing XML using Excel VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related