I am trying to use a VBA macro to parse XML file. Given with the following structure:
<bookstore>
<book category="children">
<title>Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category="web">
<title>Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>
How can I enumerate the output with element tags with its corresponding values as shown below?
book | category | children
title | harry potter
author | J K. Rowling
...
My code as follows:
Set xmlFile = CreateObject("Microsoft.XMLDOM")
xmlFile.Load (file)
Set qXML = xmlFile.SelectNodes("/bookstore")
For i = 0 To qXML.Length - 1
Debug.Print CStr(qXML(i).Text)
Next i
How to get Tag Names
Strictly speaking it's (XML)DOM syntax to to get
.Name
and/or.NodeName
properties; XMLDOM (Document Object Model) is a cross-platform and language-independent interface treating the document as a tree structure and allowing programmatic access to the tree.You can use, however the special syntax of XPath expressions (like e.g.
"/bookstore/book/title"
) to address any logical part in the hierarchical xml document structure.So a solution close to your OP would be:
Results in VBE's immediate window
Side note
As
Microsoft.XMLDOM
has been deprecated for years, I'd prefer binding to ►MSXML2
in the most current xml versionMicrosoft XML,v6.0
, e.g. viaI. LATE Binding (as in OP)
II. EARLY Binding
Side note: OP uses the object variable
XMLFile
instead ofxDoc
Note that referencing
DOMDocument
without obvious versioning would bind internally to 3.0 by default (the last stable version before 6.0, any other versions are deprecated).Further links
Obtain attribute names from xml using VBA
XML Parse ...