CODEWITHSUNDEEP
xqueryxquery-sql
Puru
Puru

Reputation: 47

Sum using XQuery

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>

Upvotes: 2

Views: 6532

Answers (3)

Jens Erat
Jens Erat

Reputation: 38732

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.

Upvotes: 6

dan radu
dan radu

Reputation: 2782

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.

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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

Upvotes: 3

Related Questions