vba parse xml file code example

Example: vba parse xml file

' Basic parsing of xml file
' Add Reference "Microsoft XML, v6.0" to your project
Sub XMLParse()
    Dim oXml As New MSXML2.DOMDocument60
    Dim lNodes  As IXMLDOMNodeList, xNode As IXMLDOMNode
    oXml.Load ("C:\temp\misc.xml")
    With oXml.DocumentElement
        Set lNodes = .SelectNodes("food")       'or try :
        Set lNodes = .SelectNodes("food/name")
    End With
    For Each xNode In lNodes
        With xNode
            Debug.Print .ParentNode.nodeName & "/" & .nodeName & " : " & .Text
        End With
    Next xNode
End Sub

' XML sample (misc.xml):
<?xml version="1.0" encoding="UTF-8"?>
<menu>
    <food>
        <name>Belgian Waffles</name>
        <price>$5.95</price>
    </food>
    <food>
        <name>Strawberry Belgian Waffles</name>
        <price>$7.95</price>
    </food>
</menu>