Unanswered

Build an xpath expression with sum()

(0) Share

Report

Posted on by chandlers201

Hi there,

I am trying to build a flow with XPath in the 'Select'. When I try to add the sum() to the XPath below, I get 'an invalid expression' error.

This statement works!

xpath(outputs('Compose_XML'), concat('(//values[EmployeeNumber=',item(),']/Email)[1]/text()')))

This statement with the sum() doesn't work!

xpath(outputs('Compose_XML'), concat('(sum//values[EmployeeNumber=',item(),']/Email)[1]/text()')))

I appreciate any help!

Thanks in advance!

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (0)

Sort by

Expiscornovus 33,855 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @chandlers201,

Afaik the sum function needs to have parentheses as well, which are missing in your example:

https://learn.microsoft.com/en-us/previous-versions/dotnet/netframework-4.0/ms256160(v=vs.100)

Below is an example (based on Example 7 of the Microsoft docs, https://learn.microsoft.com/en-gb/azure/logic-apps/workflow-definition-language-functions-reference#xpath)

xpath(xml(outputs('Compose_XML')), 'sum(/produce/item/count)')

Was this reply helpful? Yes No
chandlers201 12 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your response. I am using the 'Select' to access the list of accounts, (e.g. ACT001, ACT002, etc). In the XPath expression, I have used concat around the item() to return me each account. I am not sure how to build the XPath with the sum()? Do I need to use concat?

Was this reply helpful? Yes No
Expiscornovus 33,855 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @chandlers201,

Can you share the xml which is used in the Compose XML? Interested to see the schema of your xml. Obfuscate data if needed.

And what are you trying to sum?

Was this reply helpful? Yes No
chandlers201 12 on at

Like (0)

Report
Copy link

Link copied!

Hi there
using the sum() to add up the amount.

Was this reply helpful? Yes No
Expiscornovus 33,855 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @chandlers201,

Thanks for sharing.

Just to double check. Do you need the total amount of all accounts together? Or the total amount per unique account?

In case of the first scenario you could just use the below in a Compose action.

xpath(xml(outputs('Compose_XML')), 'sum(/root/values/amount)')

Test run

Was this reply helpful? Yes No
chandlers201 12 on at

Like (0)

Report
Copy link

Link copied!

Thanks for that. I am looking for the total amount per unique account.

Was this reply helpful? Yes No
Expiscornovus 33,855 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @chandlers201,

Can you try the below?

From

xpath(xml(outputs('Compose_XML')), '//root/values')

Account

join(xpath(item(), '//accountid[1]/text()'), '')

Amount

xpath(xml(outputs('Compose_XML')), concat('sum(//root/values[accountid=', '''', join(xpath(item(), '//accountid[1]/text()'), ''), '''', ']/amount)'))

My test run

You will also need to use a union after the select to remove the duplicates.

union(body('Select'),body('Select'))

Was this reply helpful? Yes No
chandlers201 12 on at

Like (0)

Report
Copy link

Link copied!
Thanks for this. I really appreciate it.
join(xpath(item(), '//accountid[1]/text()'), '')
I tried running this statement but I am getting an error. Does it need to be converted to XML?

Was this reply helpful? Yes No
chandlers201 12 on at

Like (0)

Report
Copy link

Link copied!
xpath(xml(outputs('Compose_XML')),'sum(//values/finops_amount)')

Was this reply helpful? Yes No
Expiscornovus 33,855 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @chandlers201,

Yes, I converted what is in the Compose (which will be text as output) to xml first, before using it in the Select action From field.

I see you have a different xml schema now compared to what you shared earlier.

Try and update the xpath expressions to use the names of your xml schema, below is an updated example.

From field of the Select action

xpath(xml(outputs('Compose_XML')), '//root/values')

Account value in the Map field of the Select action

join(xpath(item(), '//finops_accountid[1]/text()'), '')

Amount value in the Map field of the Select action

xpath(xml(outputs('Compose_XML')), concat('sum(//root/values[finops_accountid=', '''', join(xpath(item(), '//finops_accountid[1]/text()'), ''), '''', ']/finops_amount)'))

Was this reply helpful? Yes No