SQL Server - xmlデータ型のメソッド（query、value、nodes）を試す

そういえばT-SQLでxmlを扱ったことがないなーと思ったので、とりあえず基本の基本を押さえるために簡単なサンプルを書いてみましたというお話です。

まずxmlデータ型のメソッドはこれだけあります。

• xml データ型のメソッド

そのうちよく使うんじゃないかなと勝手に思うqueryメソッド、valueメソッド、nodesメソッドを使ってみます。

構文	簡単な説明	詳細
query('XQuery')	xmlからxmlを取得する	query() メソッド (xml データ型)
value('XQuery', 'SQLType')	xmlからスカラー値を取得する	value() メソッド (xml データ型)
nodes('XQuery') as Table(Column)	xmlからxmlの行セットを取得する	nodes() メソッド (xml データ型)

それぞれのメソッドの引数にはXQueryというxmlに問い合わせる文字列を渡します。この文字列は要素の位置をさすパスになることが多いのかなと思います。またXQueryのパスには省略構文があります。省略構文はよく使うのかなと思います。

• パス式 (XQuery)
• パス式での省略構文の使用

queryメソッド、valueメソッド、nodesメソッドを使ってみる

ドキュメントを読むよりクエリを見た方がわかりやすいので早速見ていきましょう。

-- xmlを変数に用意
declare @xml xml = N'
<root>
  <item id="1">abc</item>
  <item id="3">efg</item>
  <item id="2">xyz</item>
  <item id="5">lmn</item>
</root>';

-- 変数@xmlに各メソッドを使ってみる
select
    item.query('.') as Item,   -- item要素自身をxmlとして取得
    item.value('@id', 'tinyint') as Id,   -- item要素のid属性の値をtinyintとして取得
    item.value('.', 'nvarchar(3)') as Content -- item要素の中身をnvarchar(3)として取得
from
    -- root要素の子のitem要素をt(item)というテーブル(カラム)の行セットとして取得
    @xml.nodes('/root/item') as t(item);

メソッド引数の「.」がカレント要素、「@」が属性の省略構文ですね。

このクエリを実行すると次の結果になります。

cross applyでnodesメソッド

上記ではxml型の変数に対してnodesメソッドを呼び出しました。テーブルのカラムにxml型のデータがある場合はどうしたらいいかというと、from句でcross applyを使ってnodesメソッドを呼び出すといいみたいです。

-- from句のcross applyでnodesメソッドを呼び出す
select
    item.query('.') as Item,
    item.value('@id', 'tinyint') as Id,
    item.value('.', 'nvarchar(3)') as Content
from (values(@xml)) as src(xml) -- srcテーブルのxmlカラムにxmlデータがあるとして
    cross apply xml.nodes('/root/item') as t(item);

このクエリを実行すると1つめのクエリと同じ結果が得られます。

これでとりあえずとっかかりができたかなと思います。