The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference by The PostgreSQL Global Development Group Paperback (6"x9"), 454 pages ISBN 9781906966041 RRP £14.95 ($19.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual Volume 1A Buy the book here! >>> only $19.95

7.14.2 Processing XML

To process values of data type xml, PostgreSQL offers the function xpath, which evaluates XPath 1.0 expressions.

xpath(xpath, xml[, nsarray])

The function xpath evaluates the XPath expression xpath against the XML value xml. It returns an array of XML values corresponding to the node set produced by the XPath expression.

The second argument must be a well formed XML document. In particular, it must have a single root node element.

The third argument of the function is an array of namespace mappings. This array should be a two-dimensional array with the length of the second axis being equal to 2 (i.e., it should be an array of arrays, each of which consists of exactly 2 elements). The first element of each array entry is the namespace name (alias), the second the namespace URI. It is not required that aliases provided in this array are the same that those being used in the XML document itself (in other words, both in the XML document and in the xpath function context, aliases are local).

Example:

SELECT xpath('/my:a/text()', '<my:a xmlns:my=
  "http://example.com">test</my:a>',
             ARRAY[ARRAY['my', 'http://example.com']]);

 xpath
--------
 {test}
(1 row)

How to deal with default (anonymous) namespaces:

SELECT xpath('//mydefns:b/text()', '<a xmlns=
  "http://example.com"><b>test</b></a>',
             ARRAY[ARRAY['mydefns', 'http://example.com']]);

 xpath
--------
 {test}
(1 row)