title: Looping through XML using VBA
body: <p>I'm trying to loop through the following simple XML using VBA, with the ultimate goal to be able to easily extract the data in sequence.</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt; 
        &lt;PMRData&gt; 
          &lt;Staff StaffName="Person 1"&gt; 
            &lt;Openings&gt;1.1&lt;/Openings&gt; 
            &lt;Closures&gt;1.11&lt;/Closures&gt; 
          &lt;/Staff&gt; 
          &lt;Staff StaffName="Person 2"&gt; 
            &lt;Openings&gt;1.2&lt;/Openings&gt; 
            &lt;Closures&gt;1.22&lt;/Closures&gt; 
          &lt;/Staff&gt; 
          &lt;Staff StaffName="Person 3"&gt; 
            &lt;Openings&gt;1.3&lt;/Openings&gt; 
            &lt;Closures&gt;1.33&lt;/Closures&gt; 
          &lt;/Staff&gt; 
        &lt;/PMRData&gt;
  </code></pre>
  
  <p>My code so far manages to get the data into the Immediate Window but not in the order I need it. It should be:
  Staff Name Person1
  Openings 1.1
  Closures 1.11
  Staff Name Person 2
  Openings 2.2
  Closures 2.22 etc.</p>
  
  <p>Meaning i need to make my recursive function specific, rather than looping for all nodes.
  Any help would be greatly appreciated! This is what I have so far...</p>
  
  <pre class="lang-vb prettyprint-override"><code>Dim xDoc As DOMDocument
  Set xDoc = New DOMDocument
  Dim xNode As IXMLDOMNode
  Dim xElem As IXMLDOMElement
  Dim xElemCount As Integer
  Dim xSub As IXMLDOMElement
  Dim Nodes As IXMLDOMNodeList
          Set xElem = xDoc.SelectSingleNode("//PMRData")
          Range("a1").Select
          xElemCount = xElem.ChildNodes.Length
          Debug.Print "xElem has " &amp; xElemCount &amp; " Nodes"
          For Each xSub In xElem.ChildNodes
              If xSub.Attributes.Length &gt; 0 Then
              For i = 0 To xSub.Attributes.Length - 1
                  Debug.Print xSub.Attributes(i).nodeName &amp; " - " &amp; xSub.Attributes(i).NodeValue
                  ActiveCell.Value = xSub.Attributes(i).nodeName
                  ActiveCell.Offset(0, 1).Value = xSub.Attributes(i).NodeValue
                  ActiveCell.Offset(1, 0).Select
              Next i
              End If
          Next xSub
          Set Nodes = xElem.SelectNodes("//PMRData")
                  For Each xNode In Nodes
                      DisplayNode xNode
                  Next xNode
  
      End Sub
  
      Public Sub DisplayNode(ByRef xNode As IXMLDOMNode)
          Dim xNode2 As IXMLDOMNode
  
          If xNode.NodeType = NODE_TEXT Then
              Debug.Print "xNode = " &amp; xNode.ParentNode.nodeName
              Debug.Print "xNodeValue = " &amp; xNode.NodeValue
          End If
          If xNode.HasChildNodes Then
              For Each xNode2 In xNode.ChildNodes
                  DisplayNode xNode2
              Next xNode2
          End If
  
      End Sub
  </code></pre>