共用方式為

與巢狀 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 查詢產生。

相關內容