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メソッド