Build an xpath expression with sum()

Like (0) Share

Report

Posted on 9 May 2024 11:35:28 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!

All responses (10)

Answers (0)

Expiscornovus 31,701 Most Valuable Professional on 13 May 2024 at 08:37:52

Like (0)

Report
Re: Build an xpath expression with sum()

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)'))
chandlers201 12 on 11 May 2024 at 17:31:21

Like (0)

Report
Re: Build an xpath expression with sum()

xpath(xml(outputs('Compose_XML')),'sum(//values/finops_amount)')
chandlers201 12 on 11 May 2024 at 17:26:28

Like (0)

Report
Re: Build an xpath expression with sum()

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?
Expiscornovus 31,701 Most Valuable Professional on 10 May 2024 at 09:32:09

Like (0)

Report
Re: Build an xpath expression with sum()

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 12 on 09 May 2024 at 16:16:41

Like (0)

Report

Re: Build an xpath expression with sum()

Thanks for that. I am looking for the total amount per unique account.
Expiscornovus 31,701 Most Valuable Professional on 09 May 2024 at 16:06:17

Like (0)

Report
Re: Build an xpath expression with sum()

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 12 on 09 May 2024 at 15:57:52

Like (0)

Report

Re: Build an xpath expression with sum()

Hi there
using the sum() to add up the amount.
Expiscornovus 31,701 Most Valuable Professional on 09 May 2024 at 13:31:16

Like (0)

Report

Re: Build an xpath expression with sum()

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 12 on 09 May 2024 at 13:07:12

Like (0)

Report

Re: Build an xpath expression with sum()

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 31,701 Most Valuable Professional on 09 May 2024 at 12:04:19

Like (0)

Report
Re: Build an xpath expression with sum()

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