PostgreSQLでXMLを処理してみよう！

第1回　PostgreSQLとXML

響　楽人

 

XMLが制定されてから11年経ち、データベースの世界でもXML対応が進んでいます。そのような中、PostgreSQL は一段とXML対応を本格化させています。本連載では、PostgreSQL 8.3でのXMLの取り扱い方について基本から応用まで説明してゆきます。

第1回はPostgreSQLとXMLというテーマですので、まずはPostgreSQLを使える状態にした上で、XMLの形をしたデータを格納し、そのデータをSQLによって操作可能かを試みます。

PostgreSQLは、8.3からXML型のデータの内容をXPathで操作できるようになりました。XPathとは、XMLの中の特定の箇所を指定するための仕組みです。XPathの詳細は第二回以降で取り上げますが、今回もXMLインスタンスをデータベースに格納した後、この機能を使ったSELECTを行っています。

準備（インストール）

この記事を書くにあたっては以下の環境でPostgreSQL8.3をインストールし、XMLデータの処理を実行しました。

  • PostgreSQLバージョン：PostgreSQL 8.3.6（http://www.postgresql.jp/PostgreSQL）
  • OSプラットフォーム：WindowsXP
  • インストール方法：Windows版インストーラ（ZIPファイルを展開したフォルダにあるPostgres-8.3.msi）をダブルクリックし、画面の指示に従ってインストールします。

※インストール時の注意

「データベースクラスタの初期化」画面で入力するスーパーユーザ名（既定ではpostgres）と、そのパスワードはデータベースの作成と接続に必要です。インストールの仕方は、記事「PostgreSQL上にMediaWiki環境を構築」も参考にして下さい。

PostgreSQLにXML型のデータを格納し、XPathを使ってXMLを操作する方法

データベースを作成する

コマンドプロンプトを起動し、以下のように入力します。

※Windowsの場合、「スタート」メニューの「PstgreSQL8.3」の下にある「コマンドプロンプト」を起動すると以下のように "C:\Program Files\PostgreSQL\8.3\bin" ディレクトリでコマンドプロンプトが開きます。それ以外のディレクトリでコマンドプロンプトを開いてPostgreSQLを使う場合には、"C:\Program Files\PostgreSQL\8.3\bin" にパスが通るように設定してください。

C:\Program Files\PostgreSQL\8.3\bin>createdb -U postgres testdb

パスワードを聞かれるので、インストール時のスーパーユーザのパスワードを入力します（入力したパスワードは表示されません）。

Password:

psqlを使ってデータベースへ接続する

対話式にSQLコマンドの入力、編集、実行を行うことができるpsqlを使って説明を行います。

まず、データベースへの接続を行います。

C:\Program Files\PostgreSQL\8.3\bin>psql testdb postgres

ここでパスワードを聞かれるので、先ほどと同じパスワードを入力します。

Password for user postgres:

パスワードが正しければ、以下のようなメッセージが表示されます。

Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
testdb=#

最後の一行「testdb=#」の表記は、スーパーユーザとしてデータベースに接続したことを表すものです。「testdb=>」と表記されることもあります。

テーブルを作成する

PstgreSQL8.3ではXMLデータを格納するための型として「XML型」が用意されています。そこで、XML型の列「books」を持つテーブル「sample」を作成します。

以下のように入力します。

testdb=# CREATE TABLE sample(books xml);

きちんとテーブルが作成されると、以下のような表示が出ます。

CREATE TABLE

XMLデータを格納する

「sample」テーブルの「books」列に、xmlデータ「<title>XPATH AND PostgreSQL</title>」を格納します。

testdb=# INSERT INTO sample(books)
         VALUES ('<title>XPATH AND PostgreSQL</title>');

きちんとXMLデータが格納されると以下のような表示になります。

INSERT 0 1

XMLデータがたった一つでは寂しいので、もう一行追加します。

testdb=# INSERT INTO sample(books)
         VALUES ('<title>PostgreSQL new feature</title>');

XMLデータをSQLのSELECT文で取り出す

「books」列全体を「sample」テーブルから取り出します。

testdb=# SELECT books FROM sample;

結果は以下の通りです。

                 books
---------------------------------------
 <title>XPATH AND PostgreSQL</title>
 <title>PostgreSQL new feature</title>
(2 rows)

さきほど格納した２つのXMLインスタンスが表示されました。

では、次にもう少しXMLっぽいデータを使ってXPathを試してみましょう。

XMLデータをXPathを使って取り出す

新しくテーブルを作成します。

testdb=# CREATE TABLE t_books(books xml);

次に本のタイトル、著者、出版社の情報を含む実際的なXMLデータをINSERTを使って１行ずつ格納していきます。赤字の部分がXMLインスタンスです。（コマンド・プロンプトで行頭がtestdbとなっているとき、キーボードからEnterを入力すると画面上で改行されるだけで、SQLコマンドは実行されません。SQLコマンドを実行するにはセミコロン「；」を入力してからEnterを押します。）

testdb=# INSERT INTO t_books(books) VALUES('
testdb'#  <book>
testdb'#  <title>エックスエムエルとポスグレ</title>
testdb'#   <author>いくた やすし</author><author>まえだ たかし</author>
testdb'#   <publisher>ＳＱＬ出版</publisher>
testdb'#  </book>
testdb'# ');
testdb=# INSERT INTO t_books(books) VALUES('
testdb'# <book>
testdb'#    <title>ポスグレとエックスパス</title>
testdb'#    <author>いくた やすし</author>
testdb'#    <publisher>亀の本屋</publisher>
testdb'# </book>
testdb'# ');
testdb=# INSERT INTO t_books(books) VALUES('
testdb'#  <book>
testdb'#    <title>ポスグレとエックスエスエルティー</title>
testdb'#    <author>まえだ たかし</author>
testdb'#    <publisher>象ブックス</publisher>
testdb'#  </book>
testdb'# ');

では、「books」列の中の要素内容が「いくた やすし」である<title>要素のみを「t_books」テーブルから取り出してみましょう。これにはXMLデータの一部分を指定するXPathの機能を使います。'/book[author/text()="いくた やすし"]/title'となっているところがXPathの書き方で、条件を指定してtitle要素を選択しています。（※XPathについては次回以降詳しく説明します。今回は、XML内のデータを部分的に指定できる機能があるということだけ覚えておいてください。）

testdb=# SELECT xpath('/book[author/text()="いくた やすし"]/title', books)
           FROM t_books;

結果は以下の通りです。

                    xpath
---------------------------------------------
 {<title>エックスエムエルとポスグレ</title>}
 {<title>ポスグレとエックスパス</title>}
 {}
(3 rows)

要素内容が「いくた やすし」である<title>要素を取り出すことができました。ちなみに、この方法では該当しない行も空白 (空の配列) で出力されてしまいます（最後の１行がそれです）。

テーブルを削除する

最後に、最初にサンプルとして作成し不要となったテーブル「sample」を削除しておきます。

testdb=# DROP TABLE sample;

削除されました。

DROP TABLE

まとめ

今回はXMLに対応したPostgreSQL 8.3の機能を紹介しました。PostgreSQL 8.3ではXML型が用意されているので、XMLデータを格納するためのカラムを含むテーブルを作成できること、XMLデータの格納の仕方、XMLデータの取り出し方などがお分かりいただけたと思います。次回は格納したXMLデータを取り出す際、XMLデータの一部分を指定して様々な条件を付けることのできるXPathの機能について説明します。

（2009年3月24日公開）

ブックナビゲーション