Get All the Attributes and value in XML using VBA

Question

I am using VBA to get All the attributes and values from the XML parent node.

Here is the XML:

<Elements>
   <Details>
      <Name>ABC</Name>
      <Address>123ABC</Address>
   </Details>   
   <Dept>
      <Deptname>IT</Deptname>
      <ID>A123</ID>
   </Dept>  
</Elements>

This is the code I am using in VBA

sub test() 
    Dim XMLFile As Object
    Dim XMLFileName As String
    Set XMLFile = CreateObject("Microsoft.XMLDOM")

    XMLFileName = "C:\Users\Input.xml"
    XMLFile.async = False
    XMLFile.Load (XMLFileName)
    XMLFile.validateOnParse = False

    Dim mainnode As Object
    Dim node As Object

    Set mainnode = XMLFile.SelectNodes("//Elements")

    For Each node In mainnode
        Dim child As Variant
        For Each child In node.ChildNodes
            Debug.Print child.Name
        Next child
    Next node
End sub

This is the output I am expecting.

Details
Name
Address
Dept
deptname
ID

I am getting errors executing the above code. Could someone help me in getting this resolved. Thanks!

AFAIK it's preferrable to use the **`MSXML2`** namespace as the old `Microsoft.XMLDOM` one is only maintained for *legacy Support*. Thus late binding the recommended version 6 can be obtained via `Set XMLFile = CreateObject("MSXML2.DOMDocument.6.0")` after the object declaration `Dim XMLFile As Object`. — T.M., Jul 31 '18 at 14:55

score 3 · Accepted Answer · answered Jul 31 '18 at 13:27

3

In the xml sample Name and Address are childern of Details. The below code retruns Details and Dept:

For Each node In mainnode
    Dim child As Object
    For Each child In node.ChildNodes
        Debug.Print child.BaseName
    Next child
Next node

If you want the children of Details and Dept, you need to go one level deeper:

For Each node In mainnode
    Dim child As Object
    For Each child In node.ChildNodes
        Debug.Print child.BaseName
        Dim kiddo As Object
        For Each kiddo In child.ChildNodes
            Debug.Print kiddo.BaseName
        Next kiddo
    Next child
Next node

answered Jul 31 '18 at 13:27

Vityata

42,633
8
55
100

1

Thank you for the response. This works. I am also trying to write the attributes to an excel , but I am getting the "Dept" written twice on my excel. `For Each node In mainnode Dim child As Object For i = 0 To node.ChildNodes.Length - 1 For Each child In node.ChildNodes 'Debug.Print child.BaseName Worksheets("sheet1").Range("C" & i + 1).Value = child.BaseName Next child Next Next node` – Raja Jul 31 '18 at 14:10
@vityala - How do I write the records to an excel. I am using a 'for' loop to perform this operation, it is not giving the result as I expected – Raja Jul 31 '18 at 14:27
@Raja - for loop is the way to go. Or for - each. Anyway, this sounds like a new question, just put some code and a sample and you would get an answer. Or see this one - https://stackoverflow.com/questions/3875415/loop-through-each-cell-in-a-range-of-cells-when-given-a-range-object – Vityata Jul 31 '18 at 14:29
1

For the stylish answer +1 – QHarr Jul 31 '18 at 16:22
@Vityata - Is there a way I could handle repeating tags in the xml ?. Asking this question since I do get same
tag for multiple members. I do not want the same tagname to be written twice in my result. Thanks!
– Raja Jul 31 '18 at 17:34
@Raja - take a look at the dictionary structure in VBA and add the tags to a dictionary. Then loop through the keys, it should be quite ok. – Vityata Jul 31 '18 at 17:35
Thank you. I was able to add the dictionary. I am missing something in my code. ' Set dict = CreateObject("Scripting.Dictionary") For Each node In mainnode Dim child As Object i = 0 For Each child In node.ChildNodes If Not Exists(child.BaseName) Then Worksheets("sheet1").Range("C" & i + 1).Value = child.BaseName' – Raja Jul 31 '18 at 18:27
@Raja - I am a bit afk now, but it is probably a good idea to ask a separate question. Or to check the top "dictionary" questions in vba and excel in SO, something useful will pop up. – Vityata Jul 31 '18 at 18:33
@Vityata - Thanks for the response. – Raja Aug 01 '18 at 13:29
@Vityata - How do I get the "num" attribute of the XML. ` A ` – Raja Aug 09 '18 at 15:26
@Raja - I have to check, but you can really profit if you ask it as a separate question. Just make sure to add a bit of code and the XML, probably in less than 30 minutes you would have an answer :) – Vityata Aug 09 '18 at 16:13
@Vityata - it might be of interest to see my answer to assign an entire XML structure to an 2-dim array determine the number of elements via `XPath/XMLDOM` and optionally write the whole info back to a sheet via **recursive calls** - see [Obtain Attribute names from XML using VBA](https://stackoverflow.com/questions/51887820/obtain-attribute-names-from-xml-using-vba/51919182#51919182) – T.M. Aug 20 '18 at 14:14

score 0 · Answer 2 · answered Jul 31 '18 at 15:18

I am able to get the expected result with the below code

For Each node In mainnode
Dim child As Object
'For i = 0 To node.ChildNodes.Length - 1
i = 0
    For Each child In node.ChildNodes


            Debug.Print child.BaseName
            Worksheets("sheet1").Range("C" & i + 1).Value = child.BaseName

            'i = i + 1

            Dim kiddo As Object
                For Each kiddo In child.ChildNodes
                Debug.Print kiddo.BaseName
                Worksheets("sheet1").Range("D" & i + 1).Value = kiddo.BaseName
                i = i + 1
                Next kiddo
            Next child
            Next node

When my XML contains only attributes without the tags, the above code is not picking the node.

Updated XML: <Elements> <Details> <Name>ABC</Name> <Address>123ABC</Address> </Details> <Dept num = "123" > <Deptname>IT</Deptname> <ID>A123</ID> <ID1>A123</ID1> </Dept> </Elements>

The Child node: "num" is not getting displayed . Is there anything I am missing in my code. ?

Get All the Attributes and value in XML using VBA

2 Answers2