Sum using XQuery

Question

I'm using XQuery to perform addition. Following is the structure of XML saved in database:

    <Events>
        <Event>
            <id>1</id>
            <code>1001</code>
            <Amount>50,1</Amount>
        </Event>
        <Event>
            <id>1</id>
            <code>1002</code>
            <Amount>5,5</Amount>
        </Event>
             <Event>
            <id>1</id>
            <code>1001</code>
            <Amount>50,1</Amount>
        </Event>
        <Event>
            <id>1</id>
            <code>1002</code>
            <Amount>5,5</Amount>
        </Event>
    </Events>

I want to get below output by using XQuery: the sum of amount having same code. Please note , is .. I need to replace , by . and the perform arithmetic operation.

 <Total>               
            <1001> 100,2 </1001>
            <1002> 11,0 </1002>
   </Total>

Do you really need the the code values as node names? Could you consider another structure of your output XML where you have the code as a node value or attribute value? — Mikael Eriksson, May 18 '12 at 10:24
In your output, you can't have qualified names starting with a digit. They should start with either a letter or underscore. — dan radu, May 18 '12 at 10:27
Hi Mikael, I can use A1001 also. but I need code values as node names. — Puru, May 18 '12 at 11:25

Jens Erat · Accepted Answer · 2012-05-18T11:45:21.227

6

If your XQuery processor supports XQuery 3.0, use the group by statement.

<Total>
{
  for $i in //Event
  let $code := $i/code
  group by $code
  return element {"code"} { attribute {"id"} {$code}, sum($i/Amount)}
}
</Total>

There are two differences to the XML snippets in your question: I changed the floating point seperator to points (which is required, of course you could do this using some XQuery string operations, too) and element names may not consist of numbers only, have a look at the element naming rules. I decided to return the code as id-attribute instead in my example.

edited May 18 '12 at 11:45

answered May 18 '12 at 10:34

Jens Erat

37,523
16
80
96

Hi Ranon, I want to replace , by . and do the addition, could you please help me out in this, I'm trying to use replace function but its giving me error "SQL16003N An expression of data type "( item(), item()+ )" cannot be used when the data type "item() " is expected in the context. Error QName=err:XPTY0004." – Puru May 18 '12 at 11:30
Thank you for trying yourself before asking. :) You probably used `replace(...)` in the return-line. Replace expects a single string, not a bunch of strings. Add this line to the let-statements: `let $amount := number(replace($i/Amount, ",", "."))`. This replaces the colon with a dot and casts to a number. in `sum(...)`, sum over `$amount`. – Jens Erat May 18 '12 at 11:45
You can try the edited version of the solution live at http://www.zorba-xquery.com/html/demo#4yAN48cPJqRgjJvmSlBOzdEe0hk= It seems to work fine. – wcandillon May 18 '12 at 19:36
Thank you Ranon, I have added the let statement, but its giving same error. :(. Could you guide on this. – Puru May 21 '12 at 05:59

score 3 · Answer 2 · answered May 18 '12 at 10:30

This will get you the data as a result set.

declare @X xml
set @X = 
'<Events>
        <Event>
            <id>1</id>
            <code>1001</code>
            <Amount>50,1</Amount>
        </Event>
        <Event>
            <id>1</id>
            <code>1002</code>
            <Amount>5,5</Amount>
        </Event>
             <Event>
            <id>1</id>
            <code>1001</code>
            <Amount>50,1</Amount>
        </Event>
        <Event>
            <id>1</id>
            <code>1002</code>
            <Amount>5,5</Amount>
        </Event>
    </Events>'

select T.code,
       sum(Amount) as Amount
from
  (
    select T.X.value('code[1]', 'int') as code,
           cast(replace(T.X.value('Amount[1]', 'varchar(13)'), ',', '.') as float) as Amount
    from @X.nodes('Events/Event') as T(X)
  ) as T
group by T.code

score 2 · Answer 3 · answered May 18 '12 at 10:36

The following code will calculate the totals and output the result as XML, but not in your output (which is invalid):

SELECT Code AS 'Code', SUM(Value) AS 'Total'
FROM (
SELECT
    CONVERT(DECIMAL(9,2), REPLACE(c.value('Amount[1]', 'VARCHAR(10)'), ',', '.')) AS Value
    , c.value('code[1]', 'INT') AS Code
FROM @x.nodes('//Event') AS t(c)
) t
GROUP BY Code
FOR XML PATH('Total'), ROOT('Totals')

where @x is a XML variable containing your data.

Sum using XQuery

3 Answers3