Extracting SUM of data from XML in Sql
I have an XML field in SQL table like this
<Root>
<Name>Apollo</Name>
<Code>1000</Code>
<Code>2000</Cod开发者_开发百科e>
<Code>3000</Code>
</Root>
I need to write an SQL query to select the 'Name' and SUM of 'Code' values
SELECT
T1.c.value('Name[1] AS VARCHAR(100)') AS Name,
T1.c.value('Code[1] AS NUMERIC(10,5)') AS TotalCode
FROM TableName
CROSS APPLY xmlField.nodes('Root') AS T1(c)
it gives me output like this:
Name Code
---------------------------
Apollo 1000
Apollo 2000
Apollo 3000
But I need SUM of values of all the Code tags like this:
Name Code
---------------------------
Apollo 6000
Any ideas how to get sum of tag values?
This isn't the most "elegant" and I'm sure there is a more direct route, but you can try this
Select
B.Name,
SUM(B.TotalCode)
FROM
(
SELECT
T1.c.value('Name[1]', 'VARCHAR(100)') AS Name,
T1.c.value('Code[1]', 'NUMERIC(10,5)') AS TotalCode
FROM TableName
CROSS APPLY xmlField.nodes('Root') AS T1(c)
) AS B
GROUP BY Name
Basically this first pulls the data out of the XML field items and then groups by Name and gives the sum. Like I said, not elegant but works!
You can use this XQuery:
select t.xmlField.value('(//Name)[1]', 'varchar(max)')
, t.xmlField.value('fn:sum(//Code)', 'int')
from @t t
Sample data:
declare @t table(xmlField xml)
insert @t values('<Root>
<Name>Apollo</Name>
<Code>1000</Code>
<Code>2000</Code>
<Code>3000</Code>
</Root>'),
('<Root>
<Name>Apollo1</Name>
<Code>1000</Code>
<Code>2000</Code>
</Root>'),
('<Root>
<Name>Apollo3</Name>
<Code>1000</Code>
<Code>2000</Code>
<Code>13000</Code>
</Root>')
Output:
----------------------
Apollo 6000
Apollo1 3000
Apollo3 16000
SELECT
T1.c.value('Name[1]', 'VARCHAR(100)') AS Name,
T1.c.value('sum(Code)', 'NUMERIC(10,5)') AS TotalCode
FROM TableName
CROSS APPLY xmlField.nodes('/Root') AS T1(c)
精彩评论