Failing to retrieve correct node data

Question

I'm trying to automate creating sales orders from xml files sent to us by our customers.

The structure of their XML files varies between different customers so I've created an SQL Server table containing the paths to the essential pieces of order info for each customer.

$salesOrder contains the entire XML file. The SQL data is loaded in to $customerTemplate and the order lines are loaded into an array named $SalesOrderLines:

$salesOrderLines = @($salesOrder.SelectNodes($customerTemplate.LinePath))

The problem is that when I loop through the order line array e.g.:

foreach ($salesOrderLine in $salesOrderLines) {
    $partCode = ($salesOrderLine.SelectSingleNode($customerTemplate.PartCodePath)).'#text'
}

it works fine for the 1st order line, i.e. it picks the correct part code, but for any subsequent order lines $partCode stays the same value. Even if I clear the $partCode variable at the end of the loop, when it picks up the second order line $partCode gets the value from the 1st order line - though I can see that $orderLine contains the correct properties that I'm looking for.

Have I missed something obvious, because after 2 days of trying to solve this I'm not making much progress? I can provide the entire script if necessary, but I figured it's too big an ask to expect anyone to check through 300 lines of code.

A sample of the XML with a single order line:

<?xml version="1.0"?>
<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-Schema.xsd">
    <Orders>
        <Order type="B" number="23298">
            <OrderedAt>
                <Creditor code="100001" number="100001" type="S">
                    <Name>Widget Co</Name>
                    <ExternalCode>EW001</ExternalCode>
                </Creditor>
                <Date>2018-01-31</Date>
            </OrderedAt>
            <OrderLine lineNo="1">
                <Description>Widget Sub Assy Std</Description>
                <Item code="12345" type="S" searchcode="">
                    <Description>Widget Sub Assy Std</Description>
                </Item>
                <Quantity>1</Quantity>
                <ItemCode>12345</ItemCode>
            </OrderLine>
        </Order>
    </Orders>
</eExact>

Here's a code example that reproduces the problem:

[xml]$salesOrder = Get-Content -Path "C:\Temp\PurchaseOrder.xml"

$salesOrderLines = @($salesOrder.SelectNodes("/eExact/Orders/Order/OrderLine"))
foreach ($salesOrderLine in $salesOrderLines) {
    $partCode = ($salesOrderLine.SelectSingleNode("//ItemCode")).'#text'
    $orderQty = ($salesOrderLine.SelectSingleNode("//Quantity")).'#text'
    $salesOrderLine.InnerXml
    "Part code = $partCode, Qty = $orderQty"
    Clear-Variable partCode, orderQty
}

With a 2 line order, this gives the output:

<Description>Widget Sub Assy Std</Description><Item code="12345" type="S" searchcode=""><Description>Widget Sub Assy Std</Description></Item><Quantity>1</Quantity><ItemCode>12345</ItemCode>  
Part code = 12345, Qty = 1  
<Description>Big Widget</Description><Item code="54321" type="S" searchcode=""><Description>Big Widget</Description></Item><Quantity>3</Quantity><ItemCode>54321</ItemCode>  
Part code = 12345, Qty = 1

With more order lines I just get more repeats of the same 1st part number and quantity. I would expect the output from the 2nd order line to be:

Part code = 54321, Qty = 3

Answer 1

The XPath expression you're using for selecting the <ItemCode> and <Quantity> nodes is incorrect. You're calling SelectSingleNode() on the correct parent node, but the expression isn't relative to that node. // means anywhere below the root node, so you're always selecting the first <ItemCode> and <Quantity> node in the XML document.

Make the expressions relative to the current node (./ for immediate child nodes, .// for any descendant of the current node) and the code will do what you want. Also, there's no need to clear the variables afterwards, so just remove the Clear-Variable statement. Remove the grouping parentheses too. There's no need for PowerShell code to look like somebody's cut their fingernails.

foreach ($salesOrderLine in $salesOrderLines) {
    $partCode = $salesOrderLine.SelectSingleNode('./ItemCode').'#text'
    $orderQty = $salesOrderLine.SelectSingleNode('./Quantity').'#text'
    "Part code = ${partCode}`nQty = ${orderQty}"
}