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.

Installation

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 
FROM
xpath_table('article_id', 
    'article_xml',
    'articles', 
    '/article/author|/article/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).