T-SQL: 名前空間が指定された XML からデータを取得する

名前空間が指定された XML

前回 「 T-SQL クエリーで XML をテーブル形式に変換する 」 では、名前空間の指定されていないシンプルな XML から値を取得する方法をご紹介しました。

XML に名前空間の指定があると、XML データ型メソッドを使う際にも、名前空間の指定が必要になります。

ここでは、名前空間が指定された XML からデータを取得する方法をご紹介します。


XML にデフォルトの名前空間のみが指定されているケース

まずは、XML デフォルトの名前空間のみが指定されているケースです。

「 T-SQL クエリーで XML をテーブル形式に変換する 」 で使った XML の Students エレメントに、デフォルトの名前空間 xmlns="https://sql55.com/Schemas/Students" を追加します。 

<Students xmlns="https://sql55.com/Schemas/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 にデフォルトの名前空間が宣言されていると、「 T-SQL クエリーで XML をテーブル形式に変換する 」 でご紹介したクエリーのままでは、XML からデータを取得できません。

名前空間が指定された XML からデータを取得する 2


デフォルトの名前空間を指定して、XML から値を取得するクエリーは次の通りです。 

DECLARE @XMLData1 XML = 
'<Students xmlns="https://sql55.com/Schemas/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>';

WITH XMLNAMESPACES (DEFAULT 'https://sql55.com/Schemas/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	@XMLData1.nodes('/Students/Student') AS T(C);

[ 実行結果 ]
名前空間が指定された XML からデータを取得する 1

デフォルトの名前空間を指定するには WITH XMLNAMESPACES (DEFAULT 'XML名前空間のURI') を使います。

WITH というキーワードは T-SQL クエリーの他の用途でも使われるので、直前のステートメントがセミコロンで終わらないとシンタックスエラーになります。 ステートメントの最後のセミコロン自体はオプショナルなので、もし普段セミコロンを付けない方は、 ;WITH のように　WITH の前にセミコロンを付けてください。

XML データ型メソッドの value() や nodes() の説明は 「 T-SQL クエリーで XML をテーブル形式に変換する 」 をご覧ください。

XML に複数の名前空間が指定されているケース

次は XML に複数の名前空間が指定されているケースです。

例えば、この XML では、デフォルトの名前空間 xmlns="https://sql55.com/Schemas/Students" の他に、ns1 という接頭辞で xmlns:ns1="https://test.com/Student" が指定されています。

1 個目と 3 個目の Student 要素は接頭辞がついていないのでデフォルトの名前空間 "https://sql55.com/Schemas/Students" に、 2 個目の Student は要素の前に ns1: がついているので "https://test.com/Student" の名前空間に属します。 

<Students xmlns="https://sql55.com/Schemas/Students" 
	  xmlns:ns1="https://test.com/Student">
　　　<Student ID="1">
　　　　　　<StudentInfo>
　　　　　　　　　<FirstName>Saki</FirstName>
　　　　　　　　　<LastName>Suzuki</LastName>
　　　　　　</StudentInfo>
　　　　　　<TestResultCount>2</TestResultCount>
　　　　　　<ScoreAverage>92.00</ScoreAverage>
　　　</Student>
　　　<ns1:Student>
　　　　　 <ns1:FirstName>Jun</ns1:FirstName>
　　　　　 <ns1:LastName>Taguchi</ns1:LastName>
　　　　　 <ns1:Gender>M</ns1:Gender>
　　　　　 <ns1:ScoreAverage>85.64</ns1:ScoreAverage>
　　　</ns1:Student>
　　　<Student ID="2">
　　　　　　<StudentInfo>
　　　　　　　　　<FirstName>Miki</FirstName>
　　　　　　　　　<LastName>Sato</LastName>
　　　　　　</StudentInfo>
　　　　　　<TestResultCount>3</TestResultCount>
　　　　　　<ScoreAverage>89.50</ScoreAverage>
　　　</Student>
</Students>

この XML から名前空間を指定して、データを取得するクエリーは以下の通りです。 

DECLARE @XMLData2 XML = 
'<Students xmlns="https://sql55.com/Schemas/Students" 
	  xmlns:ns1="https://test.com/Student">
　　　<Student ID="1">
　　　　　　<StudentInfo>
　　　　　　　　　<FirstName>Saki</FirstName>
　　　　　　　　　<LastName>Suzuki</LastName>
　　　　　　</StudentInfo>
　　　　　　<TestResultCount>2</TestResultCount>
　　　　　　<ScoreAverage>92.00</ScoreAverage>
　　　</Student>
　　　<ns1:Student>
　　　　　 <ns1:FirstName>Jun</ns1:FirstName>
　　　　　 <ns1:LastName>Taguchi</ns1:LastName>
　　　　　 <ns1:Gender>M</ns1:Gender>
　　　　　 <ns1:ScoreAverage>85.64</ns1:ScoreAverage>
　　　</ns1:Student>
　　　<Student ID="2">
　　　　　　<StudentInfo>
　　　　　　　　　<FirstName>Miki</FirstName>
　　　　　　　　　<LastName>Sato</LastName>
　　　　　　</StudentInfo>
　　　　　　<TestResultCount>3</TestResultCount>
　　　　　　<ScoreAverage>89.50</ScoreAverage>
　　　</Student>
</Students>';

WITH XMLNAMESPACES (DEFAULT 'https://sql55.com/Schemas/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	@XMLData2.nodes('/Students/Student') AS T(C);

WITH XMLNAMESPACES ('https://test.com/Student' AS NS,
		　　　 DEFAULT 'https://sql55.com/Schemas/Students')  
SELECT	T.C.value('NS:FirstName[1]', 'NVARCHAR(MAX)') AS FirstName,
	T.C.value('NS:LastName[1]', 'NVARCHAR(MAX)') AS LastName,
	T.C.value('NS:Gender[1]', 'NVARCHAR(1)') AS TestResultCount,
	T.C.value('NS:ScoreAverage[1]', 'DECIMAL(5,2)') AS ScoreAverage
FROM	@XMLData2.nodes('/Students/NS:Student') AS T(C);

[ 実行結果 ]
名前空間が指定された XML からデータを取得する 3


一つ目のクエリーはデフォルトの名前空間のみが指定されている時のクエリーと同じです。

名前空間の接頭辞がついていない 1 個目と 3 個目の Student 要素の値のみが取得されます。 2 個目の Student は名前空間がマッチしないので取得されません。


二つ目のクエリーは WITH XMLNAMESPACES で、名前空間を二つ指定しています。 AS NS の NS 部分はエイリアスなので他の文字でも大丈夫です。

XQuery のパス式を指定する際に ns1: の接頭辞のついている要素には NS: を指定します。

@XMLData2.nodes('/Students/NS:Student') で NS の名前空間の Student 要素を行セットとして取得しています。

'https://test.com/Student' 名前空間に属する、2 個目の Student 要素の値のみが取得されていますね。

© 2010-2024 SQL Server 入門