入れ子になった FOR XML クエリ

[アーティクル]
12/16/2008

SQL Server 2000 では、SELECT クエリの最上位レベルにしか FOR XML 句を指定できません。結果の XML は、主にクライアントに返されてから追加の処理が行われます。SQL Server 2005 では、xml データ型と FOR XML クエリの TYPE ディレクティブが導入されたので、FOR XML クエリによって返された XML にサーバー側でさらに処理を加えることができます。

FOR XML クエリの結果を xml 型の変数に代入できます。また、XQuery を使用して結果にクエリを実行し、その結果を xml 型の変数に代入してからさらに処理を加えることができます。
```
DECLARE @x xml
SET @x=(SELECT ProductModelID, Name
        FROM Production.ProductModel
        WHERE ProductModelID=122 or ProductModelID=119
        FOR XML RAW, TYPE)
SELECT @x
-- Result
--<row ProductModelID="122" Name="All-Purpose Bike Stand" />
--<row ProductModelID="119" Name="Bike Wash" />
```
いずれかの xml データ型メソッドを使用して、変数 @x に返された XML にさらに処理を加えることができます。たとえば、value() メソッド (xml データ型) を使用して、ProductModelID 属性の値を取得できます。
```
DECLARE @i int
SET @i = (SELECT @x.value('/row[1]/@ProductModelID[1]', 'int'))
SELECT @i
```
次の例では、FOR XML 句に TYPE ディレクティブが指定されているので、FOR XML クエリの結果が xml 型で返されます。
```
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=119 or ProductModelID=122
FOR XML RAW, TYPE,ROOT('myRoot')
```
次に結果を示します。
```
<myRoot>
  <row ProductModelID="122" Name="All-Purpose Bike Stand" />
  <row ProductModelID="119" Name="Bike Wash" />
</myRoot>
```
結果は xml 型なので、次のクエリのように、この XML に対していずれかの xml データ型メソッドを直接指定することができます。クエリでは、query() メソッド (xml データ型) が使用され、<myRoot> 要素の最初の <row> 子要素が取得されます。
```
SELECT  (SELECT ProductModelID, Name
         FROM Production.ProductModel
         WHERE ProductModelID=119 or ProductModelID=122
         FOR XML RAW, TYPE,ROOT('myRoot')).query('/myRoot[1]/row[1]')
```
結果を次に示します。
```
<row ProductModelID="122" Name="All-Purpose Bike Stand" />
```
また、入れ子構造の FOR XML クエリを記述して、内側のクエリの結果を xml 型で外側のクエリに返すこともできます。次に例を示します。
```
SELECT Col1, 
       Col2, 
       ( SELECT Col3, Col4 
        FROM  T2
        WHERE T2.Col = T1.Col
        ...
        FOR XML AUTO, TYPE )
FROM T1
WHERE ...
FOR XML AUTO, TYPE
```
上のクエリに関して、次の点に注意してください。
- 内側の FOR XML クエリで生成された XML は、外側の FOR XML で生成された XML に追加されます。
- 内側のクエリでは、TYPE ディレクティブが指定されています。このため、内側のクエリから返される XML データは、xml 型になります。TYPE ディレクティブを指定しなかった場合、内側の FOR XML クエリの結果は nvarchar(max) 型として返され、XML データはエンティティ化されます。
入れ子構造の FOR XML クエリを使用すると、結果の XML データの構造の定義を制御しやすくなります。
- SQL Server 2000 では、既定で RAW および AUTO モードのクエリで属性中心の XML が生成されます。次に例を示します。
```
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW
```
  属性中心の結果を次に示します。
```
<row ProductModelID="122" Name="All-Purpose Bike Stand" />
<row ProductModelID="119" Name="Bike Wash" />
```
  ELEMENTS ディレクティブを指定することで、すべての XML を要素中心として取得できます。次に例を示します。
```
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW, ELEMENTS 
```
  要素中心の結果を次に示します。
```
<row>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</row>
<row>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</row>
```
  SQL Server 2005 では、入れ子構造の FOR XML クエリを使用すると、一部が属性中心で、一部が要素中心の XML を作成できます。
- SQL Server 2000 では、EXPLICIT モードを使用してクエリを作成する方法でのみ兄弟を作成できます。ただし、この方法は複雑になる場合があります。SQL Server 2005 では、入れ子構造で AUTO モードの FOR XML クエリを指定することで、兄弟を含む XML 階層を生成できます。
使用するモードに関係なく、入れ子構造の FOR XML クエリを使用すると、結果の XML の構造の定義を制御しやすくなります。入れ子構造の FOR XML クエリは、EXPLICIT モードのクエリの代わりに使用できます。

例

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

次の SELECT クエリでは、AdventureWorks データベースの製品カテゴリとサブカテゴリの情報を取得します。このクエリには入れ子になった FOR XML はありません。

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 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 が生成するために、内側の FOR XML に ELEMENTS ディレクティブを追加しています。ここで生成した XML は外側のクエリで生成される 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>

次のクエリは単に上記のクエリを拡張したものです。これによって、AdventureWorks データベースに格納されている完全な製品階層が示されます。これには次の情報が含まれます。

製品カテゴリ
各カテゴリの製品サブカテゴリ
各サブカテゴリの製品モデル
各モデルの製品

AdventureWorks データベースを理解するために、次のクエリが役に立ちます。

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 クエリでは生成できません。

B. 入れ子構造で AUTO モードのクエリを使用した兄弟の生成

次の例では、入れ子になった AUTO モードのクエリを使用して兄弟を作成する方法を示します。他の方法では、EXPLICIT モードを使用する以外に、このような XML を生成する方法はありません。ただし、この方法は複雑になる場合があります。

このクエリでは、販売注文情報を提供する XML が構築されます。これには次の情報が含まれます。

販売注文ヘッダー情報、SalesOrderID、SalesPersonID、および OrderDate。この情報は AdventureWorks の SalesOrderHeader テーブルに格納されています。
販売注文明細情報。注文を受けた 1 つ以上の製品、単価、および受注数が含まれます。この情報は SalesOrderDetail テーブルに格納されています。
販売員情報。これは注文を受けた販売員です。SalesPersonID は、SalesPerson テーブルで提供されています。このクエリでは、販売員の名前を検索するために、このテーブルと Employee テーブルを結合する必要があります。

次に示す 2 つの異なる SELECT クエリは、やや形式の異なる XML を生成します。

最初のクエリで生成される XML では、<SalesPerson> と <SalesOrderHeader> は <SalesOrder> の子の兄弟として示されます。

SELECT 
      (SELECT top 2 SalesOrderID, SalesPersonID, CustomerID,
         (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
           from Sales.SalesOrderDetail
            WHERE  SalesOrderDetail.SalesOrderID = 
                   SalesOrderHeader.SalesOrderID
            FOR XML AUTO, TYPE)
        FROM  Sales.SalesOrderHeader
        WHERE SalesOrderHeader.SalesOrderID = SalesOrder.SalesOrderID
        for xml auto, type),
        (SELECT * 
         FROM  (SELECT SalesPersonID, EmployeeID
              FROM Sales.SalesPerson, HumanResources.Employee
              WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As 
                     SalesPerson
         WHERE  SalesPerson.SalesPersonID = SalesOrder.SalesPersonID
       FOR XML AUTO, TYPE)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
      FROM Sales.SalesOrderHeader, Sales.SalesPerson
      WHERE SalesOrderHeader.SalesPersonID = SalesPerson.SalesPersonID
     ) as SalesOrder
ORDER BY SalesOrder.SalesOrderID
FOR XML AUTO, TYPE

上のクエリで、最も外側の SELECT ステートメントについて説明します。

FROM 句で指定された行セット SalesOrder に対してクエリを実行しています。結果は、1 つ以上の <SalesOrder> 要素を含む XML になります。
AUTO モードと TYPE ディレクティブが指定されています。AUTO モードはクエリの結果を XML に変換し、TYPE ディレクティブは結果を xml 型で返します。
2 つの SELECT ステートメントが含まれており、コンマで区切られ、入れ子構造になっています。入れ子の最初の SELECT ステートメントで、販売注文情報、ヘッダー、および明細を取得し、入れ子の 2 番目の SELECT ステートメントで販売員情報を取得しています。
- SalesOrderID、SalesPersonID、および CustomerID 自体を取得する SELECT ステートメントには、販売注文明細情報を返す (AUTO モードと TYPE ディレクティブを指定した) 別の SELECT ... FOR XML ステートメントが入れ子として含まれています。

販売員情報を取得する SELECT ステートメントは、FROM 句で作成された行セット SalesPerson に対して照会を行います。FOR XML クエリが機能するためには、FROM 句で生成される匿名の行セットに名前を付ける必要があります。ここで指定されている名前は SalesPerson です。

次に、結果の一部を示します。

<SalesOrder>
  <Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
  </Sales.SalesOrderHeader>
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</SalesOrder>
...

次のクエリでは、同じ販売注文情報が生成されます。ただし、結果の XML では、<SalesPerson> は <SalesOrderDetail> の兄弟として示されます。

<SalesOrder>
    <SalesOrderHeader ...>
          <SalesOrderDetail .../>
          <SalesOrderDetail .../>
          ...
          <SalesPerson .../>
    </SalesOrderHeader>
    
</SalesOrder>
<SalesOrder>
  ...
</SalesOrder>

クエリは次のとおりです。

SELECT SalesOrderID, SalesPersonID, CustomerID,
             (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
              from Sales.SalesOrderDetail
              WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
              FOR XML AUTO, TYPE),
              (SELECT * 
               FROM  (SELECT SalesPersonID, EmployeeID
                    FROM Sales.SalesPerson, HumanResources.Employee
                    WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
               WHERE  SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID
         FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID=43659 or SalesOrderID=43660
FOR XML AUTO, TYPE

次に結果を示します。

<Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>
<Sales.SalesOrderHeader SalesOrderID="43660" SalesPersonID="279" CustomerID="117">
  <Sales.SalesOrderDetail SalesOrderID="43660" ProductID="762" OrderQty="1" UnitPrice="419.4589" />
  <Sales.SalesOrderDetail SalesOrderID="43660" ProductID="758" OrderQty="1" UnitPrice="874.7940" />
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>

TYPE ディレクティブによってクエリの結果が xml 型で返されるので、各種の xml データ型メソッドを使用して結果の XML にクエリを実行できます。詳細については、「xml データ型のメソッド」を参照してください。次のクエリでは、次の点に注意してください。

上記のクエリが FROM 句に追加されています。クエリの結果はテーブルで返されます。追加された XmlCol 別名に注意してください。

SELECT 句では、FROM 句で返される XmlCol に対して XQuery を指定しています。XQuery の指定では、xml データ型の query() メソッドを使用しています。詳細については、「query() メソッド (xml データ型)」を参照してください。

SELECT XmlCol.query('<Root> { /* } </Root>')
FROM (
SELECT SalesOrderID, SalesPersonID, CustomerID,
             (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
              from Sales.SalesOrderDetail
              WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
              FOR XML AUTO, TYPE),
              (SELECT * 
               FROM  (SELECT SalesPersonID, EmployeeID
                    FROM Sales.SalesPerson, HumanResources.Employee
                    WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
               WHERE  SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID
         FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID='43659' or SalesOrderID='43660'
FOR XML AUTO, TYPE ) as T(XmlCol)

C. ブラウザで販売注文情報を取得する ASPX アプリケーションの作成

次の例では、ASPX アプリケーションからストアドプロシージャが実行され、販売注文情報が XML で返されます。結果はブラウザに表示されます。ストアドプロシージャの SELECT ステートメントは例 B のステートメントに似ています。ただし、結果の XML は要素中心になります。

CREATE PROC GetSalesOrderInfo AS
SELECT 
      (SELECT top 2 SalesOrderID, SalesPersonID, CustomerID,
         (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
           from Sales.SalesOrderDetail
            WHERE  SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
            FOR XML AUTO, TYPE)
      FROM  Sales.SalesOrderHeader
        WHERE SalesOrderHeader.SalesOrderID = SalesOrder.SalesOrderID
      for xml auto, type),
        (SELECT * 
         FROM  (SELECT SalesPersonID, EmployeeID
              FROM Sales.SalesPerson, HumanResources.Employee
              WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
         WHERE  SalesPerson.SalesPersonID = SalesOrder.SalesPersonID
       FOR XML AUTO, TYPE, ELEMENTS)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
      FROM Sales.SalesOrderHeader, Sales.SalesPerson
      WHERE SalesOrderHeader.SalesPersonID = SalesPerson.SalesPersonID
     ) as SalesOrder
ORDER BY SalesOrder.SalesOrderID
FOR XML AUTO, TYPE
GO

次に .aspx アプリケーションを示します。これによってストアドプロシージャが実行され、XML がブラウザに返されます。

<%@LANGUAGE=C# Debug=true %>
<%@import Namespace="System.Xml"%>
<%@import namespace="System.Data.SqlClient" %><%
Response.Expires = -1;
Response.ContentType = "text/xml";
%>

<%
using(System.Data.SqlClient.SqlConnection c = new System.Data.SqlClient.SqlConnection("Data Source=server;Database=AdventureWorks;Integrated Security=SSPI;"))
using(System.Data.SqlClient.SqlCommand cmd = c.CreateCommand())
{
   cmd.CommandText = "GetSalesOrderInfo";
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Connection.Open();
   System.Xml.XmlReader r = cmd.ExecuteXmlReader();
   System.Xml.XmlTextWriter w = new System.Xml.XmlTextWriter(Response.Output);
   w.WriteStartElement("Root");
   r.MoveToContent();
   while(! r.EOF)
   {
      w.WriteNode(r, true);
   }
   w.WriteEndElement();
   w.Flush();
}
%>

アプリケーションをテストするには

AdventureWorks データベースにストアドプロシージャを作成します。
.aspx アプリケーションを c:\inetpub\wwwroot directory に保存します (GetSalesOrderInfo.aspx)。
アプリケーションを実行します (https://server/GetSalesOrderInfo.aspx)。

D. 製品価格を含む XML の構築

次の例では、Production.Product テーブルにクエリを実行し、特定の製品の ListPrice 値と StandardCost 値を取得します。ここでは、例示を目的として、両方の価格を <Price> 要素に返します。各 <Price> 要素には、PriceType 属性があります。次に、想定する XML の構造を示します。

<xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" elementFormDefault="qualified">
  <xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="https://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="Production.Product" type="xsd:anyType" />
</xsd:schema>
<Production.Product xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" ProductID="520">
  <Price  PriceType="ListPrice">133.34</Price>
  <Price  PriceType="StandardCost">98.77</Price>
</Production.Product>

次に、入れ子になった FOR XML クエリを示します。

SELECT Product.ProductID, 
          (SELECT 'ListPrice' as PriceType, 
                   CAST(CAST(ListPrice as NVARCHAR(40)) as XML) 
           FROM    Production.Product Price 
           WHERE   Price.ProductID=Product.ProductID 
           FOR XML AUTO, TYPE),
          (SELECT  'StandardCost' as PriceType, 
                   CAST(CAST(StandardCost as NVARCHAR(40)) as XML) 
           FROM    Production.Product Price 
           WHERE   Price.ProductID=Product.ProductID 
           FOR XML AUTO, TYPE)
FROM Production.Product
WHERE ProductID=520
for XML AUTO, TYPE, XMLSCHEMA

上のクエリに関して、次の点に注意してください。

外側の SELECT ステートメントで <Product> 要素が構築されます。この要素には ProductID 属性と 2 つの <Price> 子要素があります。
2 つの内側の SELECT ステートメントで <Price> 要素が 2 つ構築されます。各要素には PriceType 属性と製品価格を返す XML があります。
外側の SELECT ステートメントの XMLSCHEMA ディレクティブで、結果の XML の構造を記述するインライン XSD スキーマが生成されます。

サンプルとして効果の高いクエリにするには、次のクエリに示すように、FOR XML クエリを記述し、その結果に対して XQuery を記述して、XML の構造を変更します。

SELECT ProductID, 
 ( SELECT p2.ListPrice, p2.StandardCost
   FROM Production.Product p2 
   WHERE Product.ProductID = p2.ProductID
   FOR XML AUTO, ELEMENTS XSINIL, type ).query('
                                   for $p in /p2/*
                                   return 
                                    <Price PriceType = "{local-name($p)}">
                                     { data($p) }
                                    </Price>
                                  ')
FROM Production.Product
WHERE ProductID = 520
FOR XML AUTO, TYPE

上の例では xml データ型の query() メソッドを使用し、内側の FOR XML クエリで返される XML に対してクエリを実行し、必要な結果を構築しています。

次に結果を示します。

<Production.Product ProductID="520">
  <Price PriceType="ListPrice">133.3400</Price>
  <Price PriceType="StandardCost">98.7700</Price>
</Production.Product>

Share via

入れ子になった FOR XML クエリ

例

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

B. 入れ子構造で AUTO モードのクエリを使用した兄弟の生成

C. ブラウザで販売注文情報を取得する ASPX アプリケーションの作成

アプリケーションをテストするには

D. 製品価格を含む XML の構築

その他のリソース