CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008xpath
DooDoo
DooDoo

Reputation: 13447

sum some xml nodes values in sql server 2008

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?

Upvotes: 5

Views: 6873

Answers (4)

muhmud
muhmud

Reputation: 4604

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.

Upvotes: 9

Anvesh
Anvesh

Reputation: 7703

Try this :

DECLARE @SearchKeyWords XML 

SET @SearchKeyWords =
'<Parent ID=''p''>
    <Child ID="1">10</Child > 
    <Child ID="2">20</Child > 
    <Child ID="3">0</Child > 
</Parent >' 

DECLARE @TempSearchKeyWords TABLE
        (
            SearchKeyWord INT
        )

INSERT INTO @TempSearchKeyWords                     
SELECT SearchKeyWords.SearchKeyword.value('.',' int ') AS SearchKeyword             
FROM @SearchKeyWords.nodes('/Parent/Child') AS SearchKeyWords(SearchKeyword)

SELECT SUM(SearchKeyWord) FROM @TempSearchKeyWords

This will return 30.

Upvotes: 1

praveen
praveen

Reputation: 12271

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

Upvotes: 4

Devart
Devart

Reputation: 121932

Try this one -

DECLARE @XML XML
SELECT @XML = '
<Parent ID="p">
    <Child ID="1">10</Child > 
    <Child ID="2">20</Child > 
    <Child ID="3">0</Child > 
</Parent >
<Parent ID="p2">
    <Child ID="1">15</Child > 
    <Child ID="2">20</Child > 
    <Child ID="3">5</Child > 
</Parent >'

SELECT 
      value = SUM(t.value)
    , t.id 
FROM (
    SELECT 
          value = p.value('.', 'INT')
        , id = p.value('./../@ID', 'VARCHAR(50)')
    FROM @XML.nodes('/Parent/Child') t(p)
) t
--WHERE t.id = 'p'
GROUP BY t.id

Upvotes: 1

Related Questions