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(
Adding the variable now makes the expression invalid.
Solved! Go to 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:
Blog: tachytelic.net
YouTube: https://www.youtube.com/c/PaulieM/videos
If I answered your question, please accept it as a solution 😘
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?
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
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:
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
--------------------------------
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.
My output is still the same
thanks
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.
Wonderful!!! Thank you for the help.
User | Count |
---|---|
92 | |
45 | |
20 | |
19 | |
15 |
User | Count |
---|---|
136 | |
54 | |
43 | |
42 | |
31 |