title: VBA - Display each node and its value from XML
body: <p>I have a simple XML as below and I need to display name of each and every node and its value. No element would have any attribute.</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;ResponseEnvelope xmlns="http://www.nwabcdfdfd.com/messagin" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
     &lt;ResponseHeader&gt;
        &lt;RequestId&gt;directv_99e0857d-abf3-461c-913e-3ab59c6b5ef6&lt;/RequestId&gt;
        &lt;ResponseId&gt;1162969&lt;/ResponseId&gt;
        &lt;MessageVersion&gt;1.10&lt;/MessageVersion&gt;
        &lt;RequestTimestamp&gt;2013-02-12T17:26:28.172Z&lt;/RequestTimestamp&gt;
        &lt;ResponseTimestamp&gt;2013-02-12T17:26:50.409Z&lt;/ResponseTimestamp&gt;
        &lt;SenderId&gt;CarePortal2&lt;/SenderId&gt;
        &lt;ProgramName /&gt;
        &lt;TestProdFlag&gt;P&lt;/TestProdFlag&gt;
        &lt;ResultCode&gt;9&lt;/ResultCode&gt;
        &lt;Locale&gt;en_US&lt;/Locale&gt;
        &lt;Errors&gt;
           &lt;Error&gt;
              &lt;ErrorCode&gt;9&lt;/ErrorCode&gt;
              &lt;ErrorNumber&gt;90001&lt;/ErrorNumber&gt;
              &lt;ErrorMessage&gt;System error occurred&lt;/ErrorMessage&gt;
              &lt;ErrorFieldId /&gt;
           &lt;/Error&gt;
        &lt;/Errors&gt;
     &lt;/ResponseHeader&gt;
     &lt;ResponseBody xsi:type="CPSingleSignOnResponse"&gt;
        &lt;PortalUserID&gt;45497&lt;/PortalUserID&gt;
        &lt;PartyID&gt;1858186&lt;/PartyID&gt;
        &lt;WarrantyItemName&gt;DTV ABC WOLE HE P&lt;/WarrantyItemName&gt;
        &lt;WarrantyInventoryItemId&gt;138677&lt;/WarrantyInventoryItemId&gt;
        &lt;ClientWarrantySku&gt;202&lt;/ClientWarrantySku&gt;
        &lt;ClientWarrantyDescription&gt;DV Plan&lt;/ClientWarrantyDescription&gt;
        &lt;ContractNumber&gt;4003564&lt;/ContractNumber&gt;
        &lt;IsPortalUserCreated&gt;N&lt;/IsPortalUserCreated&gt;
        &lt;IsPartyCreated&gt;N&lt;/IsPartyCreated&gt;
        &lt;IsContractUpdated&gt;N&lt;/IsContractUpdated&gt;
        &lt;IsFootPrintUpdated&gt;N&lt;/IsFootPrintUpdated&gt;
        &lt;Customer&gt;
           &lt;PartyId&gt;185812386&lt;/PartyId&gt;
           &lt;Salutation /&gt;
           &lt;FirstName&gt;Tejas&lt;/FirstName&gt;
           &lt;LastName&gt;Tanna&lt;/LastName&gt;
           &lt;AddressList&gt;
              &lt;Address&gt;
                 &lt;PartySiteId&gt;3617490&lt;/PartySiteId&gt;
                 &lt;Type&gt;BILTO&lt;/Type&gt;
                 &lt;Address1&gt;CASCADES&lt;/Address1&gt;
                 &lt;Address2&gt;202&lt;/Address2&gt;
                 &lt;Address3&gt;RIDGE HEAVEN&lt;/Address3&gt;
                 &lt;Address4 /&gt;
                 &lt;City&gt;STERLING&lt;/City&gt;
                 &lt;State&gt;VA&lt;/State&gt;
                 &lt;PostalCode&gt;20165&lt;/PostalCode&gt;
                 &lt;County&gt;LOUDOUN&lt;/County&gt;
                 &lt;Province /&gt;
                 &lt;Country&gt;US&lt;/Country&gt;
                 &lt;Urbanization /&gt;
                 &lt;AddressStyle&gt;US&lt;/AddressStyle&gt;
              &lt;/Address&gt;
              &lt;Address&gt;
                 &lt;PartySiteId&gt;3613791&lt;/PartySiteId&gt;
                 &lt;Type&gt;SHIP_T&lt;/Type&gt;
                 &lt;Address1&gt;CASADS&lt;/Address1&gt;
                 &lt;Address2&gt;22&lt;/Address2&gt;
                 &lt;Address3&gt;RIE HEEN&lt;/Address3&gt;
                 &lt;Address4 /&gt;
                 &lt;City&gt;STELI&lt;/City&gt;
                 &lt;State&gt;VA&lt;/State&gt;
                 &lt;PostalCode&gt;2065&lt;/PostalCode&gt;
                 &lt;County&gt;LOUUN&lt;/County&gt;
                 &lt;Province /&gt;
                 &lt;Country&gt;US&lt;/Country&gt;
                 &lt;Urbanization /&gt;
                 &lt;AddressStyle&gt;US&lt;/AddressStyle&gt;
              &lt;/Address&gt;
           &lt;/AddressList&gt;
           &lt;PhoneList&gt;
              &lt;Phone&gt;
                 &lt;ContactPointId&gt;2371717&lt;/ContactPointId&gt;
                 &lt;Type&gt;HOME PNE&lt;/Type&gt;
                 &lt;PhoneNumber&gt;51-62-7464&lt;/PhoneNumber&gt;
                 &lt;Country&gt;1&lt;/Country&gt;
                 &lt;PrimaryFlag&gt;Y&lt;/PrimaryFlag&gt;
              &lt;/Phone&gt;
           &lt;/PhoneList&gt;
           &lt;EmailList&gt;
              &lt;Email&gt;
                 &lt;ContactPointId&gt;237516&lt;/ContactPointId&gt;
                 &lt;EmailAddress&gt;a.abc@abc.com&lt;/EmailAddress&gt;
                 &lt;PrimaryFlag&gt;Y&lt;/PrimaryFlag&gt;
              &lt;/Email&gt;
           &lt;/EmailList&gt;
        &lt;/Customer&gt;
     &lt;/ResponseBody&gt;
  &lt;/ResponseEnvelope&gt;
  </code></pre>
  
  <p>Only challenge here is there may be some element which might have sub-element in its own e.g. <strong>Address</strong> So the code needs to have a recursive function.</p>
  
  <p>Also the elements which don't have any text like <strong>Address4(it only has sub-elements)</strong> should not be displayed. Also the elements like <strong>Province</strong> should not be displayed.</p>
  
  <p>I tried following code but not working..</p>
  
  <pre><code>Sub Driver()
      Range("4:" &amp; Rows.Count).ClearContents
      Set xmlDoc = CreateObject("Microsoft.XMLDOM")
  
      i = 4
      xmlDoc.LoadXML (Range("A2"))
      Set oParentNode = xmlDoc.DocumentElement.SelectNodes("ResponseBody")(0)
      Call List_ChildNodes(oParentNode, i, "A", "B")
  End Sub
  
  Sub List_ChildNodes(oParentNode, i, NameColumn, ValueColumn)
      For Each oChildNode In oParentNode.ChildNodes
          If oChildNode.ChildNodes.Length &gt; 1 Then
              Call List_ChildNodes(oChildNode, i, NameColumn, ValueColumn)
          Else
              Cells(i, NameColumn) = oChildNode.tagname
              Cells(i, ValueColumn) = oChildNode.Text
              i = i + 1
          End If
      Next
  End Sub
  </code></pre>