sql-serverhierarchyqxmlquery

SQL Server XML Query Hierarchy not as awaited

There are five or more database tables, that are related to each other like in the following database schema:

enter image description here

Here is the code for creating them:

-- 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

Now I add the following record samples to the table:

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')

Querying this tables with a JOIN FOR XML like

 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.Table2_Id
FOR XML AUTO

will end in this result

<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>

while I'm expecting this

 <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>

So how can I modify the query, perhaps making subqueries to get the expected result, sorting Table 3 at the same level and not beneath table2, and also sorting the childs of table2_1 and table3_1 beneath there parents?

Solution

  • Given your hierarchical example I've fleshed out the example data to include some more subitems...

    INSERT INTO table1 VALUES (1, 'FirstTitle', 'FirstAnno');

INSERT INTO table2 VALUES (1, 1, 'Tab2Title1');
INSERT INTO table2_1 VALUES (1, 1, 'Tab21Sub1.1');
INSERT INTO table2_1 VALUES (2, 1, 'Tab21Sub1.2');
INSERT INTO table2_1 VALUES (3, 1, 'Tab21Sub1.3');

INSERT INTO table2 VALUES (2, 1, 'Tab2Title2');
INSERT INTO table2_1 VALUES (4, 2, 'Tab21Sub2.1');
INSERT INTO table2_1 VALUES (5, 2, 'Tab21Sub2.2');

INSERT INTO table3 VALUES (1, 1, 'Tab3Title1');
INSERT INTO table3_1 VALUES (1, 1, 'Tab31Sub');

INSERT INTO table3 VALUES (2, 1, 'Tab3Title2');

    If you use FOR XML AUTO and correlated subqueries that return FOR XML AUTO, TYPE such as the following:

    SELECT T1.*,
  (
    SELECT T2.*,
      (
        SELECT T21.*
        FROM Table2_1 AS T21
        WHERE T2.Id = T21.Table2_Id
        FOR XML AUTO, TYPE
      )
    FROM Table2 AS T2
    WHERE T1.Id = T2.Table1_Id
    FOR XML AUTO, TYPE
  ),
  (
    SELECT T3.*,
      (
        SELECT T31.*
        FROM Table3_1 AS T31
        WHERE T3.Id = T31.Table3_Id
        FOR XML AUTO, TYPE
      )
    FROM Table3 AS T3
    WHERE T1.Id = T3.Table1_Id
    FOR XML AUTO, TYPE
  )
FROM Table1 AS T1
FOR XML AUTO;

    You can return nested XML data such as the following:

    <T1 Id="1" Title="FirstTitle" Annotation="FirstAnno ">
    <T2 Id="1" Table1_Id="1" Title="Tab2Title1">
        <T21 Id="1" Table2_Id="1" Title="Tab21Sub1.1"/>
        <T21 Id="2" Table2_Id="1" Title="Tab21Sub1.2"/>
        <T21 Id="3" Table2_Id="1" Title="Tab21Sub1.3"/>
    </T2>
    <T2 Id="2" Table1_Id="1" Title="Tab2Title2">
        <T21 Id="4" Table2_Id="2" Title="Tab21Sub2.1"/>
        <T21 Id="5" Table2_Id="2" Title="Tab21Sub2.2"/>
    </T2>
    <T3 Id="1" Table1_Id="1" Title="Tab3Title1">
        <T31 Id="1" Table3_Id="1" Title="Tab31Sub   "/>
    </T3>
    <T3 Id="2" Table1_Id="1" Title="Tab3Title2"/>
</T1>