sql-server - 使用 FOR XML 选择 xml 格式的数据

Question

我正在编写查询以选择一些 xml 格式的列。

示例

我有如下数据

Create Table #Master(Id int, Name varchar(100))

Insert Into #Master
Values(1,'Item1'),(2,'Item2')


Create Table #Sub(SubId int,MasteId int, SubName varchar(100))

Insert Into #Sub
Values(1,1,'SubItem1'),(2,1,'SubItem2')

目前写查询如下

Select *
From #Master as Main
FOR XML AUTO, ROOT ('ItemGroup'), ELEMENTS  XSINIL;

如下所示提取 xml

<ItemGroup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Main>
    <Id>1</Id>
    <Name>Item1</Name>
  </Main>
  <Main>
    <Id>2</Id>
    <Name>Item2</Name>
  </Main>
</ItemGroup>

但我想使用链接#Master 和#Sub 将子项目带入每个主项目的单独注释中，这样它会生成如下所示的内容

<ItemGroup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Main>
    <Id>1</Id>
    <Name>Item1</Name>
    <SubItems>
      <subitem>
        <subid>1</subid>
        <masterid>1</masterid>
        <subname>SubItem1</subname>
      </subitem>
      <subitem>
        <subid>2</subid>
        <masterid>1</masterid>
        <subname>SubItem2</subname>
      </subitem>
    </SubItems>
  </Main>
  <Main>
    <Id>2</Id>
    <Name>Item2</Name>
  </Main>
</ItemGroup>

有什么办法可以实现吗？

Answer 1

SELECT
    *,
    (
        SELECT *
        FROM #Sub AS subitem
        WHERE subitem.MasteId = Main.Id
        FOR XML AUTO, ELEMENTS, TYPE 
    ) AS SubItems
From #Master as Main
FOR XML AUTO, ROOT ('ItemGroup'), ELEMENTS XSINIL;