T-SQL クエリーで XML をテーブル形式に変換する

T-SQL クエリーで XML をテーブル形式に変換する

T-SQL クエリーで XML からデータを取得する方法は何通りかありますが、今回は XML の文字列をテーブル形式に変換する方法のひとつをご紹介します。

XML データ型のメソッド

SQL Server には XML データ型メソッドというものがあり、XML 型の変数や XML 型のカラムに入っているデータに対して実行でき、ノードや値を取得したり編集したりできます。

XML データ型メソッドには query()、value()、exist()、modify()、nodes() などがありますが、今回は value() と nodes() を使って、XML の文字列をテーブル形式に変換します。

value() は value(XQuery, SQLType) のように使用し、XQuery のパス式で指定した値を SQL 型に変換して返します。

nodes() は nodes(XQuery) AS Table(Column) のように使用し、ノードを XQuery で指定した行セットとして返します。

XML をテーブル形式に変換する

では、value() と nodes() を使って、XML をテーブル形式に変換してみましょう。

次のような、学生情報の XML があります。

<Students>
　　　<Student ID="1">
　　　　　　<StudentInfo>
　　　　　　　　　<FirstName>Saki</FirstName>
　　　　　　　　　<LastName>Suzuki</LastName>
　　　　　　</StudentInfo>
　　　　　　<TestResultCount>2</TestResultCount>
　　　　　　<ScoreAverage>92.00</ScoreAverage>
　　　</Student>
　　　<Student ID="2">
　　　　　　<StudentInfo>
　　　　　　　　　<FirstName>Miki</FirstName>
　　　　　　　　　<LastName>Sato</LastName>
　　　　　　</StudentInfo>
　　　　　　<TestResultCount>3</TestResultCount>
　　　　　　<ScoreAverage>89.50</ScoreAverage>
　　　</Student>
</Students>

これの XML をテーブル形式にするクエリーは以下の通りです。

DECLARE @XMLData XML = 
'<Students>
　　　<Student ID="1">
　　　　　　<StudentInfo>
　　　　　　　　　<FirstName>Saki</FirstName>
　　　　　　　　　<LastName>Suzuki</LastName>
　　　　　　</StudentInfo>
　　　　　　<TestResultCount>2</TestResultCount>
　　　　　　<ScoreAverage>92.00</ScoreAverage>
　　　</Student>
　　　<Student ID="2">
　　　　　　<StudentInfo>
　　　　　　　　　<FirstName>Miki</FirstName>
　　　　　　　　　<LastName>Sato</LastName>
　　　　　　</StudentInfo>
　　　　　　<TestResultCount>3</TestResultCount>
　　　　　　<ScoreAverage>89.50</ScoreAverage>
　　　</Student>
</Students>';

SELECT	T.C.value('@ID', 'INT') AS StudentID,
	T.C.value('(StudentInfo/FirstName)[1]', 'NVARCHAR(MAX)') AS FirstName,
	T.C.value('(StudentInfo/LastName)[1]', 'NVARCHAR(MAX)') AS LastName,
	T.C.value('TestResultCount[1]', 'INT') AS TestResultCount,
	T.C.value('ScoreAverage[1]', 'DECIMAL(5,2)') AS ScoreAverage
FROM	@XMLData.nodes('/Students/Student') AS T(C);

[ 実行結果 ]

DECLARE @XMLData XML = 'XML文字列'

まず、@XMLData という名前の XML 型の変数を定義して、先ほどの　XML 文字列を代入します。

XML 文字列のフォーマットが正しくない (invalid な) 時は、値を XML 型の変数に代入した時にエラーになりますのでご注意ください。

FROM @XMLData.nodes('/Students/Student') AS T(C);

FROM の @XMLData.nodes('/Students/Student') で、XML の /Students/Student の階層で行セットが返るように指定しています。

それに続く AS T(C) の T(C) は新しい行セットにアクセスする際のエイリアスなので、他の文字でも大丈夫です。

その行セットに対して、 SELECT で T.C.value(XQuery, SQLType) を使って値を取得していきます。

SELECT T.C.value('@ID', 'INT') AS StudentID,

XML の 属性 (Attribute) の値を取得したい時は XQuery に パス + @属性名 を指定します。

T.C.value('@ID', 'INT') では Student の属性 の ID の値を INT 型で取得しています。

指定した SQL のデータ型に 変換できない値が入っていると、変換でエラーになりますのでご注意ください。

T.C.value('(StudentInfo/FirstName)[1]', 'NVARCHAR(MAX)') AS FirstName, ...

XML の 要素 (element) の値を取得したい時は XQuery に (パス + 要素名)[1] を指定します。

.value() では、XQuery が返す値が 2 個以上の時はエラーになります。

実際には、そのパス式の返す値はひとつしかなくても、ひとつの値を返すことを明示的に指定しなければならないので [1] をつけています。

XQuery で指定した値が存在しない場合でもエラーにはならず、値が NULL になります。