title: Parsing XML using Excel VBA
body: <p>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</p>
  <pre><code>&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot;?&gt;
  &lt;feed xml:base=&quot;https://company/sites/subsite/_api/&quot; xmlns=&quot;http://www.w3.org/2005/Atom&quot; xmlns:d=&quot;http://schemas.microsoft.com/ado/2007/08/dataservices&quot; xmlns:m=&quot;http://schemas.microsoft.com/ado/2007/08/dataservices/metadata&quot; xmlns:georss=&quot;http://www.georss.org/georss&quot; xmlns:gml=&quot;http://www.opengis.net/gml&quot;&gt;
      &lt;id&gt;4db71c92-9576-4c59-bb85-89d27459139e
      &lt;/id&gt;
      &lt;title /&gt;
      &lt;updated&gt;2022-04-13T20:17:19Z
      &lt;/updated&gt;
      &lt;entry&gt;
          &lt;id&gt;https://company.sharepoint.com/sites/subsite/_api/Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)
          &lt;/id&gt;
          &lt;category term=&quot;SP.RoleAssignment&quot; scheme=&quot;http://schemas.microsoft.com/ado/2007/08/dataservices/scheme&quot; /&gt;
          &lt;link rel=&quot;edit&quot; href=&quot;Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)&quot; /&gt;
          &lt;link rel=&quot;http://schemas.microsoft.com/ado/2007/08/dataservices/related/Member&quot; type=&quot;application/atom+xml;type=entry&quot; title=&quot;Member&quot; href=&quot;Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)/Member&quot;&gt;
              &lt;m:inline&gt;
                  &lt;entry&gt;
                      &lt;id&gt;https://lionbridge.sharepoint.com/sites/LIOXSalesOpsFinancialsDEV/_api/Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)/Member
                      &lt;/id&gt;
                      &lt;category term=&quot;SP.Group&quot; scheme=&quot;http://schemas.microsoft.com/ado/2007/08/dataservices/scheme&quot; /&gt;
                      &lt;link rel=&quot;edit&quot; href=&quot;Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)/Member&quot; /&gt;
                      &lt;link rel=&quot;http://schemas.microsoft.com/ado/2007/08/dataservices/related/Owner&quot; type=&quot;application/atom+xml;type=entry&quot; title=&quot;Owner&quot; href=&quot;Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)/Member/Owner&quot; /&gt;
                      &lt;link rel=&quot;http://schemas.microsoft.com/ado/2007/08/dataservices/related/Users&quot; type=&quot;application/atom+xml;type=feed&quot; title=&quot;Users&quot; href=&quot;Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)/Member/Users&quot; /&gt;
                      &lt;title /&gt;
                      &lt;updated&gt;2022-04-13T20:17:19Z
                      &lt;/updated&gt;
                      &lt;author&gt;
                          &lt;name /&gt;
                      &lt;/author&gt;
                      &lt;content type=&quot;application/xml&quot;&gt;
                          &lt;m:properties&gt;
                              &lt;d:Id m:type=&quot;Edm.Int32&quot;&gt;3
                              &lt;/d:Id&gt;
                              &lt;d:IsHiddenInUI m:type=&quot;Edm.Boolean&quot;&gt;false
                              &lt;/d:IsHiddenInUI&gt;
                              &lt;d:LoginName&gt;John Smith
                              &lt;/d:LoginName&gt;
                              &lt;d:Title&gt;John Smith
                              &lt;/d:Title&gt;
                              &lt;d:PrincipalType m:type=&quot;Edm.Int32&quot;&gt;8
                              &lt;/d:PrincipalType&gt;
                              &lt;d:AllowMembersEditMembership m:type=&quot;Edm.Boolean&quot;&gt;false
                              &lt;/d:AllowMembersEditMembership&gt;
                              &lt;d:AllowRequestToJoinLeave m:type=&quot;Edm.Boolean&quot;&gt;false
                              &lt;/d:AllowRequestToJoinLeave&gt;
                              &lt;d:AutoAcceptRequestToJoinLeave m:type=&quot;Edm.Boolean&quot;&gt;false
                              &lt;/d:AutoAcceptRequestToJoinLeave&gt;
                              &lt;d:Description m:null=&quot;true&quot; /&gt;
                              &lt;d:OnlyAllowMembersViewMembership m:type=&quot;Edm.Boolean&quot;&gt;false
                              &lt;/d:OnlyAllowMembersViewMembership&gt;
                              &lt;d:OwnerTitle&gt;John Smith
                              &lt;/d:OwnerTitle&gt;
                              &lt;d:RequestToJoinLeaveEmailSetting&gt;
                              &lt;/d:RequestToJoinLeaveEmailSetting&gt;
                          &lt;/m:properties&gt;
                      &lt;/content&gt;
                  &lt;/entry&gt;
              &lt;/m:inline&gt;
          &lt;/link&gt;
          &lt;link rel=&quot;http://schemas.microsoft.com/ado/2007/08/dataservices/related/RoleDefinitionBindings&quot; type=&quot;application/atom+xml;type=feed&quot; title=&quot;RoleDefinitionBindings&quot; href=&quot;Web/Lists(guid'e32e86fd-3161-4e44-a654-2424b357d566')/Items(1590)/RoleAssignments/GetByPrincipalId(3)/RoleDefinitionBindings&quot;&gt;
              &lt;m:inline&gt;
                  &lt;feed&gt;
                      &lt;id&gt;db3f92c8-3b3e-40eb-99de-00be4a313e5c
                      &lt;/id&gt;
                      &lt;title /&gt;
                      &lt;updated&gt;2022-04-13T20:17:19Z
                      &lt;/updated&gt;
                      &lt;entry&gt;
                          &lt;id&gt;https://company.sharepoint.com/sites/subsite/_api/Web/RoleDefinitions(1073741829)
                          &lt;/id&gt;
                          &lt;category term=&quot;SP.RoleDefinition&quot; scheme=&quot;http://schemas.microsoft.com/ado/2007/08/dataservices/scheme&quot; /&gt;
                          &lt;link rel=&quot;edit&quot; href=&quot;Web/RoleDefinitions(1073741829)&quot; /&gt;
                          &lt;title /&gt;
                          &lt;updated&gt;2022-04-13T20:17:19Z
                          &lt;/updated&gt;
                          &lt;author&gt;
                              &lt;name /&gt;
                          &lt;/author&gt;
                          &lt;content type=&quot;application/xml&quot;&gt;
                              &lt;m:properties&gt;
                                  &lt;d:BasePermissions m:type=&quot;SP.BasePermissions&quot;&gt;
                                      &lt;d:High m:type=&quot;Edm.Int64&quot;&gt;2147483647
                                      &lt;/d:High&gt;
                                      &lt;d:Low m:type=&quot;Edm.Int64&quot;&gt;4294967295
                                      &lt;/d:Low&gt;
                                  &lt;/d:BasePermissions&gt;
                                  &lt;d:Description&gt;Has full control.
                                  &lt;/d:Description&gt;
                                  &lt;d:Hidden m:type=&quot;Edm.Boolean&quot;&gt;false
                                  &lt;/d:Hidden&gt;
                                  &lt;d:Id m:type=&quot;Edm.Int32&quot;&gt;1073741829
                                  &lt;/d:Id&gt;
                                  &lt;d:Name&gt;Full Control
                                  &lt;/d:Name&gt;
                                  &lt;d:Order m:type=&quot;Edm.Int32&quot;&gt;1
                                  &lt;/d:Order&gt;
                                  &lt;d:RoleTypeKind m:type=&quot;Edm.Int32&quot;&gt;5
                                  &lt;/d:RoleTypeKind&gt;
                              &lt;/m:properties&gt;
                          &lt;/content&gt;
                      &lt;/entry&gt;
                  &lt;/feed&gt;
              &lt;/m:inline&gt;
          &lt;/link&gt;
          &lt;title /&gt;
          &lt;updated&gt;2022-04-13T20:17:19Z
          &lt;/updated&gt;
          &lt;author&gt;
              &lt;name /&gt;
          &lt;/author&gt;
          &lt;content type=&quot;application/xml&quot;&gt;
              &lt;m:properties&gt;
                  &lt;d:PrincipalId m:type=&quot;Edm.Int32&quot;&gt;3
                  &lt;/d:PrincipalId&gt;
              &lt;/m:properties&gt;
          &lt;/content&gt;
      &lt;/entry&gt;
      &lt;entry&gt;
  ...
  </code></pre>
  
  <p>I would like to fetch the ID and LoginName (in that example, John Smith)</p>
  <p>The VBA code I use is as follows:</p>
  <pre><code>    '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 = &quot;https://lionbridge.sharepoint.com/sites/lioxsalesopsfinancialsDEV/_api/web&quot;
      endpoint = &quot;/GetFolderByServerRelativeUrl('/sites/lioxsalesopsfinancialsDEV/Shared%20Documents/LGS/Forecast')/ListItemAllFields/RoleAssignments?&quot;
      
      param_1 = &quot;$expand=&quot;
      param_1_val = &quot;Member,RoleDefinitionBindings&quot;
      
      '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:=&quot;GET&quot;, bstrURL:=api_url
      
      'Send the request
      xml_obj.send
      
      'Print the status code, it should be &quot;OK&quot;
      Debug.Print &quot;The Request was &quot; + xml_obj.statusText
  
      'To parse the info that is sent back, we will store it in a &quot;document&quot; 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 &quot;SelectionNamespaces&quot;, &quot;xmlns:d='http://schemas.microsoft.com/ado/2007/08/dataservices' xmlns:m='http://schemas.microsoft.com/ado/2007/08/dataservices/metadata'&quot;
      
      Set xNodes = xDoc.getElementsByTagName(&quot;m:properties&quot;)
      
      For Each xNode In xNodes
          If xNode.ChildNodes.Length &lt;&gt; 1 Then
              Debug.Print xNode.SelectSingleNode(&quot;d:Id&quot;).Text, xNode.SelectSingleNode(&quot;d:Title&quot;).Text
          End If
      Next
  </code></pre>
  <p>If I use the getElementsByTagName method, I get pretty much what I need but I also get nodes that I don't want</p>
  <p>I'd like to use the following method instead:</p>
  <pre><code>Set xNodes = xDoc.SelectNodes(&quot;/feed/entry/link[2]/m:inline/entry/content/m:properties&quot;)
  </code></pre>
  <p>However, xNodes.Length returns 0</p>
  <p>What am I missing?</p>