9.14. XML Functions

The functions and function-like expressions described in this
section operate on values of type xml.
Check Section 8.13 for information about
the xml type. The function-like
expressions xmlparse and xmlserialize for converting to and from type
xml are not repeated here. Use of most of
these functions requires the installation to have been built with
configure --with-libxml.

9.14.1. Producing XML Content

A set of functions and function-like expressions are available
for producing XML content from SQL data. As such, they are
particularly suitable for formatting query results into XML
documents for processing in client applications.

9.14.1.1. xmlcomment

xmlcomment(text)

The function xmlcomment creates an
XML value containing an XML comment with the specified text as
content. The text cannot contain “--” or end with
a “-” so that the resulting construct
is a valid XML comment. If the argument is null, the result is
null.

Example:

SELECT xmlcomment('hello');
xmlcomment
--------------
<!--hello-->

9.14.1.2. xmlconcat

xmlconcat(xml[, ...])

The function xmlconcat
concatenates a list of individual XML values to create a single
value containing an XML content fragment. Null values are omitted;
the result is only null if there are no nonnull arguments.

XML declarations, if present, are combined as follows. If all
argument values have the same XML version declaration, that version
is used in the result, else no version is used. If all argument
values have the standalone declaration value “yes”, then that value is
used in the result. If all argument values have a standalone
declaration value and at least one is “no”, then that is used
in the result. Else the result will have no standalone declaration.
If the result is determined to require a standalone declaration but
no version declaration, a version declaration with version 1.0 will
be used because XML requires an XML declaration to contain a
version declaration. Encoding declarations are ignored and removed
in all cases.

Element and attribute names that are not valid XML names are
escaped by replacing the offending characters by the sequence
_xHHHH_, where HHHH is the character's Unicode
codepoint in hexadecimal notation. For example:

An explicit attribute name need not be specified if the
attribute value is a column reference, in which case the column's
name will be used as the attribute name by default. In other cases,
the attribute must be given an explicit name. So this example is
valid:

Content of other types will be formatted into valid XML
character data. This means in particular that the characters <,
>, and & will be converted to entities. Binary data (data
type bytea) will be represented in base64
or hex encoding, depending on the setting of the configuration
parameter xmlbinary. The
particular behavior for individual data types is expected to evolve
in order to align the SQL and PostgreSQL data types with the XML
Schema specification, at which point a more precise description
will appear.

9.14.1.4. xmlforest

xmlforest(content [AS name] [, ...])

The xmlforest expression produces
an XML forest (sequence) of elements using the given names and
content.

9.14.1.6. xmlroot

xmlroot(xml, version text | no value [, standalone yes|no|no value])

The xmlroot expression alters the
properties of the root node of an XML value. If a version is
specified, it replaces the value in the root node's version
declaration; if a standalone setting is specified, it replaces the
value in the root node's standalone declaration.

9.14.1.7. xmlagg

xmlagg(xml)

The function xmlagg is, unlike the
other functions described here, an aggregate function. It
concatenates the input values to the aggregate function call, much
like xmlconcat does, except that
concatenation occurs across rows rather than across expressions in
a single row. See Section 9.20 for
additional information about aggregate functions.

The following non-standard approach used to be recommended in
previous versions, and may still be useful in specific cases:

SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
xmlagg
----------------------
<bar/><foo>abc</foo>

9.14.2. XML Predicates

The expressions described in this section check properties of
xml values.

9.14.2.1. IS
DOCUMENT

xml IS DOCUMENT

The expression IS DOCUMENT returns
true if the argument XML value is a proper XML document, false if
it is not (that is, it is a content fragment), or null if the
argument is null. See Section 8.13 about the
difference between documents and content fragments.

9.14.2.2. IS NOT
DOCUMENT

xml IS NOT DOCUMENT

The expression IS NOT DOCUMENT
returns false if the argument XML value is a proper XML document,
true if it is not (that is, it is a content fragment), or null if
the argument is null.

9.14.2.3. XMLEXISTS

XMLEXISTS(text PASSING [BY REF] xml [BY REF])

The function xmlexists returns
true if the XPath expression in the first argument returns any
nodes, and false otherwise. (If either argument is null, the result
is null.)

The BY REF clauses have no effect
in PostgreSQL, but are allowed for SQL conformance and
compatibility with other implementations. Per SQL standard, the
first BY REF is required, the second
is optional. Also note that the SQL standard specifies the
xmlexists construct to take an XQuery
expression as first argument, but PostgreSQL currently only
supports XPath, which is a subset of XQuery.

9.14.2.4. xml_is_well_formed

These functions check whether a text
string is well-formed XML, returning a Boolean result. xml_is_well_formed_document checks for a
well-formed document, while xml_is_well_formed_content checks for well-formed
content. xml_is_well_formed does the
former if the xmloption
configuration parameter is set to DOCUMENT, or the latter if it is set to
CONTENT. This means that xml_is_well_formed is useful for seeing whether a
simple cast to type xml will succeed,
whereas the other two functions are useful for seeing whether the
corresponding variants of XMLPARSE
will succeed.

9.14.3. Processing XML

9.14.3.1. xpath

xpath(xpath, xml [, nsarray])

The function xpath evaluates the
XPath expression xpath (a
text value) against the XML value
xml. It returns an array
of XML values corresponding to the node set produced by the XPath
expression. If the XPath expression returns a scalar value rather
than a node set, a single-element array is returned.

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

The optional third argument of the function is an array of
namespace mappings. This array should be a two-dimensional
text 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 be the
same as 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).

9.14.3.2. xpath_exists

xpath_exists(xpath, xml [, nsarray])

The function xpath_exists is a
specialized form of the xpath
function. Instead of returning the individual XML values that
satisfy the XPath, this function returns a Boolean indicating
whether the query was satisfied or not. This function is equivalent
to the standard XMLEXISTS predicate,
except that it also offers support for a namespace mapping
argument.

The xmltable function produces a
table based on the given XML value, an XPath filter to extract
rows, and an optional set of column definitions.

The optional XMLNAMESPACES clause
is a comma-separated list of namespaces. It specifies the XML
namespaces used in the document and their aliases. A default
namespace specification is not currently supported.

The required row_expression argument is an XPath
expression that is evaluated against the supplied XML document to
obtain an ordered sequence of XML nodes. This sequence is what
xmltable transforms into output
rows.

document_expression
provides the XML document to operate on. The BY REF clauses have no effect in PostgreSQL, but
are allowed for SQL conformance and compatibility with other
implementations. The argument must be a well-formed XML document;
fragments/forests are not accepted.

The mandatory COLUMNS clause
specifies the list of columns in the output table. If the
COLUMNS clause is omitted, the rows in
the result set contain a single column of type xml containing the data matched by row_expression. If COLUMNS is specified, each entry describes a
single column. See the syntax summary above for the format. The
column name and type are required; the path, default and
nullability clauses are optional.

A column marked FOR ORDINALITY will
be populated with row numbers matching the order in which the
output rows appeared in the original input XML document. At most
one column may be marked FOR
ORDINALITY.

The column_expression for a column
is an XPath expression that is evaluated for each row, relative to
the result of the row_expression, to find the value
of the column. If no column_expression
is given, then the column name is used as an implicit path.

If a column's XPath expression returns multiple elements, an
error is raised. If the expression matches an empty tag, the result
is an empty string (not NULL). Any
xsi:nil attributes are ignored.

The text body of the XML matched by the column_expression is used as the
column value. Multiple text() nodes
within an element are concatenated in order. Any child elements,
processing instructions, and comments are ignored, but the text
contents of child elements are concatenated to the result. Note
that the whitespace-only text() node
between two non-text elements is preserved, and that leading
whitespace on a text() node is not
flattened.

If the path expression does not match for a given row but
default_expression is
specified, the value resulting from evaluating that expression is
used. If no DEFAULT clause is given
for the column, the field will be set to NULL. It is possible for a default_expression to reference the
value of output columns that appear prior to it in the column list,
so the default of one column may be based on the value of another
column.

Columns may be marked NOT NULL. If
the column_expression for
a NOT NULL column does not match
anything and there is no DEFAULT or
the default_expression
also evaluates to null, an error is reported.

Unlike regular PostgreSQL functions, column_expression and default_expression are not
evaluated to a simple value before calling the function. column_expression is normally
evaluated exactly once per input row, and default_expression is evaluated
each time a default is needed for a field. If the expression
qualifies as stable or immutable the repeat evaluation may be
skipped. Effectively xmltable behaves
more like a subquery than a function call. This means that you can
usefully use volatile functions like nextval in default_expression, and column_expression may depend on
other parts of the XML document.

The following example illustrates how the XMLNAMESPACES clause can be used to specify the
default namespace, and a list of additional namespaces used in the
XML document as well as in the XPath expressions:

table_to_xml maps the content of
the named table, passed as parameter tbl. The regclass type accepts strings identifying tables
using the usual notation, including optional schema qualifications
and double quotes. query_to_xml
executes the query whose text is passed as parameter query and maps the result set.
cursor_to_xml fetches the indicated
number of rows from the cursor specified by the parameter
cursor. This variant is
recommended if large tables have to be mapped, because the result
value is built up in memory by each function.

If tableforest is false,
then the resulting XML document looks like this:

If no table name is available, that is, when mapping a query or
a cursor, the string table is used in
the first format, row in the second
format.

The choice between these formats is up to the user. The first
format is a proper XML document, which will be important in many
applications. The second format tends to be more useful in the
cursor_to_xml function if the result
values are to be reassembled into one document later on. The
functions for producing XML content discussed above, in particular
xmlelement, can be used to alter the
results to taste.

The data values are mapped in the same way as described for the
function xmlelement above.

The parameter nulls
determines whether null values should be included in the output. If
true, null values in columns are represented as:

<columnname xsi:nil="true"/>

where xsi is the XML namespace
prefix for XML Schema Instance. An appropriate namespace
declaration will be added to the result value. If false, columns
containing null values are simply omitted from the output.

The parameter targetns
specifies the desired XML namespace of the result. If no particular
namespace is wanted, an empty string should be passed.

The following functions return XML Schema documents describing
the mappings performed by the corresponding functions above:

It is essential that the same parameters are passed in order to
obtain matching XML data mappings and XML Schema documents.

The following functions produce XML data mappings and the
corresponding XML Schema in one document (or forest), linked
together. They can be useful where self-contained and
self-describing results are wanted:

Note that these potentially produce a lot of data, which needs
to be built up in memory. When requesting content mappings of large
schemas or databases, it might be worthwhile to consider mapping
the tables separately instead, possibly even through a cursor.

The result of a schema content mapping looks like this:

<schemaname>
table1-mapping
table2-mapping
...
</schemaname>

where the format of a table mapping depends on the tableforest parameter as explained
above.

As an example of using the output produced by these functions,
Figure 9.1 shows an XSLT stylesheet that converts the
output of table_to_xml_and_xmlschema
to an HTML document containing a tabular rendition of the table
data. In a similar manner, the results from these functions can be
converted into other XML-based formats.

Submit correction

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.