Answered

Merging Array Xpath Error

(0) Share

Report

Posted on by Mastermind85

I've been trying to use the following guidance to merge to arrays with a common identifier, however I keep having errors: [How to merge arrays in Power Automate (tachytelic.net)](https://www.tachytelic.net/2022/07/power-automate-merge-array/#:~:text=How%20to%20merge%20two%20arrays%20in%20Power%20Automate,expression%20to%20perform%20this%20in%20a%20single%20step.)

I am using the following formula: addProperty
(
item(),
'Request_x0020_Status',
xpath(outputs('XML'), concat('//Array[Reference/text()="', item()?['Reference'], '"]/Request_x0020_Status/text()'))?[0]
)

and I get the following error: The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{
"ID": "@item()?['ID']",
"Reference": "@item()?['Reference']",
"Status": "@xpath\r\n(\r\n outputs('XML'), \r\n concat('//Array[Reference/text()=\"', item()['Reference'], '\"]/Request_x0020_Status/text()')\r\n)?[0]"
}' failed: 'The template language function 'xpath' parameters are invalid: the 'xpath' parameter must be a supported, well formed XPath expression. Please see https://aka.ms/logicexpressions#xpath for usage details.'.

Any ideas of what could be wrong?

For reference, here is the example XML I am trying to search through:

<root>
<Array>
<ASID>98111</ASID>
<Reference>ICT-27</Reference>
<Request_x0020_Status>Accepted</Request_x0020_Status>
<Due_x0020_Date>2022-09-26T00:00:00.000Z</Due_x0020_Date>
<Title>Title of Property</Title>
<Instructions>Tested</Instructions>
<Population_x0020_Request>No</Population_x0020_Request>

</Array>

and my array I am adding to example is:

[

{
"ID": 1139,
"Reference": [
"ICT-27",
" ICT-29",
" ICT-33"
]
}

I am looking to add the Request_x0020_Status text to the array for each from the first array.

Appreciate the help!

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

grantjenkins 11,063 Moderator on at

Like (0)

Report

Are you able to show the raw JSON data before you convert to XML, and the expression you used to convert to XML?

You seem to have a lot of \ throughout your data which is a bit strange.

Also, are you able to show what you would expect to see as final output? Just a bit confused what you're trying to get as an end result.

Was this reply helpful? Yes No
Mastermind85 14 on at

Like (0)

Report

Hi! I just realized the forum post added all those "/" for some reason, I just edited to correct. The XML seems to be produced correctly.
Example Pre-XML JSON:
[
{
"ASID": "98111",
"Reference": "ICT-27",
"Request Status": "Accepted",
"Due Date": "2022-09-26T00:00:00.000Z",
"Title": "Title of Property",
"Instructions": "Tested",
"Population Request": "No"
}
]

XML Conversion:
{
"root": {
"Array": @{body('Select_All_AS_Data_-_Build_Array')}
}
}

Based on my example I would like to return the following:
[
{
"ID": 1139,
"Reference": [
"ICT-27",
" ICT-29",
" ICT-33"
],
"Request_x0020_Status": [
"Accepted",
"",
""
]
}
]

In the end I would like to do this for multiple fields and make sure that the placement is in the same as the Reference. Thanks!

Was this reply helpful? Yes No
Mastermind85 14 on at

Like (0)

Report

See response (not sure I replied directly to you for notification)

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report

I'll see what I can do for you tomorrow (just off to sleep now - just past midnight for me).

Just a question - how many objects will you likely have in your Pre-XML JSON array?

Was this reply helpful? Yes No
Mastermind85 14 on at

Like (0)

Report

No problem, I really appreciate it! There could potentially be hundreds of objects in the array (if not thousands) with ~18 properties each. If you can think of a more efficient way to do this, I'm open to alternatives 🙂

Was this reply helpful? Yes No
Mastermind85 14 on at

Like (0)

Report

Any luck? Thanks!

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like (1)

Report
@Mastermind85 Sorry for the delay in getting back to you - this one fell off my radar for a bit. Hopefully this is what you're looking for.

See full flow below. I'll go into each of the actions.

Data is a Compose that contains the data including Request Status. I've added a sample here for testing purposes.
[ { "ASID": "98111", "Reference": "ICT-27", "Request Status": "Accepted", "Due Date": "2022-09-26T00:00:00.000Z", "Title": "Title of Property", "Instructions": "Tested", "Population Request": "No" }, { "ASID": "98112", "Reference": "ICT-31", "Request Status": "Pending", "Due Date": "2022-09-26T00:00:00.000Z", "Title": "Title of Property", "Instructions": "Tested", "Population Request": "No" }, { "ASID": "98113", "Reference": "ICT-29", "Request Status": "Declined", "Due Date": "2022-09-26T00:00:00.000Z", "Title": "Title of Property", "Instructions": "Tested", "Population Request": "No" }, { "ASID": "98114", "Reference": "ICT-39", "Request Status": "Declined", "Due Date": "2022-09-26T00:00:00.000Z", "Title": "Title of Property", "Instructions": "Tested", "Population Request": "No" } ]

XML is a Compose that converts the data to XML.
xml(json(concat('{"root": { item:', outputs('Data'), '}}')))

Items is a Compose that contains your items.
[ { "ID": 1139, "Reference": [ "ICT-27", "ICT-29", "ICT-33" ] }, { "ID": 1140, "Reference": [ "ICT-35", "ICT-39", "ICT-43" ] } ]

Initialize variable creates a variable called data of type Array that will eventually contain all the data.

Apply to each iterates over each of the items.

Select uses the following expressions to extract the Request Status for each Reference, in the same order as they appear in the item.
//From item()?['Reference'] //Map xpath(outputs('XML'), concat('string(//root/item[Reference="', item(), '"]/Request_x0020_Status/text())'))

Append to array variable adds a new property (our Request Status array) to the current item and appends it to the data array we created earlier.
addProperty(item(), 'RequestStatus', body('Select'))

After the Apply to each I've put a Compose called Result that just shows the data that's stored in our data Array.

After running the flow, Result (output from our data array) would have the following output.
[ { "ID": 1139, "Reference": [ "ICT-27", "ICT-29", "ICT-33" ], "RequestStatus": [ "Accepted", "Declined", "" ] }, { "ID": 1140, "Reference": [ "ICT-35", "ICT-39", "ICT-43" ], "RequestStatus": [ "", "Declined", "" ] } ]

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
mastermind850 5 on at

Like (0)

Report

Sorry for the late reply as I was travelling. Just tried this now and seems to be working. Thanks so much!!

Was this reply helpful? Yes No