tenalibabu Posted November 2, 2006 Share Posted November 2, 2006 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. Link to comment Share on other sites More sharing options...
boen_robot Posted November 2, 2006 Share Posted November 2, 2006 how about: concat(distinct-values(/expenseReport/items/item/dateIncurred),' ',sum(/expenseReport/items/item/amount)) Link to comment Share on other sites More sharing options...
tenalibabu Posted November 2, 2006 Author Share Posted November 2, 2006 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? Link to comment Share on other sites More sharing options...
boen_robot Posted November 3, 2006 Share Posted November 3, 2006 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 . Link to comment Share on other sites More sharing options...
tenalibabu Posted November 3, 2006 Author Share Posted November 3, 2006 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. Link to comment Share on other sites More sharing options...
boen_robot Posted November 4, 2006 Share Posted November 4, 2006 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). Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now