XPath: Group by and Sum functions

[tenalibabu]

Hello,I have the following XML document.

 <?xml version="1.0" encoding="UTF-8"?><expenseReport>  <items>	<item>	  <dateIncurred>2006-10-23</dateIncurred>	  <category>meals</category>	  <amount>7.99</amount>	</item>	<item>	  <dateIncurred>2006-10-23</dateIncurred>	  <category>meals</category>	  <amount>6.50</amount>	</item>	<item>	  <dateIncurred>2006-10-24</dateIncurred>	  <category>meals</category>	  <amount>7.50</amount>	</item>	<item>	  <dateIncurred>2006-10-24</dateIncurred>	  <category>meals</category>	  <amount>19.50</amount>	</item>	<item>	  <dateIncurred>2006-10-25</dateIncurred>	  <category>one-time parking</category>	  <amount>15.00</amount>	</item>  </items></expenseReport>

I need to write an xpath that will give me the sum of all <amount> fields grouped by date.Something like this:2006-10-23 14.492006-10-24 27.002006-10-25 15.00or simply this14.4927.0015.00Any ideas?The following xpath query...

distinct-values(/expenseReport/items/item/dateIncurred)

returns:2006-10-23 2006-10-24 2006-10-25 I need to combine the above query with the sum function but I'm not sure how.

[boen_robot]

how about:

concat(distinct-values(/expenseReport/items/item/dateIncurred),' ',sum(/expenseReport/items/item/amount))

[tenalibabu]

how about:

concat(distinct-values(/expenseReport/items/item/dateIncurred),' ',sum(/expenseReport/items/item/amount))

I get an error that says "too many items". Isn't concat really used to concatenate 2 or more strings?Example: concat('XPath ','is ','FUN!')Result: 'XPath is FUN!'Also, how will this group the records by dateIncurred?

[boen_robot]

Too many items?!?! That's a first.Anyway, yeah, concat() is used to concatenate 2 strings, but you do want to get the result of two functions, right?I was wrong about the grouping truly. I forgot you needed that too. I think now is the time I know what exactly are you using? XSLT? If so and if (as it happens) your XSLT processor supports distinct-values(), then something like this may help:

<xsl:for-each select="distinct-values(/expenseReport/items/item/dateIncurred)"><xsl:value-of select="."/> <xsl:value-of select="sum(../amount)"/></xsl:for-each>

I don't have much experience with XSLT 2.0 yet, but I have a hunch this might work.If you're not using that, then... well.... the things get harder. I seriosly need to practice XPath 2.0 .

[tenalibabu]

You are right on. You understood me well. The XSLT you have there will work perfectly but unfortunately I have to do this with Xpath 2.0. I am using this within the BizTalk Rules Engine. Which means a single xpath statement should handle the grouping and summation. It almost seems impossible but I have not given up yet.

[boen_robot]

As far as I'm aware of, in XPath 2.0, there are also keyword, that can be used in the same way XQuery's FLWOR could be. And there are also other constructions that may help building such a query.This article on XML.com shows the new stuff. Scince I don't have anywhere to experiment theese new syntax constructs, I don't think I could do much to help you (though I didn't tested the above XSLT either).