FOR XML 查詢與巢狀 FOR XML 查詢的比較

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Microsoft Fabric 中的 SQL 資料庫

本文會將單一層 FOR XML 查詢與巢狀 FOR XML 查詢做比較。 使用巢狀 FOR XML 查詢的其中一個好處，就是可以針對查詢結果指定屬性中心及元素中心 XML 的組合。 此範例說明了這項優點。

本文中的程式碼範例使用 AdventureWorks2025 或 AdventureWorksDW2025 範例資料庫，您可以從 Azure Data SQL Samples Repository GitHub 存放庫下載這些範例資料庫。

範例

下列 SELECT 查詢會擷取 AdventureWorks2025 資料庫中的產品類別目錄及子類別目錄資訊。 查詢中沒有巢狀的 FOR 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>

以下查詢只是前述查詢的延伸模組。 它會顯示 AdventureWorks2025 資料庫的完整產品階層。 這包括：

  • 產品類別目錄
  • 各類別中的產品子類別
  • 各子類別中的產品型號
  • 每個型號中的產品

以下查詢應該有助於了解 AdventureWorks2025 資料庫：

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

如果從產生產品子類別的巢狀 FOR XML 查詢中移除 ELEMENTS 指示詞，整個結果都會以屬性為中心。 接著，您可以在不使用巢狀結構的情況下撰寫這個查詢。 加入 ELEMENTS 會使 XML 一部份為屬性中心，一部份為元素中心。 此結果無法由單一層 FOR XML 查詢產生。

相關內容

  • Last updated on