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

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

SQL Server では、xml (Transact-SQL) データ型がサポートされるため、必要に応じて 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 を処理できます。 TYPE ディレクティブを指定していない最上位レベルでの FOR XML 句は、カーソルと共に使用できません。

例

次の例は、FOR XML クエリでの TYPE ディレクトリの使用方法を示しています。

FOR XML クエリの結果の xml 型としての取得

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

USE AdventureWorks2022;
Go
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML AUTO, TYPE;

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

<Person.Person BusinessEntityID="1" FirstName="Ken" LastName="Sánchez"/>
<Person.Person BusinessEntityID="2" FirstName="Terri" LastName="Duffy"/>
...

FOR XML クエリ結果の xml 型の変数への代入

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

USE AdventureWorks2022;
GO
DECLARE @x xml;
SET @x = (
   SELECT BusinessEntityID,
          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.Person
   FOR XML AUTO, TYPE);
SELECT @x;
GO

FOR XML クエリの結果のクエリ

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

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

USE AdventureWorks2022;
GO
SELECT (SELECT BusinessEntityID, 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.Person
FOR XML AUTO, TYPE).query('/Person.Person[1]');

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

結果を次に示します。

<Person.Person BusinessEntityID="1" FirstName="Ken" LastName="Sánchez">
  <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.Person>

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

USE AdventureWorks2022;
GO
DECLARE @FirstPhoneFromAdditionalContactInfo varchar(40);
SELECT @FirstPhoneFromAdditionalContactInfo =
( SELECT BusinessEntityID, 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.Person Contact
   FOR XML AUTO, TYPE).value('
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
  /Contact[@BusinessEntityID="1"][1]/PhoneNumbers[1]/act:number[1]', 'varchar(40)'
);
SELECT @FirstPhoneFromAdditionalContactInfo;

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

注意

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

INSERT、UPDATE、および DELETE (Transact-SQL DML) での FOR XML クエリの結果の使用

次の例では、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

関連項目