How to sum XML node value in SQL Server

Question

I want to sum xml node values, but luck is not favouring, please help.

select 
    animals_quantity.value('data(/AnimalsAddData/Quantity/@value)','int') 
from 
    tbl_animals

Show us a sample of the XML !

marc_s
– marc_s

2016-08-12 12:54:43 +00:00
Commented Aug 12, 2016 at 12:54 — marc_s
– marc_s, Commented Aug 12, 2016 at 12:54

Paweł Dyl · Accepted Answer · 2016-08-12 14:43:41Z

1

Take values from node and pass them to SUM aggregate:

DECLARE @xml xml = '
<AnimalsAddData>
    <Quantity value="4" />
    <Quantity value="1" />
    <Quantity value="10" />
    <Quantity value="200" />
</AnimalsAddData>'

SELECT SUM(N.value('.', 'int'))
FROM @xml.nodes('/AnimalsAddData/Quantity/@value') T(N)

Make sure you include sample data.

answered Aug 12, 2016 at 14:43

Paweł Dyl

9,1831 gold badge13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Khan · Accepted Answer · 2016-08-16 08:04:23Z

0

I find a very easy solution for it.

SELECT  sum(CAST(CAST(CAST(animals_quantity AS XML) AS VARCHAR(100)) AS INT)) as total from tbl_animals

answered Aug 16, 2016 at 8:04

Khan

5,3045 gold badges25 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to sum XML node value in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related