有五个或更多数据库表,它们彼此相关,如以下数据库模式所示:
下面是创建它们的代码:
-- 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')
使用JOIN FOR XML查询此表,如
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
将以这个结果结束
<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>
当我期待这个的时候
<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>
那么,我该如何修改查询呢?也许可以进行子查询以获得预期的结果,将表3排序在同一级别而不是表2之下,并将表2_1和表3_1的查尔兹排序在它们的父表之下?
1条答案
按热度按时间hmae6n7t1#
对于您的层次结构示例,我充实了示例数据以包括更多的子项...
如果使用
FOR XML AUTO
和返回FOR XML AUTO, TYPE
的相关子查询,如下所示:可以返回如下所示的嵌套XML数据: