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.
SELECT xslt_process(article_xml, '/home/tom/standard.xsl')
WHERE article_id = 55;
SELECT xslt_process(article_xml, template_xslt)
INNER JOIN t_templates
ON article_template_id = template_id;
SELECT xslt_process('/home/book.xml', styles_xslt, 'title=Herzog')
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'.
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