次の方法で共有

  • [アーティクル]

xml データ型に対する FOR XML サポート

FOR XML クエリによって SELECT 句で xml 型の列が指定された場合、ELEMENTS ディレクティブを指定したかどうかにかかわらず、返された XML では列の値が要素としてマップされます。xml 型の列内の XML 宣言はシリアル化されません。

たとえば次のクエリは、ContactID、FirstName、および LastName 列、および xml 型の AdditionalContactInfo 列からの電話番号など、顧客の連絡先の情報を取得します。

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

クエリでは ELEMENTS ディレクティブが指定されていないため、xml 型の列から取得された追加の連絡先の情報の値を除き、列の値が属性として返されます。これらは要素として返されます。

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

<Contact ContactID="1" FirstName="Syed" LastName="Abbas">
    <act:number xmlns:act=
       "http://schemas.adventure-works.com/AdditionalContactTypes">
          111-111-1111</act:number>
    <act:number xmlns:act=
       "http://schemas.adventure-works.com/AdditionalContactTypes">
         112-111-1111</act:number>
</Contact>
<Contact ContactID="2" FirstName="Catherine" LastName="Abel">
    ...
</Contact>
...

XQuery によって生成された XML 列に対して列の別名を指定した場合、その別名は XQuery によって生成された XML にラッパー要素を追加する際に使用されます。たとえば、次のクエリでは、列の別名として MorePhoneNumbers を指定しています。

SELECT ContactID, FirstName, LastName, AdditionalContactInfo.query('
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

XQuery によって返された XML は、次の結果の一部で示すように、<MorePhoneNumbers> 要素でラップされます。

<Contact ContactID="1" FirstName="Syed" LastName="Abbas">
  <MorePhoneNumbers>
    <act:number xmlns:act="http://schemas.adventure-works.com/AdditionalContactTypes">111-111-1111</act:number>
    <act:number xmlns:act="http://schemas.adventure-works.com/AdditionalContactTypes">112-111-1111</act:number>
  </MorePhoneNumbers>
</Contact>
<Contact ContactID="2" FirstName="Catherine" LastName="Abel">
  <MorePhoneNumbers>
        ...
  </MorePhoneNumbers>
</Contact>
...

クエリで ELEMENTS ディレクティブを指定した場合、結果の XML では、ContactID、LastName、および FirstName が要素として返されます。

次に、xml 型の列の XML データでは、FOR XML の処理ロジックによって XML 宣言がシリアル化されない例を示します。

create table t(i int, x xml)
go
insert into t values(1, '<?xml version="1.0" encoding="UTF-8" ?>
                             <Root SomeID="10" />')
select i, x
from   t
for xml auto

次に結果を示します。結果の XML では、XML 宣言 <?xml version="1.0" encoding="UTF-8" ?> はシリアル化されません。

<root>
  <t i="1">
    <x>
      <Root SomeID="10" />
    </x>
  </t>
</root>

ユーザー定義関数から XML を返す

FOR XML クエリを使用して、次のいずれかを返すユーザー定義関数から XML を返すことができます。

  • 1 つの xml 型の列を持つテーブル

  • xml 型のインスタンス

たとえば、次のユーザー定義関数は、xml 型の 1 つの列を持つテーブルを返します。

CREATE FUNCTION MyUDF (@ProudctModelID int)
RETURNS @T TABLE
  (
     ProductDescription xml
  )
AS
BEGIN
  INSERT @T
     SELECT CatalogDescription.query('
declare namespace PD="https://www.adventure-works.com/schemas/products/description";
                    //PD:ProductDescription  ')
     FROM Production.ProductModel
     WHERE ProductModelID = @ProudctModelID
  RETURN
END

ユーザー定義関数を実行して、その関数から返されたテーブルにクエリを実行することができます。この例では、そのテーブルのクエリの結果として返された XML が、xml 型の変数に代入されます。

declare @x xml
set @x = (SELECT * FROM MyUDF(19))
select @x

次に、別のユーザー定義関数の例を示します。このユーザー定義関数は、xml 型のインスタンスを返します。この例では、スキーマの名前空間が指定されているため、ユーザー定義関数は型指定された XML インスタンスを返します。

drop function MyUDF4
go
CREATE FUNCTION MyUDF4 (@ProductModelID int) 
RETURNS xml ([Production].[ProductDescriptionSchemaCollection])
AS
BEGIN
  declare @x xml
  set @x =   ( SELECT CatalogDescription
          FROM Production.ProductModel
          WHERE ProductModelID = @ProductModelID )
  return @x
END

ユーザー定義関数から返された XML は、次のように xml 型の変数に代入できます。

declare @x xml
SELECT @x= dbo.MyUDF4 (19) 
select @x