XPath Sum over sub structure

Question

How do I write an XPath sum over the following structure ?

<Order>
     <Details>
        <Detail>
            <Quantity>10</Quantity>
            <ItemPrice>20</Quantity>
        </Detail>
        <Detail>
            <Quantity>10</Quantity>
            <ItemPrice>20</Quantity>
        </Detail>
     </Details>
 </Order>

I want to get the sum of (quantity * itemprice)

I can do this:

select *
from mytable
where documentdata.exist('/Order[sum(/Details/Detail/Quantity) > 20]) = 1

But I want to use Quantity * ItemPrice but I cant figure out the syntax for it..

If you're using xpath 1.0, I suspect you're out of luck, going by the info in [this question](http://stackoverflow.com/questions/436998/multiply-2-numbers-and-then-sum-with-xslt) (which is about xslt rather than plain xpath, so isn't an exact duplicate). — AakashM, Feb 28 '11 at 10:29
I'm using XML columns in Sql Server 2008.. and I'm querying those using mytable.exists( xpath ) = 1.... no idea what version of XPath sql server uses — Roger Johansson, Feb 28 '11 at 10:50
I'm fairly sure there is no Microsoft product that supports xpath 2.0, unfortunately. — AakashM, Feb 28 '11 at 11:28
So sum can not operate over multiple tags? as in the above example? — Roger Johansson, Feb 28 '11 at 11:37
@AakashM: SQL-Server implements a not full standard complain XQuery/XPath 2.0 engine. — , Feb 28 '11 at 12:15
@Roger Alsing: Check my answer for a solution without iteration. — , Feb 28 '11 at 12:16
Good question, +1. See my answer for the simplest and shortest XPath 2.0 expression to use. :) — Dimitre Novatchev, Feb 28 '11 at 14:31

score 4 · Accepted Answer · answered Feb 28 '11 at 11:46

4

Try

select *
from mytable
where documentdata.exist('/Order[sum(for $d in Details/Detail return $d/Quantity * $d/ItemPrice) > 20]) = 1

That is the right XQuery syntax so it might work with MS SQL Server as it has some limited XQuery support.

answered Feb 28 '11 at 11:46

Martin Honnen

160,499
6
90
110

I've now implemented this in my Linq to SqlXml framework: http://rogeralsing.com/2011/02/28/linq-to-sqlxml/ – Roger Johansson Feb 28 '11 at 12:46

score 1 · Answer 2 · answered Feb 28 '11 at 12:31

1

I think you want this XPath 2.0 expression:

/Order[sum(Details/Detail/(Quantity * ItemPrice)) gt 20]

answered Feb 28 '11 at 12:31

Dimitre Novatchev · Answer 3 · 2011-02-28T14:29:38.613

1

Use:

/*/*/sum(Detail/(Quantity * ItemPrice))

when this XPath 2.0 (and also XQuery) expression is evaluated against the provided XML document, the wanted, correct result is produced:

So, the complete SQL query might look like this:

select * from mytable
  where documentdata.exist
    ('/*[sum(*/Detail/(Quantity * ItemPrice)) > 20]' ) = 1

edited Feb 28 '11 at 14:29

answered Feb 28 '11 at 14:20

Dimitre Novatchev

240,661
26
293
431

XPath Sum over sub structure

3 Answers3