在 Sql 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 節點）。（因此它適用於 SQL Server 索引視圖。）

僅供參考：不確定是否重要，但我的特定查詢有超過 10 個級別的 xml 樹（從一堆不同的表中提取）。

簡短的回答：你不能。

長答案：

這裡發生了一些事情。

首先，您可能低估了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，級別 16，狀態 1，第 73 行無法在表“T3”上創建索引“IX_T3”。只能在 XML 列“x”上創建 XML 索引。

XML 索引呢？好吧：

您不能在視圖中的 xml 列、具有 xml 列的表值變數或 xml 類型變數上創建 XML 索引，無論是主索引還是輔助索引。

創建 XML 索引 (Transact-SQL)

因此，為了使用視圖具體化 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

你會得到這個錯誤：

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

你會得到一個錯誤：

消息 1937，級別 16，狀態 1，第 98 行無法在視圖“tempdb.dbo.V2”上創建索引，因為它引用了另一個視圖“dbo.V1”。考慮在索引視圖定義中手動擴展引用視圖的定義。

所以不能嵌套這些視圖，也不能使用子查詢。您也不能使用 APPLY 運算符，因此不能使用表值使用者定義函式（也不能有效使用nodesXML 函式）。

但是您可以使用確定性標量值函式。因此，您可以建構一些基本的 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

導致此錯誤：

消息 10133，級別 16，狀態 1，第 181 行無法在視圖“tempdb.dbo.V4”上創建索引，因為視圖引用的函式“dbo.F2”執行使用者或系統數據訪問。

因此，該功能無法獲取所需的資訊。它必須被提供資訊。而且根本沒有辦法（我能想到的）從多行收集數據並將其提供給函式。您可以提供確定性函式 XML 以進行操作，但如果沒有子查詢，則無法創建基本 XML。

所以簡而言之，我認為沒有任何方法可以使用索引視圖來具體化包含來自任何表中多行數據的 XML。

引用自：https://dba.stackexchange.com/questions/98721