[T-SQL] XMLデータ型を使用する

handcraft 4/10/2009 24928 3.2 SQL Server

SQL Server 2005 で XML データ型を使用するサンプルを掲載します。

動作確認は SQL Server 2005 Develoer Edition で行っています。

下記にXMLデータ型を使用する上で便利なリンクを掲載しておきます。

SQL Server での XML の使用
http://msdn.microsoft.com/ja-jp/library/ms190936(SQL.90).aspx
XML データ型のメソッド
http://msdn.microsoft.com/ja-jp/library/ms190798(SQL.90).aspx

1. XMLデータタイプを使用する

XML型を列として持つ一時テーブルを作成します。このテーブルは本サンプルで使用していきます。サンプルでは、XMLフラグメントをCAST関数を使用して変数に設定し、一時テーブルにINSERTしてSELECTしています。

-- XML型の列を含む一時テーブルの作成
CREATE TABLE #XMLSampleTable
(
  name nvarchar(100) NOT NULL,
  xmlcol xml NULL
)

-- XML データタイプの宣言
DECLARE @xmlfragment as XML
SET @xmlfragment = CAST('<test  name="naame">text</test><test  name="naame">text</test>' as XML)

select @xmlfragment

-- データのINSERT
INSERT INTO #XMLSampleTable
   VALUES( 'test', @xmlfragment)

SELECT *
  from #XMLSampleTable

XMLデータは整形型です。XMLスキーマと関連付けるには、まず、CREATE XML SCHEMA COLLECTION を使用して、スキーマコレクションを作成し、スキーマを定義し、XMLデータ型の宣言時に関連付けを行います。下記サンプルでは、CREATE XML SCHEMA COLLECTION を使用してスキーマを定義するテンプレートと、作成済みのスキーマコレクションをsys.XML_schema_collections と定義されたXMLネームスペースを sys.XML_schema_namespaces で確認するSQLも掲載しています。

作成したスキーマコレクションをサンプルで示すようにデータ型の定義時にスキーマコレクションを指定します。

-- XMLデータ型にXMLスキーマを関連付ける場合は
-- スキーマコレクションを作成してスキーマを定義
CREATE XML SCHEMA COLLECTION [スキーマコレクション名] 
AS [スキーマ定義]

-- スキーマコレクションを削除する
DROP XML SCHEMA COLLECTION [スキーマコレクション名]

-- XMLスキーマコレクションを列挙する
select *
  from sys.XML_schema_collections

-- XMLスキーマコレクションに含まれる
-- スキーマのネームスペースを列挙する
select *
   from sys.XML_schema_namespaces

-- XMLスキーマが関連付けられたXMLデータ型を宣言する
DECLARE @xmlfragment XML (DOCUMENT [スキーマコレクション名])

CREATE #XmlSample
(
  name nvarchar(100) NOT NULL,
  xmlcol xml ([スキーマコレクション名] NULL
)

2. XMLデータ型の検索、修正

XMLデータ型の検索や編集は、専用の関数を使用します。存在確認はexist関数を、XMLデータに対してクエリを出す場合はquery関数を、クエリの値をSQL型に変換する場合はvalue関数を使用します。複数ノードをそれぞれの行として扱うにはnodes関数、データを修正するにはmodify関数を使用します。使用方法を下のサンプルに掲載します。詳細はサンプル冒頭のリンクを参照してください。

-- XMLデータを検索する
select *
  from #XMLSampleTable
 where xmlcol.exist('/test[@name="naame"]') = 1

select xmlcol.value('/test/text()[1]', 'nvarchar(10)')
  from #XMLSampleTable
 where xmlcol.exist('/test[@name="naame"]') = 1

select xmlcol.query('/test/text()')
  from #XMLSampleTable

select xmlcol.value('(/test/@name)[1]', 'nvarchar(100)')
  from #XMLSampleTable

select xmlcol.value('(/test/text())[1]', 'nvarchar(100)')
  from #XMLSampleTable


DECLARE @xmlfragment1 as XML
SET @xmlfragment1 = CAST('<test  name="naame">text</test><test  name="naame">text1</test>' as XML)

select testcol.value('.','nvarchar(100)')
  from @xmlfragment1.nodes('/test')
    as XMLtest(testcol)

-- XMLデータタイプの修正
-- 詳細はBooks Online 
UPDATE #XMLSampleTable
  SET  xmlcol.modify(' insert <test name="ins">insert test </test> into (/)[1]')
 WHERE name='test'
select *
  from #XMLSampleTable

3. XMLデータ型にインデックスを設定する

XMLデータ型には最大3つまでインデックスを設定できます。また、クラスタ化インデックスが設定されている必要があります。

インデックスはCREATE XML INDEXを使用します。定義テンプレートを下記に示します。また、XMLデータ列のインデックス情報はsys.XML_indexesカタログビューを使用することで確認できます。

-- XMLデータ型にインデックスを設定するばあい
CREATE XML INDEX [インデックス名]
-- SQL文を使用します。
-- XMLのインデックス情報を参照する場合は
select *
  from sys.XML_indexes
 where OBJECT_ID('dbo.XmlTest')

4.XMLと行レコードの変換

XMLとテーブルの行を相互に変換できます。SELECTした行セットをXMLに変換する場合はFOR XML を SELECT に指定します。XMLからテーブルの行を作成する場合はOPENXML関数とsp_XML_preparedocumentプロシージャを使用します。

下記にサンプルを掲載します。細かい追加オプションなどはBooks Online 等を参照願います。

-- テーブルをXMLに変換する
select TOP 2 *
 from Article FOR XML AUTO

-- XMLからテーブルに変換する
DECLARE @xmlfragment2 as XML
SET @xmlfragment2 = CAST('<root><test  name="naame">text</test><test  name="naame">text1</test></root>' as XML)

DECLARE @xmlpointer int
EXEC sp_XML_preparedocument @xmlpointer OUTPUT, @xmlfragment2

SELECT colName
  FROM OPENXML(@xmlpointer, '/root/test', 0)
  WITH (colName nvarchar(10) '@name')

5. まとめ

今回の説明は以上です。誤り、指摘点などがありましたらご連絡ください。

// Net planet-es V2