cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rhood
Frequent Visitor

using variables with Xpath

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 = 

rhood_0-1664186539774.png

 

xpath(

xml(outputs('Input_Xml')), 
'string(variables('Path'))')
)


Adding the variable now makes the expression invalid.

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User
Super User

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 😘

View solution in original post

8 REPLIES 8
Paulie78
Super User
Super User

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

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

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 😘

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

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

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?

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


rhood_0-1664207996625.png

 

My output is still the same

 

rhood_1-1664208093987.png

 

thanks

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

XMLtoString.png

 

Expression I used is:

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

Those changes now make it produce the output like this:

Paulie78_0-1664209389007.png

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

Wonderful!!! Thank you for the help.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (4,011)