SQL Server FOR XML AUTO查询层级不符预期，如何修改查询？

问题背景 #

现有5个关联的SQL Server数据库表，执行原JOIN+FOR XML AUTO查询后，生成的XML结构不符合预期，需要修改查询语句实现以下目标：

• Table3与Table2处于同一层级（均作为Table1的直接子节点）
• Table2_1作为Table2的子节点，Table3_1作为Table3的子节点

表结构建表语句 #

-- Table 1
CREATE TABLE [dbo].[Table1](
    [Id] [INT] NOT NULL,
    [Title] [NCHAR](10) NOT NULL,
    [Annotation] [NCHAR](10) NOT NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Table 2 referencing Table 1
CREATE TABLE [dbo].[Table2](
    [Id] [INT] NOT NULL,
    [Table1_Id] [INT] NOT NULL,
    [Title] [NCHAR](10) NOT NULL,
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table2]  WITH CHECK ADD  CONSTRAINT [FK_Table2_Table1] FOREIGN KEY([Table1_Id])
REFERENCES [dbo].[Table1] ([Id])
GO
ALTER TABLE [dbo].[Table2] CHECK CONSTRAINT [FK_Table2_Table1]
GO
-- Table 2_1 referencing Table 2
CREATE TABLE [dbo].[Table2_1](
    [Id] [INT] NOT NULL,
    [Table2_Id] [INT] NOT NULL,
    [Title] [NCHAR](10) NOT NULL,
 CONSTRAINT [PK_Table2_1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table2_1]  WITH CHECK ADD  CONSTRAINT [FK_Table2_1_Table2] FOREIGN KEY([Table2_Id])
REFERENCES [dbo].[Table2] ([Id])
GO

ALTER TABLE [dbo].[Table2_1] CHECK CONSTRAINT [FK_Table2_1_Table2]
GO
-- Table 3 referencing Table 1
CREATE TABLE [dbo].[Table3](
    [Id] [INT] NOT NULL,
    [Table1_Id] [INT] NOT NULL,
    [Title] [NCHAR](10) NOT NULL,
 CONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table3]  WITH CHECK ADD  CONSTRAINT [FK_Table3_Table1] FOREIGN KEY([Table1_Id])
REFERENCES [dbo].[Table1] ([Id])
GO
ALTER TABLE [dbo].[Table3] CHECK CONSTRAINT [FK_Table3_Table1]
GO
-- Table 3_1 referencing Table 3
CREATE TABLE [dbo].[Table3_1](
    [Id] [INT] NOT NULL,
    [Table3_Id] [INT] NOT NULL,
    [Title] [NCHAR](10) NOT NULL,
 CONSTRAINT [PK_Table3_1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table3_1]  WITH CHECK ADD  CONSTRAINT [FK_Table3_1_Table3] FOREIGN KEY([Table3_Id])
REFERENCES [dbo].[Table3] ([Id])
GO
ALTER TABLE [dbo].[Table3_1] CHECK CONSTRAINT [FK_Table3_1_Table3]
GO

测试数据 #

INSERT INTO table1 VALUES (1, 'FirstTitle', 'FirstAnno')
INSERT INTO table2 VALUES (1, 1, 'Tab2Title')
INSERT INTO table3 VALUES (1, 1, 'Tab3Title')
INSERT INTO table2_1 VALUES (1, 1, 'Tab21Sub')
INSERT INTO table3_1 VALUES (1, 1, 'Tab31Sub')

原查询语句 #

SELECT * FROM Table1 AS T1
   JOIN Table2 AS T2 ON T1.Id = T2.Table1_Id
   JOIN Table3 AS T3 ON T1.Id = T3.Table1_Id
   JOIN Table2_1 AS T21 ON T2.Id = T21.Table2_Id
   JOIN Table3_1 AS T31 ON T3.Id = T31.Table3_Id
FOR XML AUTO

原查询生成的错误XML结构 #

<T1 Id="1" Title="FirstTitle" Annotation="FirstAnno ">
  <T2 Id="1" Table1_Id="1" Title="Tab2Title ">
    <T3 Id="1" Table1_Id="1" Title="Tab3Title ">
      <T21 Id="1" Table2_Id="1" Title="Tab21Sub  ">
      <T31 Id="1" Table3_Id="1" Title="Tab31Sub  " />
      </T21>
    </T3>
  </T2>
</T1>

预期XML结构 #

<T1 Id="1" Title="FirstTitle" Annotation="FirstAnno ">
  <T2 Id="1" Table1_Id="1" Title="Tab2Title ">
    <T21 Id="1" Table2_Id="1" Title="Tab21Sub" />
  </T2>
  <T3 Id="1" Table1_Id="1" Title="Tab3Title ">
    <T31 Id="1" Table3_Id="1" Title="Tab31Sub" />
  </T3>
</T1>

解决方案 #

使用子查询+FOR XML AUTO, TYPE的方式，明确指定每个节点的层级关系，避免JOIN顺序导致的嵌套混乱。TYPE关键字用于保留子查询生成的XML结构，而非转为字符串。

修改后的查询语句 #

SELECT 
    T1.Id,
    T1.Title,
    T1.Annotation,
    -- 生成T2节点及其子节点T21
    (SELECT 
         T2.Id,
         T2.Table1_Id,
         T2.Title,
         (SELECT T21.* FROM Table2_1 AS T21 WHERE T21.Table2_Id = T2.Id FOR XML AUTO, TYPE)
     FROM Table2 AS T2 
     WHERE T2.Table1_Id = T1.Id 
     FOR XML AUTO, TYPE) AS T2,
    -- 生成T3节点及其子节点T31
    (SELECT 
         T3.Id,
         T3.Table1_Id,
         T3.Title,
         (SELECT T31.* FROM Table3_1 AS T31 WHERE T31.Table3_Id = T3.Id FOR XML AUTO, TYPE)
     FROM Table3 AS T3 
     WHERE T3.Table1_Id = T1.Id 
     FOR XML AUTO, TYPE) AS T3
FROM Table1 AS T1
FOR XML AUTO

原理说明 #

原查询的JOIN方式会让SQL Server按照表的连接顺序生成嵌套结构，把后续JOIN的表作为前一个表的子节点。而通过子查询，我们可以分别将Table2（带Table2_1）和Table3（带Table3_1）作为Table1的独立子节点，精准控制XML的层级结构。

内容的提问来源于stack exchange，提问作者Helmut