Build an xpath expression with sum()

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

[Expiscornovus]

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(xml(outputs('Compose_XML')), 'sum(/produce/item/count)')

[chandlers201]

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?

[Expiscornovus]

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?

[chandlers201]

Hi there

using the sum() to add up the amount.

[Expiscornovus]

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

[chandlers201]

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

[Expiscornovus]

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

[chandlers201]

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?

[chandlers201]

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

[Expiscornovus]

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