SQL Server FOR XML查询未生成预期层级结构，如何修改？

你遇到的问题根源在于FOR XML AUTO是按照查询中表的连接顺序和关联关系自动生成XML层级的。你的原始查询最后连接了tblBPrime，而它前面的关联表是tblC，所以SQL Server默认把它嵌套到了tblC节点下，这和你预期的结构不符。

要让tblBPrime直接作为tblA的子节点，我们需要用嵌套FOR XML查询来明确指定层级关系，这种写法完全兼容SQL Server 2008、2012和2014版本。

正确的查询写法 #

SELECT 
    tblA.AStuff,
    -- 嵌套查询获取tblB及其子表tblC
    (
        SELECT 
            tblB.BStuff,
            -- 嵌套查询获取tblC
            (
                SELECT tblC.CStuff
                FROM tblC
                WHERE tblC.B_ID = tblB.ID
                FOR XML AUTO, TYPE, ELEMENTS
            )
        FROM tblB
        WHERE tblB.A_ID = tblA.ID
        FOR XML AUTO, TYPE, ELEMENTS
    ),
    -- 嵌套查询获取tblBPrime
    (
        SELECT tblBPrime.BPrimeStuff
        FROM tblBPrime
        WHERE tblBPrime.A_ID = tblA.ID
        FOR XML AUTO, TYPE, ELEMENTS
    )
FROM tblA
FOR XML AUTO, TYPE, ELEMENTS

为什么这个查询能工作？ #

• 主查询针对tblA，通过两个独立的子查询分别获取属于tblA的tblB（包含关联的tblC）和tblBPrime数据，确保它们都是tblA的直接子节点。
• 每个子查询都用FOR XML AUTO, TYPE, ELEMENTS生成对应的XML片段，TYPE参数让结果以XML类型返回，能正确嵌套到主查询的XML结构中。
• 子查询里的WHERE条件保证了数据关联的正确性，不会出现数据错乱。

执行结果 #

运行这个查询后，你会得到完全符合预期的XML结构：

<tblA>
  <AStuff>This</AStuff>
  <tblB>
    <BStuff>is</BStuff>
    <tblC>
      <CStuff>a</CStuff>
    </tblC>
  </tblB>
  <tblBPrime>
    <BPrimeStuff>test</BPrimeStuff>
  </tblBPrime>
</tblA>

额外说明 #

如果你的数据中有多个tblB或tblBPrime记录，这个写法同样适用，子查询会自动生成对应的多个节点。这种嵌套写法比单纯依赖FOR XML AUTO更灵活，能精准控制XML的层级结构，避免因连接顺序导致的意外嵌套。

建表及插入数据脚本（供参考） #

创建表及关系

CREATE TABLE [dbo].[tblA](
 [ID] [int] NOT NULL,
 [AStuff] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tblA] PRIMARY KEY CLUSTERED(
 [ID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblB](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [BStuff] [varchar](50) NOT NULL,
 [A_ID] [int] NOT NULL,
 CONSTRAINT [PK_tblB] PRIMARY KEY CLUSTERED(
 [ID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblB] WITH CHECK ADD CONSTRAINT [FK_tblB_tblA] FOREIGN KEY([A_ID])
REFERENCES [dbo].[tblA] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblB] CHECK CONSTRAINT [FK_tblB_tblA]
GO

CREATE TABLE [dbo].[tblBPrime](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [BPrimeStuff] [varchar](50) NOT NULL,
 [A_ID] [int] NOT NULL,
 CONSTRAINT [PK_tblBPrime] PRIMARY KEY CLUSTERED(
 [ID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblBPrime] WITH CHECK ADD CONSTRAINT [FK_tblBPrime_tblA] FOREIGN KEY([A_ID])
REFERENCES [dbo].[tblA] ([ID])
GO

ALTER TABLE [dbo].[tblBPrime] CHECK CONSTRAINT [FK_tblBPrime_tblA]
GO

CREATE TABLE [dbo].[tblC](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [CStuff] [varchar](50) NOT NULL,
 [B_ID] [int] NOT NULL,
 CONSTRAINT [PK_tblC] PRIMARY KEY CLUSTERED(
 [ID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblC] WITH CHECK ADD CONSTRAINT [FK_tblC_tblB] FOREIGN KEY([B_ID])
REFERENCES [dbo].[tblB] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblC] CHECK CONSTRAINT [FK_tblC_tblB]
GO

插入数据

SET XACT_ABORT ON;
DECLARE @A_ID int = 1,@B_ID int,@C_ID int
BEGIN TRAN
INSERT INTO [dbo].[tblA]([ID],[AStuff]) VALUES (@A_ID,'This');
INSERT INTO [dbo].[tblB]([BStuff],[A_ID]) VALUES('is',@A_ID);
SET @B_ID = SCOPE_IDENTITY();
INSERT INTO [dbo].[tblC]([CStuff],[B_ID]) VALUES('a',@B_ID);
SET @C_ID = SCOPE_IDENTITY();
INSERT INTO [dbo].[tblBPrime]([BPrimeStuff],[A_ID]) VALUES('test',@A_ID);
COMMIT TRAN

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