sum some xml nodes values in sql server 2008

Question

Please consider this XML:

<Parent ID="p">
    <Child ID="1">10</Child > 
    <Child ID="2">20</Child > 
    <Child ID="3">0</Child > 
</Parent > 

I want to SUM all child value inside the Parent node with ID="p".for above example I want query return 30

How I can do this?

Answer 1

select @xml.value('sum(/Parent[@ID = "p"]/Child)', 'float') as Sum

The use of float protects against there being no Parent with that ID. You can then cast this result to int.

Answer 2

Try this :-

 Declare @xml xml 
set @xml='<Parent ID="p">
         <Child ID="1">10</Child > 
         <Child ID="2">20</Child > 
         <Child ID="3">0</Child > 
          </Parent >'

 Select @xml.value('sum(/Parent/Child)','int') as Sum

Result : 30

or if you want the sum for a specific Parent ID then try the below query

 Select @xml.value('sum(/Parent/Child)','int') AS SumVal
 where @xml.exist('/Parent[@ID="p"]') = 1;

Demo in SQL FIDDLE