Parsing XML tags using VBA

Question

I am trying to parse out specific "fields" in the XML sample below using VBA. For instance, I want to specifically parse the value from "Field1" and throw it into a variable. I'm having all sorts of trouble.

Here's a bit of sample code:

Sub test()
Set oXML = New MSXML2.DOMDocument      
oXML.async = False
oXML.validateOnParse = False
oXML.Load ("C:\sample.xml")
Set oXmlNodes = oXML.selectNodes("/")

For Each oXmlNode In oXmlNodes
 Debug.Print oXmlNode.Text
Next

End Sub

And here's the XML:

<?xml version="1.0" encoding="UTF-8" ?> 
<form>
 <metadata>
 <prop name="formName">
 <value>myTestForm</value> 
 </prop>
 <prop name="formIdentifier">
 <value>0000033</value> 
 </prop>
 </metadata>
 <field name="field1" type="String">
 <value>something</value> 
 </field>
 <field name="field2" type="String">
 <value>something else</value> 
 </field>
</form>

to StackOverflow · Accepted Answer · 2013-05-22 14:43:17Z

Your sample XML is not well-formed: you have a "prop" end tag with no matching start tag:

<form>
 <metadata>

     <value>myTestForm</value> 
   </prop>
   <prop name="formIdentifier">
     <value>0000033</value> 
   </prop>
 </metadata>

Try adding a prop tag, e.g.:

<form>
 <metadata>
   <prop name="formName">
     <value>myTestForm</value> 
   </prop>
   <prop name="formIdentifier">
     <value>0000033</value> 
   </prop>
 </metadata>

In your code you should check for parse errors:

oXML.Load ("C:\sample.xml")
if (oXML.parseError.errorCode != 0) ...

UPDATE

Corrected in my post, but obviously still dont know how to parse out the specific values.

You are using an XPATH expression "/" that selects the root node. You need to change this to select the node(s) you want. It's not clear what you want, but you could start by trying to select all "field" nodes.

Set oXmlNodes = oXML.selectNodes("//field")

Or something more specific, e.g. the following will select the field node that's a child of the root form node and has an attribute name='field1':

Set oXmlNodes = oXML.selectNodes("/form/field[@name='field1']")

If this doesn't help, either post more info about what you want, or look at an XPATH tutorial.

Thanks -- I actually have the correct tags but didnt copy and paste correctly.
Corrected in my post, but obviously still dont know how to parse out the specific values.
THAT'S IT! I wasn't sure how exactly selectNodes worked.. Now I can specifically reference the attributes I need. Thank you!

Collectives™ on Stack Overflow

Parsing XML tags using VBA

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related