FOR XML クエリの TYPE ディレクティブ

SQL Server 2000 では、FOR XML クエリの結果は常にテキスト形式でクライアントに直接返されます。SQL Server 2005 では xml データ型がサポートされるため、必要に応じて TYPE ディレクティブを指定することにより、FOR XML クエリの結果を xml データ型として返すように要求できます。これにより、サーバーで FOR XML クエリの結果を処理できるようになります。たとえば、その結果に対して XQuery を指定したり、結果を xml 型の変数に割り当てたり、入れ子になった FOR XML クエリを記述したりできます。

メモ :
SQL Server は、TYPE ディレクティブを使用した FOR XML クエリなど、サーバーでのさまざまな構成の結果として、または SQL テーブルの列および出力パラメータの XML インスタンス データ値を返すために xml データ型が使用された場合に、XML データ型のインスタンス データをクライアントに返します。クライアント アプリケーション コードでは、ADO.NET プロバイダが、この XML データ型の情報をサーバーからバイナリ エンコードで送信するように要求します。ただし、TYPE ディレクティブを指定せずに FOR XML を使用した場合、この XML データは文字列型として返されます。どんな場合でも、クライアント プロバイダは常にいずれかの形式の XML を処理できます。

例

FOR XML クエリの使用方法について、例を使って説明します。

A. FOR XML クエリの結果を xml 型として取得する

次のクエリでは、Contacts テーブルから顧客の連絡先に関する情報を取得します。FOR XML に TYPE ディレクティブが指定されているので、結果は xml 型として返されます。

SELECT ContactID, FirstName, LastName, Phone
FROM Person.Contact
ORDER BY ContactID
FOR XML AUTO, TYPE

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

<Contact ContactID="1" FirstName="Syed" LastName="Abbas" 
         Phone="398-555-0132"/>
<Contact ContactID="2" FirstName="Catherine" LastName="Abel" 
         Phone="747-555-0171"/>
...

B. FOR XML クエリの結果を xml 型の変数に代入する

次の例では、FOR XML の結果が xml 型の変数 @x に代入されます。このクエリでは、xml 型の AdditionalContactInfo 列から、ContactID、FirstName、LastName、追加の電話番号などの連絡先に関する情報を取得します。FOR XML 句に TYPE ディレクティブを指定するので、この XML は xml 型として返され、変数に代入されます。

DECLARE @x XML
SET @x = (
   SELECT ContactID, 
          FirstName, 
          LastName, 
          AdditionalContactInfo.query('
declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
              //act:telephoneNumber/act:number') as MorePhoneNumbers
   FROM Person.Contact
   FOR XML AUTO, TYPE)
SELECT @x
GO

C. FOR XML クエリの結果にクエリする

FOR XML クエリにより、XML が返されます。したがって、FOR XML クエリにより返された XML 結果には、query() や value() などの xml 型のメソッドを適用できます。

次のクエリでは、xml データ型の query() メソッドを使用して、FOR XML クエリの結果にクエリします。詳細については、「query() メソッド (xml データ型)」を参照してください。

SELECT (SELECT ContactID, FirstName, LastName, AdditionalContactInfo.query('
declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
 //act:telephoneNumber/act:number
') as PhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE).query('/Person.Contact[1]')

内側の SELECT ... FOR XML クエリにより xml 型の結果が返され、外側の SELECT によりその xml 型の結果に query() メソッドが適用されます。TYPE ディレクティブが指定されていることに注意してください。

結果を次に示します。

<Person.Contact ContactID="1" FirstName="Gustavo" LastName="Achong">
  <PhoneNumbers>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">111-111-1111</act:number>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">112-111-1111</act:number>
  </PhoneNumbers>
</Person.Contact>

次のクエリでは、xml データ型の value() メソッドを使用して、SELECT ... FOR XML クエリにより返された XML 結果から値を取得します。詳細については、「value() メソッド (xml データ型)」を参照してください。

declare @FirstPhoneFromAdditionalContactInfo varchar(40);
SELECT @FirstPhoneFromAdditionalContactInfo = 
 ( SELECT ContactID, FirstName, LastName, AdditionalContactInfo.query('
declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

   //act:telephoneNumber/act:number
   ') as PhoneNumbers
   FROM Person.Contact Contact
   FOR XML AUTO, TYPE).value('
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
  /Contact[@ContactID="1"][1]/PhoneNumbers[1]/act:number[1]', 'varchar(40)'
 )
select @FirstPhoneFromAdditionalContactInfo

value() メソッドの XQuery パス式により、ContactID が 1 の顧客の連絡先から 1 つ目の電話番号が取得されます。

メモ :
TYPE ディレクティブを指定しなかった場合、この FOR XML クエリの結果は nvarchar(max) 型として返されます。

D. FOR XML クエリの結果を INSERT、UPDATE、および DELETE (Transact-SQL DML) に使用する

次の例では、FOR XML クエリをデータ操作言語 (DML) ステートメントで使用する方法について説明します。この例では、FOR XML により xml 型のインスタンスが返されます。また、INSERT ステートメントによりこの XML がテーブルに挿入されます。

CREATE TABLE T1(intCol int, XmlCol xml)
go
INSERT INTO T1 
VALUES(1, '<Root><ProductDescription ProductModelID="1" /></Root>')
go

CREATE TABLE T2(XmlCol xml)
go
INSERT INTO T2(XmlCol) 
SELECT (SELECT XmlCol.query('/Root') 
        FROM T1 
        FOR XML AUTO,TYPE) 
go