CODEWITHSUNDEEP
xmlvba
user2409865
user2409865

Reputation: 1

Parsing XML tags using VBA

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>

Upvotes: 0

Views: 1039

Answers (1)

to StackOverflow
to StackOverflow

Reputation: 124726

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.

Upvotes: 2

Related Questions