<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.14. XML Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-textsearch.html" title="9.13. Text Search Functions and Operators" /><link rel="next" href="functions-json.html" title="9.15. JSON Functions and Operators" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.14. XML Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-textsearch.html" title="9.13. Text Search Functions and Operators">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-json.html" title="9.15. JSON Functions and Operators">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-XML"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.14. XML Functions</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="functions-xml.html#FUNCTIONS-PRODUCING-XML">9.14.1. Producing XML Content</a></span></dt><dt><span class="sect2"><a href="functions-xml.html#FUNCTIONS-XML-PREDICATES">9.14.2. XML Predicates</a></span></dt><dt><span class="sect2"><a href="functions-xml.html#FUNCTIONS-XML-PROCESSING">9.14.3. Processing XML</a></span></dt><dt><span class="sect2"><a href="functions-xml.html#FUNCTIONS-XML-MAPPING">9.14.4. Mapping Tables to XML</a></span></dt></dl></div><p>
The functions and function-like expressions described in this
section operate on values of type <code class="type">xml</code>. Check <a class="xref" href="datatype-xml.html" title="8.13. XML Type">Section 8.13</a> for information about the <code class="type">xml</code>
type. The function-like expressions <code class="function">xmlparse</code>
and <code class="function">xmlserialize</code> for converting to and from
type <code class="type">xml</code> are not repeated here. Use of most of these
functions requires the installation to have been built
with <code class="command">configure --with-libxml</code>.
</p><div class="sect2" id="FUNCTIONS-PRODUCING-XML"><div class="titlepage"><div><div><h3 class="title">9.14.1. Producing XML Content</h3></div></div></div><p>
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.
</p><div class="sect3" id="id-1.5.8.19.3.3"><div class="titlepage"><div><div><h4 class="title">9.14.1.1. <code class="literal">xmlcomment</code></h4></div></div></div><a id="id-1.5.8.19.3.3.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlcomment</code>(<em class="replaceable"><code>text</code></em>)
</pre><p>
The function <code class="function">xmlcomment</code> creates an XML value
containing an XML comment with the specified text as content.
The text cannot contain <span class="quote">“<span class="quote"><code class="literal">--</code></span>”</span> or end with a
<span class="quote">“<span class="quote"><code class="literal">-</code></span>”</span> so that the resulting construct is a valid
XML comment. If the argument is null, the result is null.
</p><p>
Example:
</p><pre class="screen">
SELECT xmlcomment('hello');
xmlcomment
--------------
<!--hello-->
</pre><p>
</p></div><div class="sect3" id="id-1.5.8.19.3.4"><div class="titlepage"><div><div><h4 class="title">9.14.1.2. <code class="literal">xmlconcat</code></h4></div></div></div><a id="id-1.5.8.19.3.4.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlconcat</code>(<em class="replaceable"><code>xml</code></em>[<span class="optional">, ...</span>])
</pre><p>
The function <code class="function">xmlconcat</code> 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.
</p><p>
Example:
</p><pre class="screen">
SELECT xmlconcat('<abc/>', '<bar>foo</bar>');
xmlconcat
----------------------
<abc/><bar>foo</bar>
</pre><p>
</p><p>
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
<span class="quote">“<span class="quote">yes</span>”</span>, then that value is used in the result. If
all argument values have a standalone declaration value and at
least one is <span class="quote">“<span class="quote">no</span>”</span>, 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.
</p><p>
Example:
</p><pre class="screen">
SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');
xmlconcat
-----------------------------------
<?xml version="1.1"?><foo/><bar/>
</pre><p>
</p></div><div class="sect3" id="id-1.5.8.19.3.5"><div class="titlepage"><div><div><h4 class="title">9.14.1.3. <code class="literal">xmlelement</code></h4></div></div></div><a id="id-1.5.8.19.3.5.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlelement</code>(name <em class="replaceable"><code>name</code></em> [<span class="optional">, xmlattributes(<em class="replaceable"><code>value</code></em> [<span class="optional">AS <em class="replaceable"><code>attname</code></em></span>] [<span class="optional">, ... </span>])</span>] [<span class="optional"><em class="replaceable"><code>, content, ...</code></em></span>])
</pre><p>
The <code class="function">xmlelement</code> expression produces an XML
element with the given name, attributes, and content.
</p><p>
Examples:
</p><pre class="screen">
SELECT xmlelement(name foo);
xmlelement
------------
<foo/>
SELECT xmlelement(name foo, xmlattributes('xyz' as bar));
xmlelement
------------------
<foo bar="xyz"/>
SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent');
xmlelement
-------------------------------------
<foo bar="2007-01-26">content</foo>
</pre><p>
</p><p>
Element and attribute names that are not valid XML names are
escaped by replacing the offending characters by the sequence
<code class="literal">_x<em class="replaceable"><code>HHHH</code></em>_</code>, where
<em class="replaceable"><code>HHHH</code></em> is the character's Unicode
codepoint in hexadecimal notation. For example:
</p><pre class="screen">
SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b"));
xmlelement
----------------------------------
<foo_x0024_bar a_x0026_b="xyz"/>
</pre><p>
</p><p>
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:
</p><pre class="screen">
CREATE TABLE test (a xml, b xml);
SELECT xmlelement(name test, xmlattributes(a, b)) FROM test;
</pre><p>
But these are not:
</p><pre class="screen">
SELECT xmlelement(name test, xmlattributes('constant'), a, b) FROM test;
SELECT xmlelement(name test, xmlattributes(func(a, b))) FROM test;
</pre><p>
</p><p>
Element content, if specified, will be formatted according to
its data type. If the content is itself of type <code class="type">xml</code>,
complex XML documents can be constructed. For example:
</p><pre class="screen">
SELECT xmlelement(name foo, xmlattributes('xyz' as bar),
xmlelement(name abc),
xmlcomment('test'),
xmlelement(name xyz));
xmlelement
----------------------------------------------
<foo bar="xyz"><abc/><!--test--><xyz/></foo>
</pre><p>
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
<code class="type">bytea</code>) will be represented in base64 or hex
encoding, depending on the setting of the configuration parameter
<a class="xref" href="runtime-config-client.html#GUC-XMLBINARY">xmlbinary</a>. 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.
</p></div><div class="sect3" id="id-1.5.8.19.3.6"><div class="titlepage"><div><div><h4 class="title">9.14.1.4. <code class="literal">xmlforest</code></h4></div></div></div><a id="id-1.5.8.19.3.6.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlforest</code>(<em class="replaceable"><code>content</code></em> [<span class="optional">AS <em class="replaceable"><code>name</code></em></span>] [<span class="optional">, ...</span>])
</pre><p>
The <code class="function">xmlforest</code> expression produces an XML
forest (sequence) of elements using the given names and content.
</p><p>
Examples:
</p><pre class="screen">
SELECT xmlforest('abc' AS foo, 123 AS bar);
xmlforest
------------------------------
<foo>abc</foo><bar>123</bar>
SELECT xmlforest(table_name, column_name)
FROM information_schema.columns
WHERE table_schema = 'pg_catalog';
xmlforest
-------------------------------------------------------------------------------------------
<table_name>pg_authid</table_name><column_name>rolname</column_name>
<table_name>pg_authid</table_name><column_name>rolsuper</column_name>
...
</pre><p>
As seen in the second example, the element name can be omitted if
the content value is a column reference, in which case the column
name is used by default. Otherwise, a name must be specified.
</p><p>
Element names that are not valid XML names are escaped as shown
for <code class="function">xmlelement</code> above. Similarly, content
data is escaped to make valid XML content, unless it is already
of type <code class="type">xml</code>.
</p><p>
Note that XML forests are not valid XML documents if they consist
of more than one element, so it might be useful to wrap
<code class="function">xmlforest</code> expressions in
<code class="function">xmlelement</code>.
</p></div><div class="sect3" id="id-1.5.8.19.3.7"><div class="titlepage"><div><div><h4 class="title">9.14.1.5. <code class="literal">xmlpi</code></h4></div></div></div><a id="id-1.5.8.19.3.7.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlpi</code>(name <em class="replaceable"><code>target</code></em> [<span class="optional">, <em class="replaceable"><code>content</code></em></span>])
</pre><p>
The <code class="function">xmlpi</code> expression creates an XML
processing instruction. The content, if present, must not
contain the character sequence <code class="literal">?></code>.
</p><p>
Example:
</p><pre class="screen">
SELECT xmlpi(name php, 'echo "hello world";');
xmlpi
-----------------------------
<?php echo "hello world";?>
</pre><p>
</p></div><div class="sect3" id="id-1.5.8.19.3.8"><div class="titlepage"><div><div><h4 class="title">9.14.1.6. <code class="literal">xmlroot</code></h4></div></div></div><a id="id-1.5.8.19.3.8.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlroot</code>(<em class="replaceable"><code>xml</code></em>, version <em class="replaceable"><code>text</code></em> | no value [<span class="optional">, standalone yes|no|no value</span>])
</pre><p>
The <code class="function">xmlroot</code> 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.
</p><p>
</p><pre class="screen">
SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>abc</content>'),
version '1.0', standalone yes);
xmlroot
----------------------------------------
<?xml version="1.0" standalone="yes"?>
<content>abc</content>
</pre><p>
</p></div><div class="sect3" id="FUNCTIONS-XML-XMLAGG"><div class="titlepage"><div><div><h4 class="title">9.14.1.7. <code class="literal">xmlagg</code></h4></div></div></div><a id="id-1.5.8.19.3.9.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlagg</code>(<em class="replaceable"><code>xml</code></em>)
</pre><p>
The function <code class="function">xmlagg</code> is, unlike the other
functions described here, an aggregate function. It concatenates the
input values to the aggregate function call,
much like <code class="function">xmlconcat</code> does, except that concatenation
occurs across rows rather than across expressions in a single row.
See <a class="xref" href="functions-aggregate.html" title="9.20. Aggregate Functions">Section 9.20</a> for additional information
about aggregate functions.
</p><p>
Example:
</p><pre class="screen">
CREATE TABLE test (y int, x xml);
INSERT INTO test VALUES (1, '<foo>abc</foo>');
INSERT INTO test VALUES (2, '<bar/>');
SELECT xmlagg(x) FROM test;
xmlagg
----------------------
<foo>abc</foo><bar/>
</pre><p>
</p><p>
To determine the order of the concatenation, an <code class="literal">ORDER BY</code>
clause may be added to the aggregate call as described in
<a class="xref" href="sql-expressions.html#SYNTAX-AGGREGATES" title="4.2.7. Aggregate Expressions">Section 4.2.7</a>. For example:
</p><pre class="screen">
SELECT xmlagg(x ORDER BY y DESC) FROM test;
xmlagg
----------------------
<bar/><foo>abc</foo>
</pre><p>
</p><p>
The following non-standard approach used to be recommended
in previous versions, and may still be useful in specific
cases:
</p><pre class="screen">
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
xmlagg
----------------------
<bar/><foo>abc</foo>
</pre><p>
</p></div></div><div class="sect2" id="FUNCTIONS-XML-PREDICATES"><div class="titlepage"><div><div><h3 class="title">9.14.2. XML Predicates</h3></div></div></div><p>
The expressions described in this section check properties
of <code class="type">xml</code> values.
</p><div class="sect3" id="id-1.5.8.19.4.3"><div class="titlepage"><div><div><h4 class="title">9.14.2.1. <code class="literal">IS DOCUMENT</code></h4></div></div></div><a id="id-1.5.8.19.4.3.2" class="indexterm"></a><pre class="synopsis">
<em class="replaceable"><code>xml</code></em> IS DOCUMENT
</pre><p>
The expression <code class="literal">IS DOCUMENT</code> 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 <a class="xref" href="datatype-xml.html" title="8.13. XML Type">Section 8.13</a> about the difference
between documents and content fragments.
</p></div><div class="sect3" id="id-1.5.8.19.4.4"><div class="titlepage"><div><div><h4 class="title">9.14.2.2. <code class="literal">IS NOT DOCUMENT</code></h4></div></div></div><a id="id-1.5.8.19.4.4.2" class="indexterm"></a><pre class="synopsis">
<em class="replaceable"><code>xml</code></em> IS NOT DOCUMENT
</pre><p>
The expression <code class="literal">IS NOT DOCUMENT</code> 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.
</p></div><div class="sect3" id="XML-EXISTS"><div class="titlepage"><div><div><h4 class="title">9.14.2.3. <code class="literal">XMLEXISTS</code></h4></div></div></div><a id="id-1.5.8.19.4.5.2" class="indexterm"></a><pre class="synopsis">
<code class="function">XMLEXISTS</code>(<em class="replaceable"><code>text</code></em> PASSING [<span class="optional">BY REF</span>] <em class="replaceable"><code>xml</code></em> [<span class="optional">BY REF</span>])
</pre><p>
The function <code class="function">xmlexists</code> 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.)
</p><p>
Example:
</p><pre class="screen">
SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Toronto</town><town>Ottawa</town></towns>');
xmlexists
------------
t
(1 row)
</pre><p>
</p><p>
The <code class="literal">BY REF</code> clauses have no effect in
PostgreSQL, but are allowed for SQL conformance and compatibility
with other implementations. Per SQL standard, the
first <code class="literal">BY REF</code> is required, the second is
optional. Also note that the SQL standard specifies
the <code class="function">xmlexists</code> construct to take an XQuery
expression as first argument, but PostgreSQL currently only
supports XPath, which is a subset of XQuery.
</p></div><div class="sect3" id="XML-IS-WELL-FORMED"><div class="titlepage"><div><div><h4 class="title">9.14.2.4. <code class="literal">xml_is_well_formed</code></h4></div></div></div><a id="id-1.5.8.19.4.6.2" class="indexterm"></a><a id="id-1.5.8.19.4.6.3" class="indexterm"></a><a id="id-1.5.8.19.4.6.4" class="indexterm"></a><pre class="synopsis">
<code class="function">xml_is_well_formed</code>(<em class="replaceable"><code>text</code></em>)
<code class="function">xml_is_well_formed_document</code>(<em class="replaceable"><code>text</code></em>)
<code class="function">xml_is_well_formed_content</code>(<em class="replaceable"><code>text</code></em>)
</pre><p>
These functions check whether a <code class="type">text</code> string is well-formed XML,
returning a Boolean result.
<code class="function">xml_is_well_formed_document</code> checks for a well-formed
document, while <code class="function">xml_is_well_formed_content</code> checks
for well-formed content. <code class="function">xml_is_well_formed</code> does
the former if the <a class="xref" href="runtime-config-client.html#GUC-XMLOPTION">xmloption</a> configuration
parameter is set to <code class="literal">DOCUMENT</code>, or the latter if it is set to
<code class="literal">CONTENT</code>. This means that
<code class="function">xml_is_well_formed</code> is useful for seeing whether
a simple cast to type <code class="type">xml</code> will succeed, whereas the other two
functions are useful for seeing whether the corresponding variants of
<code class="function">XMLPARSE</code> will succeed.
</p><p>
Examples:
</p><pre class="screen">
SET xmloption TO DOCUMENT;
SELECT xml_is_well_formed('<>');
xml_is_well_formed
--------------------
f
(1 row)
SELECT xml_is_well_formed('<abc/>');
xml_is_well_formed
--------------------
t
(1 row)
SET xmloption TO CONTENT;
SELECT xml_is_well_formed('abc');
xml_is_well_formed
--------------------
t
(1 row)
SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>');
xml_is_well_formed_document
-----------------------------
t
(1 row)
SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>');
xml_is_well_formed_document
-----------------------------
f
(1 row)
</pre><p>
The last example shows that the checks include whether
namespaces are correctly matched.
</p></div></div><div class="sect2" id="FUNCTIONS-XML-PROCESSING"><div class="titlepage"><div><div><h3 class="title">9.14.3. Processing XML</h3></div></div></div><p>
To process values of data type <code class="type">xml</code>, PostgreSQL offers
the functions <code class="function">xpath</code> and
<code class="function">xpath_exists</code>, which evaluate XPath 1.0
expressions, and the <code class="function">XMLTABLE</code>
table function.
</p><div class="sect3" id="FUNCTIONS-XML-PROCESSING-XPATH"><div class="titlepage"><div><div><h4 class="title">9.14.3.1. <code class="literal">xpath</code></h4></div></div></div><a id="id-1.5.8.19.5.3.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xpath</code>(<em class="replaceable"><code>xpath</code></em>, <em class="replaceable"><code>xml</code></em> [<span class="optional">, <em class="replaceable"><code>nsarray</code></em></span>])
</pre><p>
The function <code class="function">xpath</code> evaluates the XPath
expression <em class="replaceable"><code>xpath</code></em> (a <code class="type">text</code> value)
against the XML value
<em class="replaceable"><code>xml</code></em>. 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.
</p><p>
The second argument must be a well formed XML document. In particular,
it must have a single root node element.
</p><p>
The optional third argument of the function is an array of namespace
mappings. This array should be a two-dimensional <code class="type">text</code> 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 <code class="function">xpath</code>
function context, aliases are <span class="emphasis"><em>local</em></span>).
</p><p>
Example:
</p><pre class="screen">
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)
</pre><p>
</p><p>
To deal with default (anonymous) namespaces, do something like this:
</p><pre class="screen">
SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a>',
ARRAY[ARRAY['mydefns', 'http://example.com']]);
xpath
--------
{test}
(1 row)
</pre><p>
</p></div><div class="sect3" id="FUNCTIONS-XML-PROCESSING-XPATH-EXISTS"><div class="titlepage"><div><div><h4 class="title">9.14.3.2. <code class="literal">xpath_exists</code></h4></div></div></div><a id="id-1.5.8.19.5.4.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xpath_exists</code>(<em class="replaceable"><code>xpath</code></em>, <em class="replaceable"><code>xml</code></em> [<span class="optional">, <em class="replaceable"><code>nsarray</code></em></span>])
</pre><p>
The function <code class="function">xpath_exists</code> is a specialized form
of the <code class="function">xpath</code> 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 <code class="literal">XMLEXISTS</code> predicate,
except that it also offers support for a namespace mapping argument.
</p><p>
Example:
</p><pre class="screen">
SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);
xpath_exists
--------------
t
(1 row)
</pre><p>
</p></div><div class="sect3" id="FUNCTIONS-XML-PROCESSING-XMLTABLE"><div class="titlepage"><div><div><h4 class="title">9.14.3.3. <code class="literal">xmltable</code></h4></div></div></div><a id="id-1.5.8.19.5.5.2" class="indexterm"></a><a id="id-1.5.8.19.5.5.3" class="indexterm"></a><pre class="synopsis">
<code class="function">xmltable</code>( [<span class="optional">XMLNAMESPACES(<em class="replaceable"><code>namespace uri</code></em> AS <em class="replaceable"><code>namespace name</code></em>[<span class="optional">, ...</span>]), </span>]
<em class="replaceable"><code>row_expression</code></em> PASSING [<span class="optional">BY REF</span>] <em class="replaceable"><code>document_expression</code></em> [<span class="optional">BY REF</span>]
COLUMNS <em class="replaceable"><code>name</code></em> { <em class="replaceable"><code>type</code></em> [<span class="optional">PATH <em class="replaceable"><code>column_expression</code></em></span>] [<span class="optional">DEFAULT <em class="replaceable"><code>default_expression</code></em></span>] [<span class="optional">NOT NULL | NULL</span>]
| FOR ORDINALITY }
[<span class="optional">, ...</span>]
)
</pre><p>
The <code class="function">xmltable</code> function produces a table based
on the given XML value, an XPath filter to extract rows, and an
optional set of column definitions.
</p><p>
The optional <code class="literal">XMLNAMESPACES</code> 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.
</p><p>
The required <em class="replaceable"><code>row_expression</code></em> 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
<code class="function">xmltable</code> transforms into output rows.
</p><p>
<em class="replaceable"><code>document_expression</code></em> provides the XML document to
operate on.
The <code class="literal">BY REF</code> 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.
</p><p>
The mandatory <code class="literal">COLUMNS</code> clause specifies the list
of columns in the output table.
If the <code class="literal">COLUMNS</code> clause is omitted, the rows in the result
set contain a single column of type <code class="literal">xml</code> containing the
data matched by <em class="replaceable"><code>row_expression</code></em>.
If <code class="literal">COLUMNS</code> 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.
</p><p>
A column marked <code class="literal">FOR ORDINALITY</code> 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 <code class="literal">FOR ORDINALITY</code>.
</p><p>
The <code class="literal">column_expression</code> for a column is an XPath expression
that is evaluated for each row, relative to the result of the
<em class="replaceable"><code>row_expression</code></em>, to find the value of the column.
If no <code class="literal">column_expression</code> is given, then the column name
is used as an implicit path.
</p><p>
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 <code class="literal">NULL</code>).
Any <code class="literal">xsi:nil</code> attributes are ignored.
</p><p>
The text body of the XML matched by the <em class="replaceable"><code>column_expression</code></em>
is used as the column value. Multiple <code class="literal">text()</code> 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 <code class="literal">text()</code> node between two non-text
elements is preserved, and that leading whitespace on a <code class="literal">text()</code>
node is not flattened.
</p><p>
If the path expression does not match for a given row but
<em class="replaceable"><code>default_expression</code></em> is specified, the value resulting
from evaluating that expression is used.
If no <code class="literal">DEFAULT</code> clause is given for the column,
the field will be set to <code class="literal">NULL</code>.
It is possible for a <em class="replaceable"><code>default_expression</code></em> 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.
</p><p>
Columns may be marked <code class="literal">NOT NULL</code>. If the
<em class="replaceable"><code>column_expression</code></em> for a <code class="literal">NOT NULL</code> column
does not match anything and there is no <code class="literal">DEFAULT</code> or the
<em class="replaceable"><code>default_expression</code></em> also evaluates to null, an error
is reported.
</p><p>
Unlike regular PostgreSQL functions, <em class="replaceable"><code>column_expression</code></em>
and <em class="replaceable"><code>default_expression</code></em> are not evaluated to a simple
value before calling the function.
<em class="replaceable"><code>column_expression</code></em> is normally evaluated
exactly once per input row, and <em class="replaceable"><code>default_expression</code></em>
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 <code class="function">xmltable</code> behaves more like a subquery than a
function call.
This means that you can usefully use volatile functions like
<code class="function">nextval</code> in <em class="replaceable"><code>default_expression</code></em>, and
<em class="replaceable"><code>column_expression</code></em> may depend on other parts of the
XML document.
</p><p>
Examples:
</p><pre class="screen">
CREATE TABLE xmldata AS SELECT
xml $$
<ROWS>
<ROW id="1">
<COUNTRY_ID>AU</COUNTRY_ID>
<COUNTRY_NAME>Australia</COUNTRY_NAME>
</ROW>
<ROW id="5">
<COUNTRY_ID>JP</COUNTRY_ID>
<COUNTRY_NAME>Japan</COUNTRY_NAME>
<PREMIER_NAME>Shinzo Abe</PREMIER_NAME>
<SIZE unit="sq_mi">145935</SIZE>
</ROW>
<ROW id="6">
<COUNTRY_ID>SG</COUNTRY_ID>
<COUNTRY_NAME>Singapore</COUNTRY_NAME>
<SIZE unit="sq_km">697</SIZE>
</ROW>
</ROWS>
$$ AS data;
SELECT xmltable.*
FROM xmldata,
XMLTABLE('//ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
ordinality FOR ORDINALITY,
"COUNTRY_NAME" text,
country_id text PATH 'COUNTRY_ID',
size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
size_other text PATH
'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') ;
id | ordinality | COUNTRY_NAME | country_id | size_sq_km | size_other | premier_name
----+------------+--------------+------------+------------+--------------+---------------
1 | 1 | Australia | AU | | | not specified
5 | 2 | Japan | JP | | 145935 sq_mi | Shinzo Abe
6 | 3 | Singapore | SG | 697 | | not specified
</pre><p>
The following example shows concatenation of multiple text() nodes,
usage of the column name as XPath filter, and the treatment of whitespace,
XML comments and processing instructions:
</p><pre class="screen">
CREATE TABLE xmlelements AS SELECT
xml $$
<root>
<element> Hello<!-- xyxxz -->2a2<?aaaaa?> <!--x--> bbb<x>xxx</x>CC </element>
</root>
$$ AS data;
SELECT xmltable.*
FROM xmlelements, XMLTABLE('/root' PASSING data COLUMNS element text);
element
----------------------
Hello2a2 bbbCC
</pre><p>
</p><p>
The following example illustrates how
the <code class="literal">XMLNAMESPACES</code> clause can be used to specify
a list of namespaces
used in the XML document as well as in the XPath expressions:
</p><pre class="screen">
WITH xmldata(data) AS (VALUES ('
<example xmlns="http://example.com/myns" xmlns:B="http://example.com/b">
<item foo="1" B:bar="2"/>
<item foo="3" B:bar="4"/>
<item foo="4" B:bar="5"/>
</example>'::xml)
)
SELECT xmltable.*
FROM XMLTABLE(XMLNAMESPACES('http://example.com/myns' AS x,
'http://example.com/b' AS "B"),
'/x:example/x:item'
PASSING (SELECT data FROM xmldata)
COLUMNS foo int PATH '@foo',
bar int PATH '@B:bar');
foo | bar
-----+-----
1 | 2
3 | 4
4 | 5
(3 rows)
</pre><p>
</p></div></div><div class="sect2" id="FUNCTIONS-XML-MAPPING"><div class="titlepage"><div><div><h3 class="title">9.14.4. Mapping Tables to XML</h3></div></div></div><a id="id-1.5.8.19.6.2" class="indexterm"></a><p>
The following functions map the contents of relational tables to
XML values. They can be thought of as XML export functionality:
</p><pre class="synopsis">
table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xml(query text, nulls boolean, tableforest boolean, targetns text)
cursor_to_xml(cursor refcursor, count int, nulls boolean,
tableforest boolean, targetns text)
</pre><p>
The return type of each function is <code class="type">xml</code>.
</p><p>
<code class="function">table_to_xml</code> maps the content of the named
table, passed as parameter <em class="parameter"><code>tbl</code></em>. The
<code class="type">regclass</code> type accepts strings identifying tables using the
usual notation, including optional schema qualifications and
double quotes. <code class="function">query_to_xml</code> executes the
query whose text is passed as parameter
<em class="parameter"><code>query</code></em> and maps the result set.
<code class="function">cursor_to_xml</code> fetches the indicated number of
rows from the cursor specified by the parameter
<em class="parameter"><code>cursor</code></em>. This variant is recommended if
large tables have to be mapped, because the result value is built
up in memory by each function.
</p><p>
If <em class="parameter"><code>tableforest</code></em> is false, then the resulting
XML document looks like this:
</p><pre class="screen">
<tablename>
<row>
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</row>
<row>
...
</row>
...
</tablename>
</pre><p>
If <em class="parameter"><code>tableforest</code></em> is true, the result is an
XML content fragment that looks like this:
</p><pre class="screen">
<tablename>
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</tablename>
<tablename>
...
</tablename>
...
</pre><p>
If no table name is available, that is, when mapping a query or a
cursor, the string <code class="literal">table</code> is used in the first
format, <code class="literal">row</code> in the second format.
</p><p>
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
<code class="function">cursor_to_xml</code> function if the result values are to be
reassembled into one document later on. The functions for
producing XML content discussed above, in particular
<code class="function">xmlelement</code>, can be used to alter the results
to taste.
</p><p>
The data values are mapped in the same way as described for the
function <code class="function">xmlelement</code> above.
</p><p>
The parameter <em class="parameter"><code>nulls</code></em> determines whether null
values should be included in the output. If true, null values in
columns are represented as:
</p><pre class="screen">
<columnname xsi:nil="true"/>
</pre><p>
where <code class="literal">xsi</code> 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.
</p><p>
The parameter <em class="parameter"><code>targetns</code></em> specifies the
desired XML namespace of the result. If no particular namespace
is wanted, an empty string should be passed.
</p><p>
The following functions return XML Schema documents describing the
mappings performed by the corresponding functions above:
</p><pre class="synopsis">
table_to_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)
cursor_to_xmlschema(cursor refcursor, nulls boolean, tableforest boolean, targetns text)
</pre><p>
It is essential that the same parameters are passed in order to
obtain matching XML data mappings and XML Schema documents.
</p><p>
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:
</p><pre class="synopsis">
table_to_xml_and_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xml_and_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)
</pre><p>
</p><p>
In addition, the following functions are available to produce
analogous mappings of entire schemas or the entire current
database:
</p><pre class="synopsis">
schema_to_xml(schema name, nulls boolean, tableforest boolean, targetns text)
schema_to_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)
schema_to_xml_and_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)
database_to_xml(nulls boolean, tableforest boolean, targetns text)
database_to_xmlschema(nulls boolean, tableforest boolean, targetns text)
database_to_xml_and_xmlschema(nulls boolean, tableforest boolean, targetns text)
</pre><p>
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.
</p><p>
The result of a schema content mapping looks like this:
</p><pre class="screen">
<schemaname>
table1-mapping
table2-mapping
...
</schemaname></pre><p>
where the format of a table mapping depends on the
<em class="parameter"><code>tableforest</code></em> parameter as explained above.
</p><p>
The result of a database content mapping looks like this:
</p><pre class="screen">
<dbname>
<schema1name>
...
</schema1name>
<schema2name>
...
</schema2name>
...
</dbname></pre><p>
where the schema mapping is as above.
</p><p>
As an example of using the output produced by these functions,
<a class="xref" href="functions-xml.html#XSLT-XML-HTML" title="Figure 9.1. XSLT Stylesheet for Converting SQL/XML Output to HTML">Figure 9.1</a> shows an XSLT stylesheet that
converts the output of
<code class="function">table_to_xml_and_xmlschema</code> 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.
</p><div class="figure" id="XSLT-XML-HTML"><p class="title"><strong>Figure 9.1. XSLT Stylesheet for Converting SQL/XML Output to HTML</strong></p><div class="figure-contents"><pre class="programlisting">
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.w3.org/1999/xhtml"
>
<xsl:output method="xml"
doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"
doctype-public="-//W3C/DTD XHTML 1.0 Strict//EN"
indent="yes"/>
<xsl:template match="/*">
<xsl:variable name="schema" select="//xsd:schema"/>
<xsl:variable name="tabletypename"
select="$schema/xsd:element[@name=name(current())]/@type"/>
<xsl:variable name="rowtypename"
select="$schema/xsd:complexType[@name=$tabletypename]/xsd:sequence/xsd:element[@name='row']/@type"/>
<html>
<head>
<title><xsl:value-of select="name(current())"/></title>
</head>
<body>
<table>
<tr>
<xsl:for-each select="$schema/xsd:complexType[@name=$rowtypename]/xsd:sequence/xsd:element/@name">
<th><xsl:value-of select="."/></th>
</xsl:for-each>
</tr>
<xsl:for-each select="row">
<tr>
<xsl:for-each select="*">
<td><xsl:value-of select="."/></td>
</xsl:for-each>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
</pre></div></div><br class="figure-break" /></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-textsearch.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-json.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.13. Text Search Functions and Operators </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 9.15. JSON Functions and Operators</td></tr></table></div></body></html>
