例: PATH モードの使用

2017-06-13

次の例は、SELECT クエリから XML を生成する場合の PATH モードの使用方法を示しています。これらのクエリの多くは、ProductModel テーブルの Instructions 列に格納されている自転車製造命令 XML ドキュメントに対して指定されます。

単純な PATH モードクエリの指定

このクエリでは、FOR XML PATH モードを指定します。

USE AdventureWorks2012;  
GO  
SELECT   
       ProductModelID,  
       Name  
FROM Production.ProductModel  
WHERE ProductModelID=122 OR ProductModelID=119  
FOR XML PATH;  
GO

次の結果は、結果の行セット内の各列値が要素にラップされる、要素中心の XML です。 SELECT句では列名のエイリアスが指定されないため、生成される子要素名は、SELECT句の対応する列名と同じです。行セットの各行に対して、 <row> タグが追加されます。

<row>

<ProductModelID>122</ProductModelID>

<Name>All-Purpose Bike Stand</Name>

</row>

<row>

<ProductModelID>119</ProductModelID>

<Name>Bike Wash</Name>

</row>

次の結果は、ELEMENTS オプションを指定したRAW モードクエリと同じです。結果セット内の各行の既定の <row> 要素を持つ要素中心の XML を返します。

USE AdventureWorks2012;  
GO  
SELECT ProductModelID,  
       Name  
FROM Production.ProductModel  
WHERE ProductModelID=122 OR ProductModelID=119  
FOR XML RAW, ELEMENTS;

必要に応じて、行要素名を指定して、既定の <row>を上書きできます。たとえば、次のクエリは、行セット内の各行の <ProductModel> 要素を返します。

USE AdventureWorks2012;  
GO  
SELECT ProductModelID,  
       Name  
FROM Production.ProductModel  
WHERE ProductModelID=122 or ProductModelID=119  
FOR XML PATH ('ProductModel');  
GO

結果の XML には、指定された行要素名が含まれます。

<ProductModel>

<ProductModelID>122</ProductModelID>

<Name>All-Purpose Bike Stand</Name>

</ProductModel>

<ProductModel>

<ProductModelID>119</ProductModelID>

<Name>Bike Wash</Name>

</ProductModel>

長さ 0 の文字列を指定した場合、折り返し要素は生成されません。

USE AdventureWorks2012;  
GO  
SELECT ProductModelID,  
       Name  
FROM Production.ProductModel  
WHERE ProductModelID=122 OR ProductModelID=119  
FOR XML PATH ('');  
GO

結果を次に示します。

<ProductModelID>122</ProductModelID>

<Name>All-Purpose Bike Stand</Name>

<ProductModelID>119</ProductModelID>

<Name>Bike Wash</Name>

XPath に似た列名の指定

次のクエリでは、指定 ProductModelID 列名は '@' で始まり、スラッシュ ('/') は含まれません。そのため、対応する列値を持つ <row> 要素の属性が、結果の XML に作成されます。

USE AdventureWorks2012;  
GO  
SELECT ProductModelID AS "@id",  
       Name  
FROM Production.ProductModel  
WHERE ProductModelID=122 OR ProductModelID=119  
FOR XML PATH ('ProductModelData');  
GO

結果を次に示します。

< ProductModelData id="122">

<Name>All-Purpose Bike Stand</Name>

</ ProductModelData >

< ProductModelData id="119">

<Name>Bike Wash</Name>

</ ProductModelData >

FOR XMLで root オプションを指定することで、最上位レベルの要素を 1 つ追加できます。

SELECT ProductModelID AS "@id",  
       Name  
FROM Production.ProductModel  
WHERE ProductModelID=122 or ProductModelID=119  
FOR XML PATH ('ProductModelData'), root ('Root');  
GO

階層を生成するには、PATH のような構文を含めることができます。たとえば、次の結果に示すように、 Name 列の列名を "SomeChild/ModelName" に変更すると、階層を含む XML が取得されます。

<Root>

<ProductModelData id="122">

<SomeChild>

<ModelName>All-Purpose Bike Stand</ModelName>

</SomeChild>

</ProductModelData>

<ProductModelData id="119">

<SomeChild>

<ModelName>Bike Wash</ModelName>

</SomeChild>

</ProductModelData>

</Root>

次のクエリでは、製品モデル ID と名前に加えて、製品モデルの製造指示の場所を取得します。 Instructions 列はxml型であるため、xmlデータ型のquery()メソッドを指定して場所を取得します。

SELECT ProductModelID AS "@id",  
       Name,  
       Instructions.query('declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";  
                /MI:root/MI:Location   
              ') AS ManuInstr  
FROM Production.ProductModel  
WHERE ProductModelID = 7  
FOR XML PATH ('ProductModelData'), root ('Root');  
GO

次に結果の一部を示します。クエリでは列名として ManuInstr が指定されているため、 query() メソッドによって返される XML は、次に示すように <ManuInstr> タグでラップされます。

<Root>

<ProductModelData id="7">

<Name>HL Touring Frame</Name>

<ManuInstr>

<MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"

<MI:step>...</MI:step>...

</MI:Location>

...

</ManuInstr>

</ProductModelData>

</Root>

前の FOR XML クエリでは、 <Root> 要素と <ProductModelData> 要素の名前空間を含めることができます。これを行うには、最初にプレフィックスのバインドを名前空間に定義し、それからWITH XMLNAMESPACESを使用して、FOR XML クエリでプレフィックスを使用します。詳細については、「 WITH XMLNAMESPACES を使用したクエリへの名前空間の追加」を参照してください。

USE AdventureWorks2012;  
GO  
WITH XMLNAMESPACES (  
   'uri1' AS ns1,    
   'uri2' AS ns2,  
   'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as MI)  
SELECT ProductModelID AS "ns1:ProductModelID",  
       Name           AS "ns1:Name",  
       Instructions.query('  
                /MI:root/MI:Location   
              ')   
FROM Production.ProductModel  
WHERE ProductModelID=7  
FOR XML PATH ('ns2:ProductInfo'), root('ns1:root');  
GO

MI プレフィックスもWITH XMLNAMESPACESで定義されていることに注意してください。その結果、指定されたxml型のquery() メソッドは、クエリプロローグのプレフィックスを定義しません。結果を次に示します。

<ns1:root xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" xmlns="uri2" xmlns:ns2="uri2" xmlns:ns1="uri1">

<ns2:ProductInfo>

<ns1:ProductModelID>7</ns1:ProductModelID>

<ns1:Name>HL Touring Frame</ns1:Name>

<MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"

LaborHours="2.5" LotSize="100" MachineHours="3" SetupHours="0.5" LocationID="10" xmlns="">

<MI:step>

Insert <MI:material>aluminum sheet MS-2341</MI:material> into the <MI:tool>T-85A framing tool</MI:tool>.

</MI:step>

...

</MI:Location>

...

</ns2:ProductInfo>

</ns1:root>

PATH モードを使用した値リストの生成

製品モデルごとに、このクエリは製品 ID の値リストを作成します。各製品 ID に対して、クエリでは、次の XML フラグメントに示すように、入れ子になった要素 <ProductName> も構築されます。

<ProductModelData ProductModelID="7" ProductModelName="..."

ProductIDs="product id list in the product model" >

<ProductName>...</ProductName>

...

</ProductModelData>

これは、必要な XML を生成するクエリです。

USE AdventureWorks2012;  
GO  
SELECT ProductModelID     AS "@ProductModelID",  
       Name               S "@ProductModelName",  
      (SELECT ProductID AS "data()"  
       FROM   Production.Product  
       WHERE  Production.Product.ProductModelID =   
              Production.ProductModel.ProductModelID  
       FOR XML PATH ('')) S "@ProductIDs",  
       (SELECT Name AS "ProductName"  
       FROM   Production.Product  
       WHERE  Production.Product.ProductModelID =   
              Production.ProductModel.ProductModelID  
        FOR XML PATH ('')) as "ProductNames"  
FROM   Production.ProductModel  
WHERE  ProductModelID= 7 or ProductModelID=9  
FOR XML PATH('ProductModelData');

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

入れ子になった最初の SELECT は、列名として data() を使用して ProductID の一覧を返します。クエリでは、 FOR XML PATHの行要素名として空の文字列が指定されているため、要素は生成されません。代わりに、値リストが ProductID 属性に割り当てられます。
2 つ目の入れ子になった SELECT は、製品モデル内の製品の製品名を取得します。クエリでは列名としてProductNamesを指定するため、<ProductNames>要素にラップされて返される<ProductName>要素が生成されます。

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

<ProductModelData PId="7"

ProductModelName="HL Touring Frame"

ProductIDs="885 887 ...">

<ProductNames>

<ProductName>HL Touring Frame - Yellow, 60</ProductName>

<ProductName>HL Touring Frame - Yellow, 46</ProductName></ProductNames>

...

</ProductModelData>

<ProductModelData PId="9"

ProductModelName="LL Road Frame"

ProductIDs="722 723 724 ...">

<ProductNames>

<ProductName>LL Road Frame - Black, 58</ProductName>

<ProductName>LL Road Frame - Black, 60</ProductName>

<ProductName>LL Road Frame - Black, 62</ProductName>

...

</ProductNames>

</ProductModelData>

製品名を構築するサブクエリは、結果をエンティティ化された文字列として返し、XML に追加します。 type ディレクティブ FOR XML PATH (''), typeを追加すると、サブクエリは結果を xml 型として返し、エンティティ化は行われません。

USE AdventureWorks2012;  
GO  
SELECT ProductModelID AS "@ProductModelID",  
      Name AS "@ProductModelName",  
      (SELECT ProductID AS "data()"  
       FROM   Production.Product  
       WHERE  Production.Product.ProductModelID =   
              Production.ProductModel.ProductModelID  
       FOR XML PATH ('')  
       ) AS "@ProductIDs",  
       (  
       SELECT Name AS "ProductName"  
       FROM   Production.Product  
       WHERE  Production.Product.ProductModelID =   
              Production.ProductModel.ProductModelID  
       FOR XML PATH (''), type  
       ) AS "ProductNames"  
  
FROM Production.ProductModel  
WHERE ProductModelID= 7 OR ProductModelID=9  
FOR XML PATH('ProductModelData');

結果の XML に名前空間を追加する

WITH XMLNAMESPACES を使用した名前空間の追加の説明に従って、WITH XMLNAMESPACES を使用して PATH モードのクエリに名前空間を含めることができます。たとえば、SELECT 句で指定された名前には、名前空間プレフィックスが含まれます。次の PATH モードクエリは、名前空間を持つ XML を構築します。

SELECT 'en'    as "English/@xml:lang",  
       'food'  as "English",  
       'ger'   as "German/@xml:lang",  
       'Essen' as "German"  
FOR XML PATH ('Translation')  
GO

< English >要素に追加された@xml:lang属性は、定義済みの xml 名前空間で定義されます。

結果を次に示します。

<Translation>

<English xml:lang="en">food</English>

<German xml:lang="ger">Essen</German>

</Translation>

次のクエリは C の例に似ていますが、 WITH XMLNAMESPACES を使用して XML 結果に名前空間を含める点が異なります。詳細については、「 WITH XMLNAMESPACES を使用したクエリへの名前空間の追加」を参照してください。

USE AdventureWorks2012;  
GO  
WITH XMLNAMESPACES ('uri1' AS ns1,  DEFAULT 'uri2')  
SELECT ProductModelID AS "@ns1:ProductModelID",  
      Name AS "@ns1:ProductModelName",  
      (SELECT ProductID AS "data()"  
       FROM   Production.Product  
       WHERE  Production.Product.ProductModelID =   
              Production.ProductModel.ProductModelID  
       FOR XML PATH ('')  
       ) AS "@ns1:ProductIDs",  
       (  
       SELECT ProductID AS "@ns1:ProductID",   
              Name AS "@ns1:ProductName"  
       FROM   Production.Product  
       WHERE  Production.Product.ProductModelID =   
              Production.ProductModel.ProductModelID  
       FOR XML PATH , type   
       ) AS "ns1:ProductNames"  
FROM Production.ProductModel  
WHERE ProductModelID= 7 OR ProductModelID=9  
FOR XML PATH('ProductModelData'), root('root');

結果を次に示します。

<root xmlns="uri2" xmlns:ns1="uri1">

<ProductModelData ns1:ProductModelID="7" ns1:ProductModelName="HL Touring Frame" ns1:ProductIDs="885 887 888 889 890 891 892 893">

<ns1:ProductNames>

<row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="885" ns1:ProductName="HL Touring Frame - Yellow, 60" />

<row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="887" ns1:ProductName="HL Touring Frame - Yellow, 46" />

...

</ns1:ProductNames>

</ProductModelData>

<ProductModelData ns1:ProductModelID="9" ns1:ProductModelName="LL Road Frame" ns1:ProductIDs="722 723 724 725 726 727 728 729 730 736 737 738">

<ns1:ProductNames>

<row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="722" ns1:ProductName="LL Road Frame - Black, 58" />

...

</ns1:ProductNames>

</ProductModelData>

</root>

こちらもご覧ください

FOR XML で PATH モードを使用する

次の方法で共有

例: PATH モードの使用

単純な PATH モード クエリの指定

XPath に似た列名の指定

PATH モードを使用した値リストの生成

結果の XML に名前空間を追加する

こちらもご覧ください

その他のリソース

単純な PATH モードクエリの指定