PostgreSQLでXML(1): まずは使ってみる
はじめに
はてさて、諸事情で最近PostgreSQL8.4のXMLサポートを使ってみたりしているのですが、 これが結構面白い！ てなわけで、ちょっとずつメモを残してみることにしました。 なお、ここではPostgreSQL依存上等！ というノリでやってますので、 別にポータビリティは全く気にしてません。 それを前提にしたメモということでご承知おきを。
FreeBSD 8.1-RELEASEで環境を作っているのですが、 普通にでports/databases/postgresql84-serverを突っ込めばXMLサポートは付いてきます。 SQLのテーブルの中にXMLという型が使えるようになっていて、 それに対してXPathでWHEREの条件を書いたり、SELECTの対象にしたりすることができます。
XML型のINSERT
例えば超単純な次のようなテーブルを考えます。
CREATE TABLE samplexml (
id SERIAL PRIMARY KEY,
xmldoc XML
);
ここのXML部分には、次のように適当なXMLを突っ込むことができるわけです。
INSERT INTO samplexml (xmldoc)
VALUES ('<document><h1>挨拶</h1><p id="hello">こんにちは</p><p>さようなら</p></document>');
XPathを用いたSELECT
とりあえず条件を入れずにSELECTしてみましょう。 <h1>というエレメントを抜いてくるには次のようにします。
pgsql=# SELECT xpath(E'//h1/text()', xmldoc) FROM samplexml;
xpath
--------
{挨拶}
(1 行)
結果が "挨拶" ではなく、"{挨拶}"になっているところに注目です。 これは、一つのXPathの検索によって、 XML文書の複数箇所が検索されてくる可能性があるので、 文字列ではなく、文字列の配列として値を返してきているのです。 たとえば<h1>ではなく<p>を検索すると次のように返ってきます。
pgsql=# SELECT xpath(E'//p/text()', xmldoc) FROM samplexml;
xpath
-------------------------
{こんにちは,さようなら}
(1 行)
まあ、どうしても最初の1個だけとか限定を付けて文字列(PostgreSQLのtext型)として返したいのであれば、次のようにtext型の配列にキャストした上でインデックス番号を付加すればOKです。
pgsql=# SELECT (xpath(E'//p/text()', xmldoc)::text[])[1] FROM samplexml; xpath ------------ こんにちは (1 行)
あるいは、array_to_string()を使って空白区切りで1つの文字列として返すようなこともできます。
pgsql=# SELECT array_to_string(xpath(E'//p/text()', xmldoc)::text[], ' ') FROM samplexml;
array_to_string
-----------------------
こんにちは さようなら
(1 行)
2つの形式の条件検索
PostgreSQLでXMLを条件検索する場合は、2つのレベルがあります。 一つはWHEREによる検索で、これは通常のSQLと同様、 XPathの値によって、SQL的な「行」を検索します。 もうひとつはXPathの条件による検索で、 これはSELECTの中で、XML中のどの部分を表示するかについて検索を行うわけです。
たとえば、上記XML中での双方の例を示します。
- 前者の例
(複数行のデータが入っていることを仮定して) id="hello" という属性が設定されたエレメントが含まれるXML文書を検索し、その文書中の<h1>エレメントの内容を表示する。
pgsql=# SELECT xpath(E'//h1/text()', xmldoc) FROM samplexml pgsql-# WHERE (xpath(E'//@id', xmldoc)::text[]) @> '{hello}'; xpath -------- {挨拶} (1 行)- 後者の例
XML文書中でid="hello"属性が設定されたというエレメントの内容を表示する
pgsql=# SELECT xpath(E'//*[@id="hello"]/text()', xmldoc) FROM samplexml; xpath -------------- {こんにちは} (1 行)
もちろん、この双方を組み合わせて利用することも可能です。
前者の例中に"@>"という演算子が含まれていますが、 これは配列を集合と捉え、左辺の集合が右辺を包含しているかどうかを示す演算子です。 同様の検索は、any()を使って次のように実行することもできるのですが、 性能上の理由でおすすめできません。この理由は後日。
pgsql=# SELECT xpath(E'//h1/text()', xmldoc) FROM samplexml
pgsql-# WHERE 'hello' = any(xpath(E'//@id', xmldoc)::text[]);
xpath
--------
{挨拶}
(1 行)
(追記) この「おすすめしない」理由は第2回に書きました。
ということで、また後日に続きます。