Extract XML nodes names in VBA

Question

Context

In an example Xml file, some data would be in the following format :

<A1>
   <B1>
      <c1>value</c1>
      <c2>value</c2>
      <c3>value</c3>
   </B1>
   <B2>
      <c1>value</c1>
      <c2>value</c2>
      <c3>value</c3>
    </B2>
 </A1>
<A2>
   <B1>
      <c1>value</c1>
      <c2>value</c2>
      <c3>value</c3>
   </B1>
   <B2>
      <c1>value</c1>
      <c2>value</c2>
      <c3>value</c3>
    </B2>
 </A2>

My question

I need iterate the nodes to get the following result:

A1B1C1
A1B1C2
A1B1C3
A1B2C1
A1B2C2
A1B2C3

I tried many times but was unable to get this result, can anyone help me with this thanks.

You are trying to parse an XML document in VBA and I must say you have a bumpy road ahead of you, you can have a look at [this topic](http://stackoverflow.com/questions/11305/how-to-parse-xml-using-vba) and [this one](http://stackoverflow.com/questions/21491736/how-to-parse-an-xml-file-and-write-in-data-then-save-it). Parsing XML in VBA is nightmarish, try to have a look at XPath also, but there are no practical way to this easily. — Pierre Chevallier, Nov 11 '16 at 13:02
Apparently you did not open the links I provided, but I'll explain to you anyway. You can, but it is extremely difficult and you will have to use all sort of libraries and tools to do it, like [XPath click on the link](https://msdn.microsoft.com/en-us/library/ms256086(v=vs.110).aspx). Try to start looking for answers please and provide us with something to chew on else your post will likely be deleted. — Pierre Chevallier, Nov 11 '16 at 13:32
@PierreChevallier "there are no practical way to this easily" - disagree unless you meant to add "...for someone who's not a programmer" — Tim Williams, Nov 11 '16 at 17:14
@TimWilliams yes indeed, I probably forgot to add this particular point. — Pierre Chevallier, Nov 11 '16 at 18:51

Xam Eseerts · Answer 1 · 2016-11-12T13:24:55.393

First I would recommend using the library "Microsoft XML, vx.x" where x stands for the newest version available to you (should be 6.0). After that you are good to go. But the things to consider when working with XML in VBA are to broad to cover in an answer. I will provide the code here to get your desired output though:

Sub parse_xml()

    Dim XmlFileName As String: XmlFileName = "C:\Path\Filename.xml"
    Dim XmlDocument As New MSXML2.DOMDocument60
    Dim NodeA As IXMLDOMNode
    Dim NodeB As IXMLDOMNode
    Dim NodeC As IXMLDOMNode

    XmlDocument.Load XmlFileName

    For Each NodeA In XmlDocument.DocumentElement.ChildNodes
        For Each NodeB In NodeA.ChildNodes
            For Each NodeC In NodeB.ChildNodes
                Debug.Print NodeC.ParentNode.ParentNode.BaseName & NodeC.ParentNode.BaseName & NodeC.BaseName
            Next NodeC
        Next NodeB
    Next NodeA

End Sub

Please note that I had to expand your file a little bit in order to make it a valid XML-File which can be loaded into the XmlDocument variable. The Xml-File I used for this example is the following:

<?xml version="1.0" encoding="UTF-8"?>
<data>
  <A1>
     <B1>
        <c1>value</c1>
        <c2>value</c2>
        <c3>value</c3>
     </B1>
     <B2>
        <c1>value</c1>
        <c2>value</c2>
        <c3>value</c3>
      </B2>
  </A1>
  <A2>
     <B1>
        <c1>value</c1>
        <c2>value</c2>
        <c3>value</c3>
     </B1>
     <B2>
        <c1>value</c1>
        <c2>value</c2>
        <c3>value</c3>
      </B2>
  </A2>
</data>

This will generate the following output (I just used Debug.Print as you can see):

A1B1c2
A1B1c3
A1B2c1
A1B2c2
A1B2c3
A2B1c1
A2B1c2
A2B1c3
A2B2c1
A2B2c2
A2B2c3

Side note at the end: Different from what Pierre commented I think parsing XML in VBA is quite the achievable task. From my experience you only need the library I recommended at the top of the post to do the actual parsing of the file. I have worked with fairly complex XML-Files using only that library for parsing.

Edit: This is the code, that will only extract values for your desired A criterion:

Sub parse_xml()

    Dim XmlFileName As String: XmlFileName = "C:\Path\Filename.xml"
    Dim XmlDocument As New MSXML2.DOMDocument60
    Dim NodeA As IXMLDOMNode
    Dim NodeB As IXMLDOMNode
    Dim NodeC As IXMLDOMNode

    Dim DesiredA As String: DesiredA = "A2" 'Enter your desired A here

    XmlDocument.Load XmlFileName

    For Each NodeA In XmlDocument.DocumentElement.ChildNodes
        If NodeA.BaseName = DesiredA Then 'This is the new line that selects your desired A
            For Each NodeB In NodeA.ChildNodes
                For Each NodeC In NodeB.ChildNodes
                    Debug.Print NodeC.ParentNode.ParentNode.BaseName & NodeC.ParentNode.BaseName & NodeC.BaseName
                Next NodeC
            Next NodeB
        End If 'End of the If-Statement
    Next NodeA

End Sub

Thanks,i have one more requirement.. I need to find the 'B' and 'C' values of particular 'A'.it means for suppose i have A1,A2,.......A1000,in this case i need to get the data of particular A i.e A99 The data should be A99B1C1 A99B1C2 A99B1C3 A99B2C1 A99B2C2 A99B2C3 Is it possible in VBA ?Thanks — user2155454, Nov 12 '16 at 07:37
Yes it is and it is quite simple! I will edit my answer accordingly in a few hours if that's ok with you. Would be great if you could mark my answer as the correct one then! — Xam Eseerts, Nov 12 '16 at 13:06
Ok I just edited the answer. Did this quick, so I hope it does what you wanted it to do. Tested it on my machine and it works fine. Cheers! — Xam Eseerts, Nov 12 '16 at 13:25
Yes I agree with your answer,but it is looping all 'A' nodes,but my requirement is that is there any chances to go to particular 'A' node based on index if i have the index value..thanks — user2155454, Nov 12 '16 at 14:04
Hi, I described my exact requirement in another question,please check http://stackoverflow.com/questions/40564303/how-to-parse-xml-file-in-vba if you have any idea about in this ,please help me.thanks :) — user2155454, Nov 12 '16 at 15:21
Just answered it. Again: It would be nice if you could mark my answers as the correct ones when you think they are. — Xam Eseerts, Nov 12 '16 at 17:05

Extract XML nodes names in VBA

1 Answers1