XPath Summation by Substructure

Question

How to write XPath sum by 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 amount (quantity * itemprice)

I can do it:

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

But I want to use Item * Item Item, but I can not determine 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

This is the correct XQuery syntax, so it can work with MS SQL Server, as it has limited support for XQuery.

Answer 2

I think you need this XPath 2.0 expression:

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

Answer 3

Using

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

when an XPath 2.0 expression (as well as XQuery) is evaluated against the provided XML document, the desired, correct result is obtained:

400

So, a complete SQL query might look like this:

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