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 Documents/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?
CodePudding user response：
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