與巢狀 FOR XML 查詢比較的 FOR XML 查詢
適用於:SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體
本文會將單一層 FOR XML
查詢與巢狀 FOR XML
查詢做比較。 使用巢狀 FOR XML
查詢的其中一個好處,就是可以針對查詢結果指定屬性中心及元素中心 XML 的組合。 以下範例將提供示範。
本文 Transact-SQL 程式碼範例使用 AdventureWorks2022
或 AdventureWorksDW2022
範例資料庫,從 Microsoft SQL Server Samples 和 Community Projects (Microsoft SQL Server 範例和社群專案) 首頁即可下載。
範例
下列 SELECT
查詢會擷取 AdventureWorks2022
資料庫中的產品類別目錄及子類別目錄資訊。 此查詢中沒有巢狀的 FFOR XML
。
USE AdventureWorks2022;
GO
SELECT ProductCategory.ProductCategoryID,
ProductCategory.Name AS CategoryName,
ProductSubCategory.ProductSubCategoryID,
ProductSubCategory.Name
FROM Production.ProductCategory,
Production.ProductSubCategory
WHERE ProductCategory.ProductCategoryID = ProductSubCategory.ProductCategoryID
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE;
GO
部分結果如下:
<ProductCategory ProductCategoryID="1" CategoryName="Bike">
<ProductSubCategory ProductSubCategoryID="1" Name="Mountain Bike"/>
<ProductSubCategory ProductSubCategoryID="2" Name="Road Bike"/>
<ProductSubCategory ProductSubCategoryID="3" Name="Touring Bike"/>
</ProductCategory>
...
如果在查詢中指定 ELEMENTS
指示詞,會收到元素中心的結果,如以下結果片段所示:
<ProductCategory>
<ProductCategoryID>1</ProductCategoryID>
<CategoryName>Bike</CategoryName>
<ProductSubCategory>
<ProductSubCategoryID>1</ProductSubCategoryID>
<Name>Mountain Bike</Name>
</ProductSubCategory>
<ProductSubCategory>
...
</ProductSubCategory>
</ProductCategory>
接下來,假設您想要產生 XML 階層,結合屬性中心及元素中心的 XML,如以下片段所示:
<ProductCategory ProductCategoryID="1" CategoryName="Bike">
<ProductSubCategory>
<ProductSubCategoryID>1</ProductSubCategoryID>
<SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
<ProductSubCategory>
...
<ProductSubCategory>
...
</ProductCategory>
在前述片段中,類別目錄識別碼及類別目錄名稱等產品類別目錄資訊為屬性。 但是,子類別目錄資訊是元素中心的。 若要建構 <ProductCategory>
元素,您可以撰寫 FOR XML
查詢,如下所示:
SELECT ProductCategoryID,
Name AS CategoryName
FROM Production.ProductCategory ProdCat
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE;
結果如下︰
< ProdCat ProductCategoryID="1" CategoryName="Bikes" />
< ProdCat ProductCategoryID="2" CategoryName="Components" />
< ProdCat ProductCategoryID="3" CategoryName="Clothing" />
< ProdCat ProductCategoryID="4" CategoryName="Accessories" />
若要以希望的 XML 建構巢狀的 <ProductSubCategory>
元素,您可以接著加入巢狀 FOR XML
查詢,如下程式碼範例所示:
SELECT ProductCategoryID,
Name AS CategoryName,
(
SELECT ProductSubCategoryID, Name AS SubCategoryName
FROM Production.ProductSubCategory
WHERE ProductSubCategory.ProductCategoryID = ProductCategory.ProductCategoryID
FOR XML AUTO, TYPE, ELEMENTS
)
FROM Production.ProductCategory
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE;
下列為上一個查詢的注意事項:
內部
FOR XML
查詢會擷取產品子類別目錄資訊。 在內部ELEMENTS
中加入FOR XML
指示詞,以產生元素中心的 XML,此 XML 已加入至外部查詢產生的 XML 中。 根據預設,外部查詢會產生屬性中西的 XML。在內部查詢中指定
TYPE
指示詞,好讓結果屬於 xml 類型。 如果未指定TYPE
,則結果會以 nvarchar(max) 類型傳回,而 XML 資料會以實體傳回。外部查詢也可以指定
TYPE
指示詞。 因此,此查詢的結果會以 xml 類型傳回給用戶端。
部分結果如下:
<ProductCategory ProductCategoryID="1" CategoryName="Bike">
<ProductSubCategory>
<ProductSubCategoryID>1</ProductSubCategoryID>
<SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
<ProductSubCategory>
...
<ProductSubCategory>
...
</ProductCategory>
以下查詢只是前述查詢的延伸模組。 它會顯示 AdventureWorks2022
資料庫的完整產品階層。 這包括:
- 產品類別目錄
- 每個_類別目錄中的產品子類別目錄
- 每個子類別目錄中的產品型號
- 每個型號中的產品
以下查詢應該有助於了解 AdventureWorks2022
資料庫:
SELECT ProductCategoryID,
Name AS CategoryName,
(
SELECT ProductSubCategoryID,
Name AS SubCategoryName,
(
SELECT ProductModel.ProductModelID,
ProductModel.Name AS ModelName,
(
SELECT ProductID, Name AS ProductName, Color
FROM Production.Product
WHERE Product.ProductModelID = ProductModel.ProductModelID
FOR XML AUTO, TYPE
)
FROM (
SELECT DISTINCT ProductModel.ProductModelID, ProductModel.Name
FROM Production.ProductModel, Production.Product
WHERE ProductModel.ProductModelID = Product.ProductModelID
AND Product.ProductSubCategoryID = ProductSubCategory.ProductSubCategoryID
) ProductModel
FOR XML AUTO, TYPE
)
FROM Production.ProductSubCategory
WHERE ProductSubCategory.ProductCategoryID = ProductCategory.ProductCategoryID
FOR XML AUTO, TYPE, ELEMENTS
)
FROM Production.ProductCategory
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE;
部分結果如下:
<Production.ProductCategory ProductCategoryID="1" CategoryName="Bikes">
<Production.ProductSubCategory>
<ProductSubCategoryID>1</ProductSubCategoryID>
<SubCategoryName>Mountain Bikes</SubCategoryName>
<ProductModel ProductModelID="19" ModelName="Mountain-100">
<Production.Product ProductID="771"
ProductName="Mountain-100 Silver, 38" Color="Silver" />
<Production.Product ProductID="772"
ProductName="Mountain-100 Silver, 42" Color="Silver" />
<Production.Product ProductID="773"
ProductName="Mountain-100 Silver, 44" Color="Silver" />
...
</ProductModel>
...
如果從產生產品子類別目錄的巢狀 ELEMENTS
查詢移除 FOR XML
指示詞,整個結果將為屬性中心。 然後您可以無套疊的方式撰寫此查詢 加入 ELEMENTS
會使 XML 一部份為屬性中心,一部份為元素中心。 此結果無法由單一層 FOR XML
查詢產生。