xml2 module provides XPath
querying and XSLT functionality.
From PostgreSQL 8.3 on, there
is XML-related functionality based on the SQL/XML standard in the
core server. That functionality covers XML syntax checking and
XPath queries, which is what this module does, and more, but the
API is not at all compatible. It is planned that this module will
be removed in a future version of PostgreSQL in favor of the newer
standard API, so you are encouraged to try converting your
applications. If you find that some of the functionality of this
module is not available in an adequate form with the newer API,
please explain your issue to
so that the deficiency can be addressed.
Table F.35 shows the
functions provided by this module. These functions provide
straightforward XML parsing and XPath queries. All arguments are of
text, so for brevity that is not
Table F.35. Functions
This parses the document text in its parameter and returns true
if the document is well-formed XML. (Note: this is an alias for the
standard PostgreSQL function
These functions evaluate the XPath query on the supplied document, and cast the result to the specified type.
This evaluates query on document and wraps the result in XML tags. If the result is multivalued, the output will look like:
<toptag> <itemtag>Value 1 which could be an XML fragment</itemtag> <itemtag>Value 2....</itemtag> </toptag>
This function returns multiple values separated by the specified
separator, for example
||This is a wrapper for the above function that uses
xpath_table(text key, text document, text relation, text xpaths, text criteria) returns setof record
xpath_table is a table function
that evaluates a set of XPath queries on each of a set of documents
and returns the results as a table. The primary key field from the
original document table is returned as the first column of the
result so that the result set can readily be used in joins. The
parameters are described in Table F.36.
the name of the “key” field — this is just a field to be used as the first column of the output table, i.e., it identifies the record from which each output row came (see note below about multiple values)
the name of the field containing the XML document
the name of the table or view containing the documents
one or more XPath expressions, separated by
the contents of the WHERE clause. This cannot be omitted, so use
These parameters (except the XPath strings) are just substituted into a plain SQL SELECT statement, so you have some flexibility — the statement is
SELECT <key>, <document> FROM
<relation> WHERE <criteria>
so those parameters can be anything valid in those particular locations. The result from this SELECT needs to return exactly two columns (which it will unless you try to list multiple fields for key or document). Beware that this simplistic approach requires that you validate any user-supplied values to avoid SQL injection attacks.
The function has to be used in a
FROM expression, with an
AS clause to specify the output columns; for
SELECT * FROM xpath_table('article_id', 'article_xml', 'articles', '/article/author|/article/pages|/article/title', 'date_entered > ''2003-01-01'' ') AS t(article_id integer, author text, page_count integer, title text);
AS clause defines the names and
types of the columns in the output table. The first is the
and the rest correspond to the XPath queries. If there are more
XPath queries than result columns, the extra queries will be
ignored. If there are more result columns than XPath queries, the
extra columns will be NULL.
Notice that this example defines the
page_count result column as an integer. The
function deals internally with string representations, so when you
say you want an integer in the output, it will take the string
representation of the XPath result and use PostgreSQL input
functions to transform it into an integer (or whatever type the
AS clause requests). An error will result
if it can't do this — for example if the result is empty — so you
may wish to just stick to
text as the
column type if you think your data has any problems.
doesn't necessarily have to be just
* — it can reference the output columns by name or join them
to other tables. The function produces a virtual table with which
you can perform any operation you wish (e.g. aggregation, joining,
sorting etc). So we could also have:
SELECT t.title, p.fullname, p.email FROM xpath_table('article_id', 'article_xml', 'articles', '/article/title|/article/author/@id', 'xpath_string(article_xml,''/article/@date'') > ''2003-03-20'' ') AS t(article_id integer, title text, author_id integer), tblPeopleInfo AS p WHERE t.author_id = p.person_id;
as a more complicated example. Of course, you could wrap all of this in a view for convenience.
xpath_table function assumes
that the results of each XPath query might be multivalued, so the
number of rows returned by the function may not be the same as the
number of input documents. The first row returned contains the
first result from each query, the second row the second result from
each query. If one of the queries has fewer values than the others,
null values will be returned instead.
In some cases, a user will know that a given XPath query will return only a single result (perhaps a unique document identifier) — if used alongside an XPath query returning multiple results, the single-valued result will appear only on the first row of the result. The solution to this is to use the key field as part of a join against a simpler XPath query. As an example:
CREATE TABLE test ( id int PRIMARY KEY, xml text ); INSERT INTO test VALUES (1, '<doc num="C1"> <line num="L1"><a>1</a><b>2</b><c>3</c></line> <line num="L2"><a>11</a><b>22</b><c>33</c></line> </doc>'); INSERT INTO test VALUES (2, '<doc num="C2"> <line num="L1"><a>111</a><b>222</b><c>333</c></line> <line num="L2"><a>111</a><b>222</b><c>333</c></line> </doc>'); SELECT * FROM xpath_table('id','xml','test', '/doc/@num|/doc/line/@num|/doc/line/a|/doc/line/b|/doc/line/c', 'true') AS t(id int, doc_num varchar(10), line_num varchar(10), val1 int, val2 int, val3 int) WHERE id = 1 ORDER BY doc_num, line_num id | doc_num | line_num | val1 | val2 | val3 ----+---------+----------+------+------+------ 1 | C1 | L1 | 1 | 2 | 3 1 | | L2 | 11 | 22 | 33
doc_num on every line, the
solution is to use two invocations of
xpath_table and join the results:
SELECT t.*,i.doc_num FROM xpath_table('id', 'xml', 'test', '/doc/line/@num|/doc/line/a|/doc/line/b|/doc/line/c', 'true') AS t(id int, line_num varchar(10), val1 int, val2 int, val3 int), xpath_table('id', 'xml', 'test', '/doc/@num', 'true') AS i(id int, doc_num varchar(10)) WHERE i.id=t.id AND i.id=1 ORDER BY doc_num, line_num; id | line_num | val1 | val2 | val3 | doc_num ----+----------+------+------+------+--------- 1 | L1 | 1 | 2 | 3 | C1 1 | L2 | 11 | 22 | 33 | C1 (2 rows)
The following functions are available if libxslt is installed:
xslt_process(text document, text stylesheet, text paramlist) returns text
This function applies the XSL stylesheet to the document and
returns the transformed result. The
paramlist is a list of parameter assignments to be
used in the transformation, specified in the form
a=1,b=2. Note that the parameter parsing is very
simple-minded: parameter values cannot contain commas!
There is also a two-parameter version of
xslt_process which does not pass any parameters
to the transformation.
Development of this module was sponsored by Torchbox Ltd. (www.torchbox.com). It has the same BSD license as PostgreSQL.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.