Запрос SQL Server FOR XML не возвращает результаты в ожидаемой иерархии

Question

У меня есть система из четырех связанных таблиц, A, B, C и D, и мне нужно получить XML-представление этих данных. Таблицы выглядят так:

A ← B´
↑
B               Both table B and B´ have table A as a parent.
↑               Table C has table B as a parent.
C

За исключением таблицы A, у которой нет родителя, каждая из таблиц имеет 3 поля:

ID     int  -- integer ID of field
XStuff varchar(20) -- Replace 'X' with table name letter, such as AStuff, BStuff, etc.
Y_ID   int  -- Foreign key to parent table. Replace 'Y' with parent table name letter.

Данные таблицы:

tblA                 tblB
===============      =============
    ID | 1               ID | 1
AStuff | 'This'      BStuff | 'is'
                       A_ID | 1


tblC                 tblBPrime
============         ====================
    ID | 1                    ID | 1
CStuff | 'a'         BPrimeStuff | 'test'
  B_ID | 1                  A_ID | 1

Запрос:

SELECT tblA.AStuff
      ,tblB.BStuff
      ,tblC.CStuff
      ,tblBPrime.BPrimeStuff
  FROM tblA 
    JOIN tblB ON tblB.A_ID = tblA.ID
    JOIN tblC ON tblC.B_ID = tblB.ID
    JOIN tblBPrime ON tblBPrime.A_ID = tblA.ID
  FOR XML AUTO, TYPE, ELEMENTS

Что приводит к следующему выводу:

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

Это сбивает с толку, потому что tblBPrime подчиняется tblA, а не tblC, и я ожидаю, что результат будет примерно таким:

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

Я хотел бы знать, как запрос должен быть изменен, чтобы получить ожидаемые результаты. Кстати, я пометил этот SQL Server 2008, потому что самый старый блок SQL Server, который должен будет выполнять этот код, находится на этом уровне, но он также должен работать на версиях SQL Server 2012 и 2014.

РЕДАКТИРОВАТЬ:

В соответствии с запросом, здесь приведены сценарии для создания таблиц и их взаимосвязей и их заполнения:

Создать таблицы и отношения:

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

Answer 1

Похоже, одним из решений является использование FOR XML PATH вместо FOR XML AUTO, Я нашел отличную информацию об использовании FOR XML заявления в ряде статей, написанных Джейкобом Себастьяном на SQLServerCentral.com: http://www.sqlservercentral.com/articles/XML/3022/.

Следующий запрос дает ожидаемые результаты, но зависит от определения иерархии каждого элемента непосредственно в запросе. Я надеюсь, что есть лучший способ:

SELECT tblA.AStuff
      ,tblB.BStuff as [tblB/BStuff]
      ,tblC.CStuff as [tblB/tblC/Cstuff]
      ,tblBPrime.BPrimeStuff as [tblBPrime/BPrimeStuff]
  FROM tblA 
    JOIN tblB ON tblB.A_ID = tblA.ID
    JOIN tblC ON tblC.B_ID = tblB.ID
    JOIN tblBPrime ON tblBPrime.A_ID = tblA.ID
  FOR XML PATH ('tblA'), ELEMENTS;

... выход...

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