使用TSQL如何在按字段进行分组的同时从表中返回XML?
问题描述:
这很难解释,但我会举一个例子。可以说我有如下表:使用TSQL如何在按字段进行分组的同时从表中返回XML?
PolicyNo ClientNo Name
-------- -------- ----
123 1 John
123 2 Sally
ABC 3 Alice
ABC 4 Bob
而且我想使用TSQL和“FOR XML”,以组信息在类似的策略号,如下所示:
<root>
<Policy>
<PolicyNo> 123 </PolicyNo>
<ClientInfo>
<ClientNo> 1 </ClientNo>
<Name> John </Name>
</ClientInfo>
<ClientInfo>
<ClientNo> 2 </ClientNo>
<Name> Sally </Name>
</ClientInfo>
</Policy>
<Policy>
<PolicyNo> ABC </PolicyNo>
<ClientInfo>
<ClientNo> 3 </ClientNo>
<Name> Alice </Name>
</ClientInfo>
<ClientInfo>
<ClientNo> 4 </ClientNo>
<Name> Bob </Name>
</ClientInfo>
</Policy>
</root>
答
这仅仅是一个补充自己,伟大的解决方案:
SELECT
T1.PolicyNo AS "PolicyNo",
(SELECT
T2.ClientNo AS "ClientNo",
T2.Name AS "Name"
FROM @T T2
WHERE T1.PolicyNo=T2.PolicyNo
FOR XML PATH ('ClientInfo'),TYPE)
FROM @T T1
GROUP BY T1.PolicyNo
FOR XML PATH ('Policy'), ROOT ('root')
没有必要使用CAST(... AS XML)
。只需使用,TYPE
即可强制将子选择处理为原生XML。
我不知道此刻,这是否会影响性能(读作字符串的重铸到XML),或者如果发动机是足够聪明的遭遇,实际上并不需要此投...
答
其实我身材在玩了一段时间之后,回答我自己的问题。这里是我的解决方案:
declare @T table
(
PolicyNo varchar(3),
ClientNo int,
Name varchar(10)
)
insert into @T values
('123', 1, 'John'),
('123', 2, 'Sally'),
('ABC', 3, 'Alice'),
('ABC', 4, 'Bob')
SELECT
T1.PolicyNo AS "PolicyNo",
cast((SELECT
T2.ClientNo AS "ClientNo",
T2.Name AS "Name"
FROM @T T2
WHERE T1.PolicyNo=T2.PolicyNo
FOR XML PATH ('ClientInfo')) as XML)
FROM @T T1
GROUP BY T1.PolicyNo
FOR XML PATH ('Policy'), ROOT ('root')