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

Solution

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

Comments

[Greybeard]

<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]

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]

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]

@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.