在Server索引(物化)视图中创建嵌套XML
此页面https://msdn.microsoft.com/en-us/library/ms188276.aspx表示，如果需要嵌套XML (即XML )，则需要设置如下查询：
SELECT Col1,
Col2,
( SELECT Col3, Col4
FROM T2
WHERE T2.Col = T1.Col
...
FOR XML AUTO, TYPE )
FROM T1
WHERE ...
FOR XML AUTO, TYPE;(使用子查询)
这完全不符合索引视图的要求(不允许子查询)。
不管怎么说，这两个特性是有联系的吗？(有一个有XML树的索引视图吗？)
我的问题是:如何在不使用任何子查询或联合的情况下，从许多表创建XML树(即嵌套XML节点)。(因此它将使用Server索引视图。)
FYI:不确定是否重要，但我的特定查询有超过10个级别的xml树(从一堆不同的表中提取)。
回答 1
Database Administration用户
发布于 2017-05-19 20:13:38
简短的回答:你不能。
较长的答覆：
这里发生了一些事情。
首先，您可能低估了XML AUTO的威力。它将提供一定数量的嵌套“自动”。实际上，不需要嵌套XML生成就可以处理所提供的示例。
让我们制作一些测试表和数据：
USE tempdb;
DROP TABLE IF EXISTS T1;
DROP TABLE IF EXISTS T2;
CREATE TABLE T1
(
Col1 int,
Col2 varchar(50),
[Col] char(1)
);
CREATE TABLE T2
(
[Col] char(1),
Col3 varchar(50),
Col4 varchar(50)
);
INSERT INTO T1 (Col1, Col2, [Col]) VALUES
(1, 'Test1', 'A'), (2, 'Test2', 'B');
INSERT INTO T2 ([Col], Col3, Col4) VALUES
('A','1x','1y'), ('A','2x','2y'), ('B','3x','3y'), ('B','4x','4y'), ('B','5x','5y');您的查询：
SELECT
Col1,
Col2,
(
SELECT Col3, Col4
FROM T2
WHERE T2.Col = T1.Col
FOR XML AUTO, TYPE
)
FROM T1
FOR XML AUTO, TYPE;产生的结果与没有嵌套的结果相同：
SELECT
Col1,
Col2,
Col3,
Col4
FROM T1
JOIN T2
ON T2.Col = T1.Col
FOR XML AUTO, TYPE;它们都产生：
<T1 Col1="1" Col2="Test1">
<T2 Col3="1x" Col4="1y" />
<T2 Col3="2x" Col4="2y" />
</T1>
<T1 Col1="2" Col2="Test2">
<T2 Col3="3x" Col4="3y" />
<T2 Col3="4x" Col4="4y" />
<T2 Col3="5x" Col4="5y" />
</T1>(如果您有更复杂的或自定义的嵌套需求，则会发生故障。)
不管这个特定的例子如何，我认为没有一种有效的方法可以将(动态) XML放到索引视图中。
首先，“索引视图”实际上是一个具有唯一聚集索引的视图。不能在XML列上创建“正常”索引.
假设你试着：
CREATE TABLE T3
(
x xml
);
CREATE UNIQUE CLUSTERED INDEX IX_T3 ON T3 (x);
GO您将得到一个错误：
1977年Msg，第16级，状态1，第73行无法在表'T3‘上创建索引'IX_T3’。只能在XML列'x‘上创建XML索引。
XML索引呢？嗯：
不能在视图中的XML列、带有xml列的表值变量或xml类型变量上创建xml索引(主索引或辅助索引)。
因此，为了使用视图实现XML，它不能是唯一的列。您需要一个“普通”列来创建唯一的聚集索引。获取普通列的一种方法是使用子查询创建XML。但正如你所指出的，如果你试着这样做：
CREATE VIEW V1
(
n,
x
)
WITH SCHEMABINDING
AS
SELECT
1,
(
SELECT 'Test' AS 'Col'
FOR XML PATH, TYPE
)
GO
CREATE UNIQUE CLUSTERED INDEX IX_V1 ON V1 (n);
GO您将得到以下错误：
Msg 10127、级别16、状态1、行83不能在视图"tempdb.dbo.V1“上创建索引，因为它包含一个或多个子查询。考虑将视图更改为只使用联接而不是子查询。或者，考虑不对此视图进行索引。
您可能认为可以通过创建没有子查询的外部视图来欺骗，调用原始视图。但如果你试着：
CREATE VIEW V2
(
n,
x
)
WITH SCHEMABINDING
AS
SELECT
n,
x
FROM dbo.V1;
GO
CREATE UNIQUE CLUSTERED INDEX IX_V2 ON V2(n);
GO您将得到一个错误：
MSG1937、级别16、状态1、第98行不能在视图'tempdb.dbo.V2‘上创建索引，因为它引用了另一个视图'dbo.V1’。考虑在索引视图定义中手动扩展引用视图的定义。
因此，您不能嵌套这些视图，也不能使用子查询。您也不能使用应用运算符，因此您不能使用表值用户定义函数(也不能有效地使用nodes XML函数)。
但是你可以使用确定性标量值函数。因此，您可以构造一些基本的XML。例如：
CREATE FUNCTION F1
(
@Col1 int,
@Col2 varchar(50)
)
RETURNS xml
WITH SCHEMABINDING
AS
BEGIN
RETURN
(
SELECT
@Col1,
@Col2
FOR XML PATH(''), TYPE
)
END
GO
CREATE VIEW V3
(
n,
x
)
WITH SCHEMABINDING
AS
SELECT
Col1,
dbo.F1(T1.Col1, T1.Col2)
FROM dbo.T1 AS T1;
GO
CREATE UNIQUE CLUSTERED INDEX IX_V3 ON V3(n);
GO虽然您可以在函数中创建复杂的、多层次的XML，但是函数不能引用任何表(否则它不会是确定性的)。例如，这是：
CREATE FUNCTION F2
(
@Col1 int,
@Col2 varchar(50),
@Col char(1)
)
RETURNS xml
WITH SCHEMABINDING
AS
BEGIN
RETURN
(
SELECT
@Col1 AS Col1,
@Col2 AS Col2,
(
SELECT Col3, Col4
FROM dbo.T2 AS T2
WHERE T2.Col = @Col
FOR XML AUTO, TYPE
)
FOR XML PATH, TYPE
)
END
GO
CREATE VIEW V4
(
n,
x
)
WITH SCHEMABINDING
AS
SELECT
Col1,
dbo.F2(T1.Col1, T1.Col2, T1.Col)
FROM dbo.T1 AS T1;
GO
CREATE UNIQUE CLUSTERED INDEX IX_V4 ON V4(n);
GO导致此错误：
Msg 10133、级别16、状态1、第181行不能在视图"tempdb.dbo.V4“上创建索引，因为视图引用的函数"dbo.F2”执行用户或系统数据访问。
因此，该函数无法获取所需的信息。它必须得到信息。而且，我根本没有办法(我可以想到)从多个行收集数据，并将其提供给函数。可以提供要操作的确定性函数XML，但如果没有子查询，则无法创建基本XML。
因此，简而言之，我不认为有任何方法使用索引视图来物化XML，因为XML包含来自任何表中多行的数据。
https://dba.stackexchange.com/questions/98721
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号