xmltable function

はじめに

にゃーん
この記事は、PostgreSQL 10全部ぬこ Advent Calendar 2017 の10日目のエントリです。

PostgreSQLのXML対応

とは言っても滅んではくれないXML。
PostgreSQLではかなり古くから(PostgreSQL 8.2)XML型をサポートしており、それなりにXML型に対する操作を行うSQL関数も用意されていた。

xmltable関数

xmltable関数は簡単に言えば、1つのXML文書に複数のxpathを指定して、レコード群を生成する関数である。
以前からサポートされている table_to_xml()やquery_to_xml()はテーブルや検索結果からXMLを生成するものだが、このxmltable()は、いわばその逆方向の変換を行うものだ。

例えばこんなシンプルなXML文書が格納されたテーブルがあるとする。

xml=# \d simple_xml
           Table "public.simple_xml"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 data   | xml  |           |          |

xml=# TABLE simple_xml ;
                               data
------------------------------------------------------------------
 <animal id="1"><name>ぬこ</name><voice>にゃーん</voice></animal>
 <animal id="2"><name>ヒト</name><voice>うぉオン</voice></animal>
(2 rows)

PostgreSQL 9.6まで

これを表形式に変換する場合、xpath関数を使うとこんな感じになるだろう。

xml=# SELECT
  ((xpath('/animal/@id', data)::text[])[1])::int AS id,
  (xpath('/animal/name/text()', data)::text[])[1] AS name,
  (xpath('/animal/voice/text()', data)::text[])[1] AS voice
FROM simple_xml;
 id | name |  voice
----+------+----------
  1 | ぬこ | にゃーん
  2 | ヒト | うぉオン
(2 rows)

もうね、xpath関数の結果の処理の部分(xml配列をtext配列にキャストして、その最初の要素を取り出す)という部分で、けっこう嫌気がささないかい？
名前空間を使ったXMLを対象にする場合、xpath関数の第3引数に名前空間の指定が追加されるわけで、もうこんなの人間が書くSQLじゃねーよ・・・。

xmltable関数を使う

さて、PostgreSQL 10のxmltable関数を使うと、こういう処理が楽になるんだろうか。
関数の書式を見てみよう。

xmltable( [XMLNAMESPACES(namespace uri AS namespace name[, ...]), ]
          row_expression PASSING [BY REF] document_expression [BY REF]
          COLUMNS name { type [PATH column_expression] [DEFAULT default_expression] [NOT NULL | NULL]
                        | FOR ORDINALITY }
                   [, ...]
)

もう、関数の形式からは面倒くさそうな予感しかしないｗ

まあ、とりあえずさっきのシンプルなXML文書に適用してみよう。

xml=# SELECT xmltable.*
  FROM simple_xml,
  XMLTABLE('/animal'
    PASSING data
    COLUMNS id int PATH '@id',
            name text PATH 'name',
            voice text PATH 'voice'
    );
 id | name |  voice
----+------+----------
  1 | ぬこ | にゃーん
  2 | ヒト | うぉオン
(2 rows)

同じ結果が得られた。
xpath関数を使うよりは、キャスト処理を考慮しなくてもいいとはいえ、このSQL関数も十分複雑だ。正直、マニュアル見ながらじゃないと、このSQL関数を正しく書ける気がしねぇｗ

このxmltable、こんなシンプルなXMLだけでなく非常に複雑なXML対して実行することもできるが(PostgreSQL文書を見てください)、とんでもなく複雑な記述をすることになるので、今の疲れきった俺には検証する気力もわかない・・・。

参考：テーブルや検索結果からXMLを生成する

こんなシンプルなテーブルがあるとする。

xml=# \d simple_table
            Table "public.simple_table"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
 name   | text    |           |          |
 voice  | text    |           |          |

xml=# TABLE simple_table ;
 id | name |  voice
----+------+----------
  1 | ぬこ | にゃーん
  2 | ヒト | うぉオン
(2 rows)

table_to_xml()やquery_to_xml()の実行例を以下に示す。

xml=# SELECT table_to_xml('simple_table', false, false, '') ;
table_to_xml
<simple_table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<row>
  <id>1</id>
  <name>ぬこ</name>
  <voice>にゃーん</voice>
</row>

<row>
  <id>2</id>
  <name>ヒト</name>
  <voice>うぉオン</voice>
</row>

</simple_table>

(1 row)

xml=# SELECT query_to_xml('SELECT name, voice FROM simple_table', false, false, '') ;
query_to_xml
<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<row>
  <name>ぬこ</name>
  <voice>にゃーん</voice>
</row>

<row>
  <name>ヒト</name>
  <voice>うぉオン</voice>
</row>

</table>

(1 row)

これらの関数は複数のレコードを1つのXML文書(3つ目のパラメータがtrueの場合)、あるいはXML Forest(3つ目のパラメータがtrueの場合)として返却する。
XML文書に変換する関数の対象はテーブルだけではなく、スキーマやデータベース全体を対象にするSQL関数も用意されているが、ここでは割愛。

おまけ

PostgreSQL 10文書のxmltableの例、

<PREMIER_NAME>Shinzo Abe</PREMIER_NAME>

と書いてあるんだけど、政権交代したらこの例も変更するんだろうかｗ

おわりに

• PostgreSQLでは、XML文書から、1つのSQL関数を使って、表形式の情報を生成できるようになった。
• 

参考：該当するリリースノート

本エントリに関連するPostgreSQL 10リリースノートの記載です。

E.2.3.3. Queries

• Add XMLTABLE function that converts XML-formatted data into a row set (Pavel Stehule, Álvaro Herrera)