using variables with Xpath

Like (0) Share

Report

Posted on 26 Sep 2022 10:04:31 by 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.

I have the same question (0)

All responses (8)

Answers (1)

rhood 47 on 27 Sep 2022 at 07:53:22

Like (0)

Report

Re: using variables with Xpath

Wonderful!!! Thank you for the help.

Was this reply helpful? Yes No
Paulie78 8,416 Moderator on 26 Sep 2022 at 16:23:47

Like (0)

Report
Re: using variables with Xpath

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.

Was this reply helpful? Yes No
rhood 47 on 26 Sep 2022 at 16:03:41

Like (0)

Report

Re: using variables with Xpath

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

My output is still the same

thanks

Was this reply helpful? Yes No
Paulie78 8,416 Moderator on 26 Sep 2022 at 15:52:39

Like (0)

Report

Re: using variables with Xpath

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?

Was this reply helpful? Yes No
rhood 47 on 26 Sep 2022 at 15:40:47

Like (0)

Report

Re: using variables with Xpath

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

Was this reply helpful? Yes No
Verified answer

Paulie78 8,416 Moderator on 26 Sep 2022 at 13:38:37

Like (0)

Report

Re: using variables with Xpath

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 😘

Was this reply helpful? Yes No
rhood 47 on 26 Sep 2022 at 12:43:00

Like (0)

Report

Re: using variables with Xpath

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

Was this reply helpful? Yes No
Paulie78 8,416 Moderator on 26 Sep 2022 at 12:01:32

Like (0)

Report
Re: using variables with Xpath

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?

Was this reply helpful? Yes No