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>