SQLServerのXMLデータ中から特定の要素の値を取得する

More than 1 year has passed since last update.


SQLServerのXMLデータ中から特定の要素の値を取得する

SQLServerにはintやvarcharなどおなじみのデータ型以外に、xmlデータ型が存在する。

xml型はその名前の通りxmlを格納するデータ型なのだが、SQL文でこのXML中の値を取得することができる。

今回は、XML中の値の取得についてのみ記載する。他にも色々とできるらしいが、今回は言及しない。

また、個人的メモの意味も強いので、説明を色々と省略している。


XQuery

XML中の特定の要素を取得する場合、XQueryを利用する。

XQueryについての説明は今回は省略する。(詳しくはググろう)


簡単な利用サンプル

記述のルールを説明してもよいが、ぱっと見でなんとなく分かると思うので例示だけしておく。

例えば、以下のようなXMLが存在したとする。この時、目的のノードを取得するXQueryは以下のものになる。

<group>

<name>石鹸</name>
<member>
<person number="1">高坂</person>
<person number="2">南</person>
<person number="3">園田</person>
</member>
</group>

<name>石鹸</name> を取得したい場合のXQuery

/group/name

<person number="2">南</person> を取得したい場合のXQuery

/group/member/person[@number="2"]


名前空間付きの利用サンプル

名前空間が指定されている場合、XQueryでもその名前空間を指定する必要がある。

<group xmlns:="sunshine">

<name>水</name>
<member>
<person number="1">高見</person>
<person number="2">渡辺</person>
<person number="3">桜内</person>
</member>
</group>

<name>水</name> を取得したい場合のXQuery

declareで名前空間を宣言し、要素を指定する必要がある。

declare namespace sun="sunshine";

/sun:group/name


SQLServer中のXMLから値を取得する

上記で記載したXQueryを利用して、データを取得する。


SQL文ではXMLの項目に対してvalueメソッド(.value(XQuery文, SQL型))を利用することで値を取得できる。

SELECT 

xmlCol1.value('(/group/name)[1]', 'nvarchar(50)') AS NAME1,
xmlCol2.value('
declare namespace sun="sunshine";
(/sun:group/name)[1]', 'nvarchar(50)') AS NAME2
FROM L_L

結果

NAME1   NAME2

----------------------
石鹸 水


最後に

SQLServer中のxmlの特定の要素を取得したい、という場合を例示した。

XQueryはきちんと利用すれば便利かもしれませんが、自分の用途では特に必要がなかったので、この程度にとどめておく。

例示が露骨にアレなのはご愛嬌。


修正履歴

SQL Serverで使用するメソッドを間違えていたので修正。

 valメソッド → valueメソッド