60. PostgreSQL and XML updated

The XML functions for PostgreSQL (introduced here) have been updated and, as of version 8, ship with the standard distribution of PostgreSQL. The package now includes support for in-database XSLT transformations and a flexible new xpath_table function which evaluates a set of XPath queries and returns the results as a virtual table.


To build the functions, follow the instructions in contrib/xml2. You'll need recent versions of the libxml2 and libxslt libraries somewhere your compiler can see them. We've compiled and used the functions for PostgreSQL 7.4+ under Linux (Red Hat and Debian), Windows and OS X; here's a Win32 binary with statically linked libxml2 and libxslt libraries. If you get stuck with the installation, do get in touch.

XSLT support

An example:

SELECT xslt_process(article_xml, '/home/tom/standard.xsl')

    AS rendered_article 

FROM t_articles

WHERE article_id = 55;

A second:

SELECT xslt_process(article_xml, template_xslt)

    AS rendered_article

FROM t_articles 

INNER JOIN t_templates 

    ON article_template_id = template_id;

A third:

SELECT xslt_process('/home/book.xml', styles_xslt, 'title=Herzog')

    AS rendered_book

FROM t_books 

INNER JOIN t_styles

    ON book_style_id = style_id;

These demonstrate the function signature:

xslt_process(document, stylesheet [,paramlist]) RETURNS text

Document and stylesheet can be URLs or XML strings, which are identified by a leading left angle bracket. The optional paramlist argument is a list of parameter assignments to be used in the transformation, specified in the form 'a=1,b=2'.

XPath table

This makes use of the table functions which were introduced in PostgreSQL 7.3. It's an attempted solder for the impedance mismatch of many-elements-per-document XML and one-unit-per-row SQL.

Here's an example, modified from John's docs:

SELECT article_id, author, title 






    'date_entered > ''2003-01-01'' ') 

AS t_virtual_table(article_id integer, author text, title text);

The function signature is

xpath_table(key, document, relation, xpaths, criteria)

where key is the column to be used as the primary key of the generated table; document is the name of the column containing a blob of XML; relation specifies the table which holds the column with the XML blob; xpaths are multiple, pipe-separated XPath expressions; criteria is the contents of the WHERE clause which constrains the generation of the virtual table (use 1=1 to return all rows).

© Tom Dyson 2007

Home | About | Contact | Search | Archives