このトピックでは、単一レベルの FOR XML クエリと入れ子になった FOR XML クエリを比較します。 入れ子になった FOR XML クエリを使用する利点の 1 つは、クエリ結果に属性中心の XML と要素中心の XML の組み合わせを指定できることです。 この例では、これを示します。
例
次の SELECT クエリでは、 AdventureWorks2012 データベース内の製品カテゴリとサブカテゴリの情報を取得します。 クエリ内に入れ子になった FOR XML は存在しません。
USE AdventureWorks2012;
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>
前のフラグメントでは、カテゴリ 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 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ディレクティブは、外側のクエリによって生成された XML に追加される要素中心の XML を生成するために、内部FOR XMLに追加されます。 既定では、外部クエリは属性中心の XML を生成します。内部クエリでは、結果が xml 型になるように、
TYPEディレクティブが指定されます。TYPEが指定されていない場合、結果はnvarchar(max)型として返され、XML データはエンティティとして返されます。外側のクエリでは、
TYPEディレクティブも指定されます。 したがって、このクエリの結果は xml 型としてクライアントに返されます。
結果の一部を次に示します。
<ProductCategory ProductCategoryID="1" CategoryName="Bike">
<ProductSubCategory>
<ProductSubCategoryID>1</ProductSubCategoryID>
<SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
<ProductSubCategory>
...
<ProductSubCategory>
...
</ProductCategory>
次のクエリは、前のクエリの拡張機能にすぎません。 AdventureWorks2012 データベースの完全な製品階層が表示されます。 次に例を示します。
製品カテゴリ
各カテゴリの製品サブカテゴリ
各サブカテゴリの製品モデル
各モデルの製品
AdventureWorks2012 データベースを理解する際に、次のクエリが役立つ場合があります。
SELECT ProductCategoryID, Name as CategoryName,
(SELECT ProductSubCategoryID, Name 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 クエリでは生成できません。