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..

Answer 1

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.

Answer 2

I think you want this XPath 2.0 expression:

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