VBScript iterating through XML child nodes and retrieving values

Question

I have the following XML provided by a customer from which I need to extract items like:

<CustomerProductName>
<ProductName>
<ProductAssetName>

Treating each <CustomerProducts> section as a seperate order.

I can get to <CustomerProducts> and extract the <CustomerProductName> without a problem and iterate over the value in each section.

But I can't see how I get to the values below it. If someone could someone give me some pointers as to how to achieve thIs would be gratefull as I have been trying for over a day now.

The basic code is below the XML

<?xml version="1.0"?>
<Products_Root>
    <Products_IPN VendorID="11344" >
        <CustomerProducts CustomerProductName="Test" ProductCount="7">
            <Products ProductType="BOOK" ProductName="Donald" >
                <ProductComponents ProductAssetName="Donald.pdf" />
                <ProductSpecs SpecClass="MEASUREMENT" SpecType="MARGINS" SpecValue="PER FILE"/>
                <ProductSpecs SpecClass="OPERATION" SpecType="BIND" SpecValue="SADDLE"/>
            </Products>
            <Products ProductType="BOOK" ProductName="Duck">
                <ProductComponents ProductAssetName="Duck.pdf"/>
                <ProductSpecs SpecClass="MEASUREMENT" SpecType="MARGINS" SpecValue="PER FILE"/>
                </Products>
        </CustomerProducts>
        <CustomerProducts CustomerProductName="Test2" ProductCount="2">
            <Products ProductType="BOOK" ProductName="Micky">
                <ProductComponents ProductAssetName="Mouse.pdf" />
                <ProductComponents ProductAssetName="Mouse.pdf" />
                <ProductSpecs SpecClass="MEASUREMENT" SpecType="MARGINS" SpecValue="PER FILE"/>
            </Products>
            <CustomerProductSpecs SpecClass="OPERATION" SpecType="KITTING" SpecValue="SHRINKWRAP KIT"/>
        </CustomerProducts>
        <CustomerProducts CustomerProductName="Test3" ProductCount="6">
            <Products ProductType="BOOK" ProductName="Minnie">
                <ProductComponents ProductAssetName="Mouse" />
                <ProductSpecs SpecClass="MEASUREMENT" SpecType="MARGINS" SpecValue="PER FILE"/>
            </Products>
        </CustomerProducts>
    </Products_IPN>
</Products_Root>

And here my VBScript code so far

Dim xmlDoc, objNodeList, plot

Set xmlDoc = CreateObject("Msxml2.DOMDocument")
xmlDoc.setProperty "SelectionLanguage", "XPath"
xmlDoc.load("E:\dropbox\Dropbox\Hobbs\Xerox Example Files\test.xml")

Set objNodeList = xmlDoc.getElementsByTagName("CustomerProducts")

plot="No Value"
If objNodeList.length > 0 then
    For each x in objNodeList
        JobName=x.getattribute("CustomerProductName")
        msgbox JobName
    Next
Else
    msgbox chr(34) & "CustomerProducts" & chr(34) & " field not found."
End If

Tomalak · Accepted Answer · 2020-02-25 11:15:43Z

7

You already set the selection language to XPath, maybe you should use it, too. :)

Option Explicit

Dim xmlDoc, CustomerProducts, Products
Dim plot, CustomerProductName, ProductName

Set xmlDoc = CreateObject("MSXML2.DOMDocument")
xmlDoc.setProperty "SelectionLanguage", "XPath"
xmlDoc.load "products.xml"

plot = "No Value"

For Each CustomerProducts In xmlDoc.SelectNodes("//CustomerProducts")
  CustomerProductName = CustomerProducts.getAttribute("CustomerProductName")

  For Each Products In CustomerProducts.SelectNodes("./Products")
    ProductName = Products.getAttribute("ProductName")

    MsgBox CustomerProductName & " - " & ProductName
  Next
Next

edited Feb 25, 2020 at 11:15

answered Aug 22, 2012 at 14:36

Tomalak

339k68 gold badges547 silver badges635 bronze badges

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

4 Comments

Greybeard Over a year ago

<return> sends the post, must remember that ;)<br /> To het to the lower structures <ProductComponents><ProductSpecs> etc, do I use a For Each process or can I refference them in another way i.e. <br /> ASSET = "./Products/ProductSpecs"<br />

Tomalak Over a year ago

My answer shows the way to use XPath (SelectNodes()). If you don't want to loop over the nodes, then you don't need to use For Each. It will be helpful to have a look at the documentation to find out what other methods and properties are available.

Greybeard Over a year ago

Tomalak, thanks for the advice yesterday, it helped greatly :) I have updated code but it's too big for the text box, how would I best share it?

Tomalak Over a year ago

@Greybeard There is no upper limit for the code you publish here. Nevertheless it's advisable to keep the code samples small. Nobody likes digging through tons of foreign code.

Collectives™ on Stack Overflow

VBScript iterating through XML child nodes and retrieving values

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related