次の方法で共有

FOR XML クエリと入れ子になった FOR XML クエリの比較

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric の SQL データベース

この記事では、単一レベルの FOR XML クエリと入れ子になった FOR XML クエリを比較します。 入れ子の FOR XML クエリを使用すると、たとえば、属性中心の XML と要素中心の XML の組み合わせをクエリの結果に指定できるなどの利点があります。 次の例はこの利点を示しています。

この記事のコード サンプルでは、AdventureWorks2025 または AdventureWorksDW2025 サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクト ホーム ページからダウンロードできます。

例

次の 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 と要素中心の XML の組み合わせだとします。

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
  <ProductSubCategory>
     ...
  <ProductSubCategory>
     ...
</ProductCategory>

上のフラグメントに示した結果で、カテゴリ ID やカテゴリ名などの製品カテゴリの情報は属性です。 ただし、サブカテゴリの情報は要素中心です。 <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 クエリは、製品サブカテゴリの情報を取得しています。 要素中心の XML を生成するために、内側の ELEMENTS に FOR 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>
     ...

製品サブカテゴリを生成している入れ子構造の ELEMENTS クエリから FOR XML ディレクティブを削除すると、結果全体が属性中心になります。 このクエリは入れ子にしないでも記述できます。 ELEMENTS を追加することで、結果として一部が属性中心で、一部が要素中心の XML が得られます。 この結果は単一レベルの FOR XML クエリでは生成できません。

関連するコンテンツ

  • Last updated on