CODEWITHSUNDEEP
sql-serverxml
Khan
Khan

Reputation: 5204

How to sum XML node value in SQL Server

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

Upvotes: 0

Views: 637

Answers (2)

Khan
Khan

Reputation: 5204

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

Upvotes: 0

Paweł Dyl
Paweł Dyl

Reputation: 9143

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.

Upvotes: 1

Related Questions