Fetch data from XML Tag using VBA

Question

I have XML file in the in the following Format.

<?xml version="1.0" encoding="UTF-8"?>
    <root1>
    <root2>
    <value1 claimed="23"/>
    <value2 claimed="3343"/>
    <value3 claimed="5656"/>
    </root2>
    </root1>

I need data from each of the child tags.

like /root1/root2/value1 should give "23" /root1/root2/value2 will be "3343"

I have the following code so far which fetches data from each the Xpath that I provide.

    Sub testxml()

    Set xmlDoc = CreateObject("Microsoft.XMLDOM")
    xmlDoc.setProperty "SelectionLanguage", "XPath"
    xmlDoc.async = False
'XML File 
    xmlDoc.Load (ThisWorkbook.Sheets(1).Range("A1").Value)
    'XPath stored here
    Set nodeXML = xmlDoc.getElementsByTagName(Range("A2").Value)
    Columns("B:B").Clear
    For i = 0 To nodeXML.Length - 1

        For Each chlnodes In nodeXML(i).ParentNode.ChildNodes
           'Debug.Print chlnodes

        Next chlnodes

        Range("B" & i + 1).Value = nodeXML(i).Text
    Next

    End Sub

This code works fine for following XML structure

<?xml version="1.0" encoding="UTF-8"?>
<root1>
<root2>
<value1>23</value1>
<value2>3343</value2>
<value3>5656</value3>
</root2>
</root1>

Can anyone please update my VBA code code for this.

Thanks in advance.

Instead of .Text try using this .getAttribute("claimed"). It should fix the issue. — SIM
– SIM, Commented Jul 2, 2018 at 10:54

Erik A · Accepted Answer · 2018-07-02 10:54:15Z

1

You can just try appending the attribute selector to your XPath query:

Sub testxml()
    Set xmlDoc = CreateObject("Microsoft.XMLDOM")
    xmlDoc.setProperty "SelectionLanguage", "XPath"
    xmlDoc.async = False
'XML File 
    xmlDoc.Load (ThisWorkbook.Sheets(1).Range("A1").Value)
    'XPath stored here
    Set nodeXML = xmlDoc.SelectNodes(Range("A2").Value & /@claimed)
    Columns("B:B").Clear
    For i = 0 To nodeXML.Length - 1
        Range("B" & i + 1).Value = nodeXML(i).NodeValue
    Next
End Sub

I've also removed some redundant code, and reworked it a bit.

answered Jul 2, 2018 at 10:54

Erik A

32.8k13 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Fetch data from XML Tag using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related