Xquery SUM after value has been rounded

Question

I want to sum the quantity of all products/Product/Quantity after the value has been rounded.

My XML looks like:

<Products>
  <Product>
    <ExternalId>116511</ExternalId>
    <Price>2.99 </Price>
    <Quantity>1.500 </Quantity>
    <NetValue>4.08 </NetValue>
  </Product>
  <Product>
    <ExternalId>116510</ExternalId>
    <Price>2.99 </Price>
    <Quantity>1.500 </Quantity>
    <NetValue>4.08 </NetValue>
  </Product>
  <Product>
    <ExternalId>116512</ExternalId>
    <Price>1.99 </Price>
    <Quantity>10.000 </Quantity>
    <NetValue>18.09 </NetValue>
  </Product>
  <Product>
    <ExternalId>329245</ExternalId>
    <Price>59.99 </Price>
    <Quantity>1.000 </Quantity>
    <NetValue>54.53 </NetValue>
  </Product>
</Products>

The above XML is stored in x with column of Data.

I've tried using xQuery sum and round functions like so but this only rounds and sums the first instance of Quantity (i.e. sum(round(1.5)) = 2) :

SELECT Data.Value('(Products/Product/ExternalId/text()[1]', 'float') AS ExternalId,
x.Data.value('sum(round((/row/Products[1]/Product/Quantity)[1]))', 'float') Trn_Quantity
FROM x

Not sure I understand what your query is trying to do. You want the _first_ ExternalId along with the sum of _all_ Quantities? — Joachim Isaksson, Feb 08 '16 at 07:19

score 2 · Accepted Answer · answered Feb 08 '16 at 06:54

2

You can try using XQuery for loop construct to round individual Quantity and pass them to sum(), something like this :

SELECT 
    Data.Value('(Products/Product/ExternalId/text()[1]) AS ExternalId,
    x.Data.value('
        sum(
            for $quantity in /Products[1]/Product/Quantity
            return round($quantity)
        )
    ', 'float') Trn_Quantity
FROM x

Quick test here : http://sqlfiddle.com/#!3/9eecb7/7351

answered Feb 08 '16 at 06:54

har07

88,338
12
84
137

Thanks har07, that works perfectly. Didn't know you could use loops in xQuery. You learn something new everyday. – user2034250 Feb 08 '16 at 09:49

score 1 · Answer 2 · edited Feb 09 '16 at 14:41

You could use CROSS APPLY to get all quantity nodes, then just sum them up in regular SQL;

SELECT SUM(ROUND(p.value('.', 'float'), 0)) AS Quantity
FROM mytable x
CROSS APPLY data.nodes('/Products/Product/Quantity') t(p)

...or, to sum other product fields too, get the product nodes with cross apply and sum the sub-nodes by name;

SELECT SUM(p.value('(./Price)[1]', 'float')) AS Price,
       SUM(ROUND(p.value('(./Quantity)[1]', 'float'), 0)) AS Quantity
FROM mytable x
CROSS APPLY data.nodes('/Products/Product') t(p)

Xquery SUM after value has been rounded

2 Answers2