CODEWITHSUNDEEP
sql-servergroup-byxml-parsing
Skary
Skary

Reputation: 1362

SQL Server group by key and sum over an xml column

As stated in the title i have a table with a composed key (main key and position key) and a Value column that contain an XML (with a fixed schema).

The fixed XML appear like the subsequent :

 <Data>
     <ItemACount></ItemACount>
     <ItemBCount></ItemBCount>
 </Data>

Both ItemACount and ItemBCount represent positive integer number.

I would like group records that have the same main key (but different position key) then, for each group calculate the sum of each ItemACount and ItemBCount.

I write the SQL code as below :

SELECT
    MainKey AS MainKey 
    SUM ( [Value].value('/Data/ItemACount/@value') ) AS TotalItemACount ,
    SUM ( [Value].value('/Data/ItemBCount/@value') ) AS TotalItemBCount 
FROM 
    [dbo].[tblItems]
GROUP BY 
    [MainKey]

But I get a syntax error:

Cannot find either column "Value" or the user-defined function or aggregate "Value.value", or the name is ambiguous.

I would like to understand which is the correct syntax.

Upvotes: 0

Views: 429

Answers (1)

Ruslan Tolkachev
Ruslan Tolkachev

Reputation: 644

Try this 

SELECT
   MainKey AS MainKey 
   SUM ( [Value].value(('/Data/ItemACount/@value)[1]', 'int')) AS TotalItemACount ,
   SUM ( [Value].value(('/Data/ItemBCount/@value)[1]', 'int')) AS TotalItemBCount 
   FROM [dbo].[tblItems]
   GROUP BY [MainKey]

Upvotes: 1

Related Questions