sqlserver中xml格式数据的分组方式

pbpqsu0x 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(469)

我带着这些数据。

declare @T table
(
  ID int,
[subject]  varchar(30),
Marks int
)

insert into @T values  
(1, 'Maths',78),  
(1, 'Science',89),  
(2, 'Maths',90),  
(3, 'Maths',91),  
(4, 'Maths',92)

我尝试了以下查询：

SELECT ID
 ,(SELECT t1.* FOR XML PATH('body'),TYPE) AS TheRowAsXml
FROM @T as t1

但它为每个条目提供一行。
我想按id分组
我的预期输出如下：

<body>
      <ID>1</ID>
      <subject>Maths</subject>
      <Marks>78</Marks>
      <subject>Science</subject>
      <Marks>89</Marks>
    </body>

对于id 1等等
如果您有任何建议，我们将不胜感激

sql sql-server sql-server-2012

来源：https://stackoverflow.com/questions/62709316/how-to-use-group-by-format-for-xml-format-data-in-sql-server

1条答案

按热度按时间

ljo96ir51#

可以使用子查询生成，如下所示：

declare @T table
(
  ID int,
[subject]  varchar(30),
Marks int
)

insert into @T values
(1, 'Maths',78),
(1, 'Science',89),
(2, 'Maths',90),
(3, 'Maths',91),
(4, 'Maths',92)

--SELECT id, subject, marks from @T WHERE id = 1
--for xml path('')

SELECT distinct  id,CONCAT('<body><ID>',cast(id as varchar(10)),'</ID>',
(SELECT subject, marks from @T WHERE id = t.id
for xml path('')
),'</body>') as Rowxml
from @t as t

+----+-------------------------------------------------------------------------------------------------------------+
| id |                                                   Rowxml                                                    |
+----+-------------------------------------------------------------------------------------------------------------+
|  1 | <body><ID>1</ID><subject>Maths</subject><marks>78</marks><subject>Science</subject><marks>89</marks></body> |
|  2 | <body><ID>2</ID><subject>Maths</subject><marks>90</marks></body>                                            |
|  3 | <body><ID>3</ID><subject>Maths</subject><marks>91</marks></body>                                            |
|  4 | <body><ID>4</ID><subject>Maths</subject><marks>92</marks></body>                                            |
+----+-------------------------------------------------------------------------------------------------------------+

更新
一个更干净的方法

SELECT id "ID",max(t2.rowxml)
from @t as t
CROSS APPLY 
(SELECT subject, marks 
from @T WHERE id = t.id
for xml path('')
) as t2(rowxml)
group by t.id
for xml path('body')

赞(0）回复(0）举报 2021-07-29

我来回答

sqlserver中xml格式数据的分组方式

1条答案

相关问题

热门标签

最新问答