T-SQL クエリーで XML をテーブル形式に変換する
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 になります。
XML に名前空間が指定されている場合は こちらもご覧ください。 「 名前空間が指定された XML からデータを取得する 」