入れ子になった FOR XML クエリを使用した 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 xmlns="" PriceType="ListPrice">133.34</Price>  
  <Price xmlns="" PriceType="StandardCost">98.77</Price>  
</Production.Product>  

これは、入れ子になった FOR XML クエリです。

USE AdventureWorks2012;  
GO  
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 ステートメントは、ProductID 属性と 2 つの<Price>子要素を持つ<Product>要素を構築します。

• 2 つの内部 SELECT ステートメントは、2 つの <Price> 要素を構築し、それぞれが 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>  

こちらもご覧ください

入れ子になった FOR XML クエリを使用する