SQL Server XML -在现有节点中嵌入数据集

Question

有许多将记录集插入XML的示例，但它们往往是非常基本的，不能满足我的特定需求。在下面的实例中，我希望将一些数据提取为XML，并且在其中，我需要一个节点集合来表示可变数量的记录。我知道这是可以做到的，因为我有一个例子，但我不明白它是如何工作的。我确实理解我的代码返回数据集可能没有意义，但它只是为了代表我试图实现的一个例子。

SELECT 'Node' AS [A/B],
(
SELECT x.Item
FROM (
SELECT 'Line1' AS [Item]
FROM (SELECT 1 AS x) x
UNION
SELECT 'Line2' AS [Item]
FROM (SELECT 1 AS x) x
) x
FOR XML PATH(''), ROOT('Lines'), TYPE
)
FROM (SELECT 1 AS x) x
FOR XML PATH('Demo')

结果如下:

<Demo>
<A>
<B>Node</B>
</A>
<Lines>
<Item>Line1</Item>
<Item>Line2</Item>
</Lines>
</Demo>

我想要的是:

<Demo>
<A>
<B>Node</B>
<Lines>
<Item>Line1</Item>
<Item>Line2</Item>
</Lines>
</A>
</Demo>

有谁能帮助我或指出正确的答案吗?

Answer 1

对源数据的结构进行一些假设，您应该能够轻松地进行调整，下面的脚本为您提供了您想要的输出，甚至跨多个Node组:

<标题>查询

declare @t table([Node] varchar(10),Line varchar(10));
insert into @t values
('Node1','Line1')
,('Node1','Line2')
,('Node1','Line3')
,('Node1','Line4')
,('Node2','Line1')
,('Node2','Line2')
,('Node2','Line3')
,('Node2','Line4')
;
with n as
(
select distinct [Node]
from @t
)
select n.[Node] as [B]
,(select t.Line as Item
from @t as t
where t.[Node] = n.[Node]
for xml path(''), root('Lines'), type
)
from n
for xml path('A'), root('Demo')
;

---

输出

<Demo>
<A>
<B>Node1</B>
<Lines>
<Item>Line1</Item>
<Item>Line2</Item>
<Item>Line3</Item>
<Item>Line4</Item>
</Lines>
</A>
<A>
<B>Node2</B>
<Lines>
<Item>Line1</Item>
<Item>Line2</Item>
<Item>Line3</Item>
<Item>Line4</Item>
</Lines>
</A>
</Demo>

Answer 2

需要将A作为外部查询的节点名，Demo作为其根节点名。

SELECT 'Node' AS [B],
(
SELECT x.Item
FROM (
SELECT 'Line1' AS [Item]
UNION
SELECT 'Line2' AS [Item]
) x
FOR XML PATH(''), ROOT('Lines'), TYPE
)
FOR XML PATH('A'), ROOT('Demo'), TYPE

SQL小提琴