在SQL中从XML提取数据总和 [英] Extracting SUM of data from XML in Sql

问题描述

我在SQL表中有这样的XML字段

 < Root><名称>阿波罗</名称>< Code> 1000</Code>< Code> 2000</Code>< Code> 3000</Code></Root> 

我需要编写一个SQL查询来选择名称"和代码"值的总和

  SELECTT1.c.value('Name [1] AS VARCHAR(100)')AS名称，T1.c.value('Code [1] AS NUMERIC(10,5)')AS TotalCodeFROM TableName交叉应用xmlField.nodes('Root')AS T1(c) 

它给我这样的输出:

 名称代码---------------------------阿波罗1000号阿波罗2000阿波罗3000 

但是我需要像这样的所有Code标记的值之和:

 名称代码---------------------------阿波罗6000 

任何想法如何获取标签值的总和?

解决方案

这不是最优雅"的路线，我敢肯定还有更直接的路线，但是您可以尝试一下

 选择B.姓名，SUM(B.TotalCode)从(选择T1.c.value('Name [1]'，'VARCHAR(100)')AS名称，T1.c.value('Code [1]'，'NUMERIC(10,5)')AS TotalCodeFROM TableName交叉应用xmlField.nodes('Root')AS T1(c))AS B按名称分组 

基本上，这首先是将数据从XML字段项中提取出来，然后按名称分组并给出总和.就像我说的那样，虽然不优雅但是可以工作！

I have an XML field in SQL table like this

<Root>
 <Name>Apollo</Name>
 <Code>1000</Code>
 <Code>2000</Code>
 <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!

这篇关于在SQL中从XML提取数据总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！