using variables with Xpath

[rhood]

Hello, I am new to xpath and have the following issues.

I am inputting XML with the the following excerpt:

<ItemOut requisitionID="3761071" lineNumber="1" quantity="1">
<ItemID>
<SupplierPartID>BNPMARKETING003</SupplierPartID>
</ItemID>
</ItemOut>
<ItemOut requisitionID="3761072" lineNumber="2" quantity="1">
<ItemID>
<SupplierPartID>BNPMARKETING005</SupplierPartID>
</ItemID>
</ItemOut>

I am using xpath to put out the values of node through out with the follow style:

xpath(

xml(outputs('Input_Xml')), 

'string(//ItemCode)'

)

I use string as the outputs are always [ ] if I dont.

Now, as the number of ItemOut nodes are unknow each time I wanted to loop the following:

var = 

xpath(

xml(outputs('Input_Xml')), 

'string(variables('Path'))')

)

Adding the variable now makes the expression invalid.

[Paulie78]

Would you mind rephrasing the question a little bit please? What you want to do should be easy, but I am unclear as to what output you are trying to achieve. 

If this is your input:

<ItemOut requisitionID="3761071" lineNumber="1" quantity="1">
<ItemID>
<SupplierPartID>BNPMARKETING003</SupplierPartID>
</ItemID>
</ItemOut>
<ItemOut requisitionID="3761072" lineNumber="2" quantity="1">
<ItemID>
<SupplierPartID>BNPMARKETING005</SupplierPartID>
</ItemID>
</ItemOut>

What is your desired output?

[rhood]

Of course.

I need to complete a spreadsheet where I would, at worst, populate a cell with the content:

BNPMARKETING003, QTY1, BNPMARKETING005, QTY1

As this are products, the numbers of products ordered is not know up front.

thanks

[Paulie78 · Accepted solution]

It's not complex enough to require xpath, but there are still a couple of complications. I made you an unlisted video to explain how I did it:

https://youtu.be/yNgO0pL_8vg

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

[rhood]

That's a great help. To add a little context, each XML file has an orderID.

--------------------------------
I grab the ID now with your JSON command

json(xml(outputs('Compose')))?['cXML']?['OrderRequest']?['OrderRequestHeader']?['@orderID']

--------------------------------

Below is the full file.

<?xml version="1.0" encoding="UTF-8"?>
<cXML payloadID="1860247-580-22-09-20T12:11:44" timestamp="2022-09-20T12:11:44">
<OrderRequest>
<OrderRequestHeader type="new" orderID="581" orderDate="2022-09-20T12:11:44">
<Request deploymentMode="production">
<ShipTo>
<Address addressID="1764d5dc-5cf6-407c-9928-6cca69f25666">
<Name xml:lang="en">1764d5dc-5cf6-407c-9928-6cca69f25666</Name>
<PostalAddress name="1764d5dc-5cf6-407c-9928-6cca69f25666">
<ContactName>BNP_Coreprint_Orders</ContactName>
<DeliverTo>test</DeliverTo>
<Street>Str1</Street>
<Street>Str2</Street>
<Street>Str3</Street>
<Street>Str4</Street>
<Street>Str5</Street>
<City>City</City>
<State>County</State>
<PostalCode>PCode</PostalCode>
<Country isoCountryCode="GB">United Kingdom</Country>
</PostalAddress>
<Telephone>0207000000</Telephone>
</Address>
</ShipTo>
</Request>
</OrderRequestHeader>
<SupplierOrderInfo orderID="1860247" />
<Extrinsic name="Username">BNP_Coreprint_Orders</Extrinsic>
<ItemOut requisitionID="3761071" lineNumber="1" quantity="1">
<ItemID>
<SupplierPartID>BNPMARKETING003</SupplierPartID>
</ItemID>
</ItemOut>
<ItemOut requisitionID="3761072" lineNumber="2" quantity="1">
<ItemID>
<SupplierPartID>BNPMARKETING005</SupplierPartID>
</ItemID>
</ItemOut>
</OrderRequest>
</cXML>

--------------------------------

If I add the values from the products in ItemOut to an excel sheet with apply to each, I get 2 rows created

581 - BNPMARKETING003 - 1

581 - BNPMARKETING005 - 1

--------------------------------

I'd like to put the product values into a single cell

581 - BNPMARKETING003,1,BNPMARKETING005,1

--------------------------------

[Paulie78]

Simply modify the select action and put it into "text mode" and instead of creating name/value pairs you can create a string - this will give you a single value that you can insert into Excel. Does that make sense?

[rhood]

I do as I found this as a solution online, though I must have made a mistake.

My output is still the same

thanks

[Paulie78]

You need to change the select action to build you a string:

Expression I used is:

concat(item()['@requisitionID'], ' - ', item()['ItemID']['SupplierPartID'], '-', item()['@quantity'])

Those changes now make it produce the output like this:

Which is an array of strings. This will go into a single cell in Excel. You might need to play with the expression a bit to get it just how you want it.

[rhood]

Wonderful!!! Thank you for the help.