path: root/doc/src/sgml/func/func-xml.sgml

 <sect1 id="functions-xml">

  <title>XML Functions</title>

  <indexterm>
   <primary>XML Functions</primary>
  </indexterm>

  <para>
   The functions and function-like expressions described in this
   section operate on values of type <type>xml</type>.  See <xref
   linkend="datatype-xml"/> for information about the <type>xml</type>
   type.  The function-like expressions <function>xmlparse</function>
   and <function>xmlserialize</function> for converting to and from
   type <type>xml</type> are documented there, not in this section.
  </para>

  <para>
   Use of most of these functions
   requires <productname>PostgreSQL</productname> to have been built
   with <command>configure --with-libxml</command>.
  </para>

  <sect2 id="functions-producing-xml">
   <title>Producing XML Content</title>

   <para>
    A set of functions and function-like expressions is 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.
   </para>

  <sect3 id="functions-producing-xml-xmltext">
    <title><literal>xmltext</literal></title>

    <indexterm>
     <primary>xmltext</primary>
    </indexterm>

<synopsis>
<function>xmltext</function> ( <type>text</type> ) <returnvalue>xml</returnvalue>
</synopsis>

    <para>
     The function <function>xmltext</function> returns an XML value with a single
     text node containing the input argument as its content. Predefined entities
     like ampersand (<literal><![CDATA[&]]></literal>), left and right angle brackets
     (<literal><![CDATA[< >]]></literal>), and quotation marks (<literal><![CDATA[""]]></literal>)
     are escaped.
    </para>

    <para>
     Example:
<screen><![CDATA[
SELECT xmltext('< foo & bar >');
         xmltext
-------------------------
 &lt; foo &amp; bar &gt;
]]></screen>
    </para>
   </sect3>

   <sect3 id="functions-producing-xml-xmlcomment">
    <title><literal>xmlcomment</literal></title>

    <indexterm>
     <primary>xmlcomment</primary>
    </indexterm>

<synopsis>
<function>xmlcomment</function> ( <type>text</type> ) <returnvalue>xml</returnvalue>
</synopsis>

    <para>
     The function <function>xmlcomment</function> creates an XML value
     containing an XML comment with the specified text as content.
     The text cannot contain <quote><literal>--</literal></quote> or end with a
     <quote><literal>-</literal></quote>, otherwise the resulting construct
     would not be a valid XML comment.
     If the argument is null, the result is null.
    </para>

    <para>
     Example:
<screen><![CDATA[
SELECT xmlcomment('hello');

  xmlcomment
--------------
 <!--hello-->
]]></screen>
    </para>
   </sect3>

   <sect3 id="functions-producing-xml-xmlconcat">
    <title><literal>xmlconcat</literal></title>

    <indexterm>
     <primary>xmlconcat</primary>
    </indexterm>

<synopsis>
<function>xmlconcat</function> ( <type>xml</type> <optional>, ...</optional> ) <returnvalue>xml</returnvalue>
</synopsis>

    <para>
     The function <function>xmlconcat</function> 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.
    </para>

    <para>
     Example:
<screen><![CDATA[
SELECT xmlconcat('<abc/>', '<bar>foo</bar>');

      xmlconcat
----------------------
 <abc/><bar>foo</bar>
]]></screen>
    </para>

    <para>
     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
     <quote>yes</quote>, then that value is used in the result.  If
     all argument values have a standalone declaration value and at
     least one is <quote>no</quote>, 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.
    </para>

    <para>
     Example:
<screen><![CDATA[
SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');

             xmlconcat
-----------------------------------
 <?xml version="1.1"?><foo/><bar/>
]]></screen>
    </para>
   </sect3>

   <sect3 id="functions-producing-xml-xmlelement">
    <title><literal>xmlelement</literal></title>

   <indexterm>
    <primary>xmlelement</primary>
   </indexterm>

<synopsis>
<function>xmlelement</function> ( <literal>NAME</literal> <replaceable>name</replaceable> <optional>, <literal>XMLATTRIBUTES</literal> ( <replaceable>attvalue</replaceable> <optional> <literal>AS</literal> <replaceable>attname</replaceable> </optional> <optional>, ...</optional> ) </optional> <optional>, <replaceable>content</replaceable> <optional>, ...</optional></optional> ) <returnvalue>xml</returnvalue>
</synopsis>

    <para>
     The <function>xmlelement</function> expression produces an XML
     element with the given name, attributes, and content.
     The <replaceable>name</replaceable>
     and <replaceable>attname</replaceable> items shown in the syntax are
     simple identifiers, not values.  The <replaceable>attvalue</replaceable>
     and <replaceable>content</replaceable> items are expressions, which can
     yield any <productname>PostgreSQL</productname> data type.  The
     argument(s) within <literal>XMLATTRIBUTES</literal> generate attributes
     of the XML element; the <replaceable>content</replaceable> value(s) are
     concatenated to form its content.
    </para>

    <para>
     Examples:
<screen><![CDATA[
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>
]]></screen>
    </para>

    <para>
     Element and attribute names that are not valid XML names are
     escaped by replacing the offending characters by the sequence
     <literal>_x<replaceable>HHHH</replaceable>_</literal>, where
     <replaceable>HHHH</replaceable> is the character's Unicode
     codepoint in hexadecimal notation.  For example:
<screen><![CDATA[
SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b"));

            xmlelement
----------------------------------
 <foo_x0024_bar a_x0026_b="xyz"/>
]]></screen>
    </para>

    <para>
     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:
<screen>
CREATE TABLE test (a xml, b xml);
SELECT xmlelement(name test, xmlattributes(a, b)) FROM test;
</screen>
     But these are not:
<screen>
SELECT xmlelement(name test, xmlattributes('constant'), a, b) FROM test;
SELECT xmlelement(name test, xmlattributes(func(a, b))) FROM test;
</screen>
    </para>

    <para>
     Element content, if specified, will be formatted according to
     its data type.  If the content is itself of type <type>xml</type>,
     complex XML documents can be constructed.  For example:
<screen><![CDATA[
SELECT xmlelement(name foo, xmlattributes('xyz' as bar),
                            xmlelement(name abc),
                            xmlcomment('test'),
                            xmlelement(name xyz));

                  xmlelement
----------------------------------------------
 <foo bar="xyz"><abc/><!--test--><xyz/></foo>
]]></screen>

     Content of other types will be formatted into valid XML character
     data.  This means in particular that the characters &lt;, &gt;,
     and &amp; will be converted to entities.  Binary data (data type
     <type>bytea</type>) will be represented in base64 or hex
     encoding, depending on the setting of the configuration parameter
     <xref linkend="guc-xmlbinary"/>.  The particular behavior for
     individual data types is expected to evolve in order to align the
     PostgreSQL mappings with those specified in SQL:2006 and later,
     as discussed in <xref linkend="functions-xml-limits-casts"/>.
    </para>
   </sect3>

   <sect3 id="functions-producing-xml-xmlforest">
    <title><literal>xmlforest</literal></title>

   <indexterm>
    <primary>xmlforest</primary>
   </indexterm>

<synopsis>
<function>xmlforest</function> ( <replaceable>content</replaceable> <optional> <literal>AS</literal> <replaceable>name</replaceable> </optional> <optional>, ...</optional> ) <returnvalue>xml</returnvalue>
</synopsis>

    <para>
     The <function>xmlforest</function> expression produces an XML
     forest (sequence) of elements using the given names and content.
     As for <function>xmlelement</function>,
     each <replaceable>name</replaceable> must be a simple identifier, while
     the <replaceable>content</replaceable> expressions can have any data
     type.
    </para>

    <para>
     Examples:
<screen>
SELECT xmlforest('abc' AS foo, 123 AS bar);

          xmlforest
------------------------------
 &lt;foo&gt;abc&lt;/foo&gt;&lt;bar&gt;123&lt;/bar&gt;


SELECT xmlforest(table_name, column_name)
FROM information_schema.columns
WHERE table_schema = 'pg_catalog';

                                xmlforest
------------------------------------&zwsp;-----------------------------------
 &lt;table_name&gt;pg_authid&lt;/table_name&gt;&zwsp;&lt;column_name&gt;rolname&lt;/column_name&gt;
 &lt;table_name&gt;pg_authid&lt;/table_name&gt;&zwsp;&lt;column_name&gt;rolsuper&lt;/column_name&gt;
 ...
</screen>

     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.
    </para>

    <para>
     Element names that are not valid XML names are escaped as shown
     for <function>xmlelement</function> above.  Similarly, content
     data is escaped to make valid XML content, unless it is already
     of type <type>xml</type>.
    </para>

    <para>
     Note that XML forests are not valid XML documents if they consist
     of more than one element, so it might be useful to wrap
     <function>xmlforest</function> expressions in
     <function>xmlelement</function>.
    </para>
   </sect3>

   <sect3 id="functions-producing-xml-xmlpi">
    <title><literal>xmlpi</literal></title>

   <indexterm>
    <primary>xmlpi</primary>
   </indexterm>

<synopsis>
<function>xmlpi</function> ( <literal>NAME</literal> <replaceable>name</replaceable> <optional>, <replaceable>content</replaceable> </optional> ) <returnvalue>xml</returnvalue>
</synopsis>

    <para>
     The <function>xmlpi</function> expression creates an XML
     processing instruction.
     As for <function>xmlelement</function>,
     the <replaceable>name</replaceable> must be a simple identifier, while
     the <replaceable>content</replaceable> expression can have any data type.
     The <replaceable>content</replaceable>, if present, must not contain the
     character sequence <literal>?&gt;</literal>.
    </para>

    <para>
     Example:
<screen><![CDATA[
SELECT xmlpi(name php, 'echo "hello world";');

            xmlpi
-----------------------------
 <?php echo "hello world";?>
]]></screen>
    </para>
   </sect3>

   <sect3 id="functions-producing-xml-xmlroot">
    <title><literal>xmlroot</literal></title>

   <indexterm>
    <primary>xmlroot</primary>
   </indexterm>

<synopsis>
<function>xmlroot</function> ( <type>xml</type>, <literal>VERSION</literal> {<type>text</type>|<literal>NO VALUE</literal>} <optional>, <literal>STANDALONE</literal> {<literal>YES</literal>|<literal>NO</literal>|<literal>NO VALUE</literal>} </optional> ) <returnvalue>xml</returnvalue>
</synopsis>

    <para>
     The <function>xmlroot</function> 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.
    </para>

    <para>
<screen><![CDATA[
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>
]]></screen>
    </para>
   </sect3>

   <sect3 id="functions-xml-xmlagg">
    <title><literal>xmlagg</literal></title>

    <indexterm>
     <primary>xmlagg</primary>
    </indexterm>

<synopsis>
<function>xmlagg</function> ( <type>xml</type> ) <returnvalue>xml</returnvalue>
</synopsis>

    <para>
     The function <function>xmlagg</function> is, unlike the other
     functions described here, an aggregate function.  It concatenates the
     input values to the aggregate function call,
     much like <function>xmlconcat</function> does, except that concatenation
     occurs across rows rather than across expressions in a single row.
     See <xref linkend="functions-aggregate"/> for additional information
     about aggregate functions.
    </para>

    <para>
     Example:
<screen><![CDATA[
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/>
]]></screen>
    </para>

    <para>
     To determine the order of the concatenation, an <literal>ORDER BY</literal>
     clause may be added to the aggregate call as described in
     <xref linkend="syntax-aggregates"/>. For example:

<screen><![CDATA[
SELECT xmlagg(x ORDER BY y DESC) FROM test;
        xmlagg
----------------------
 <bar/><foo>abc</foo>
]]></screen>
    </para>

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

<screen><![CDATA[
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
        xmlagg
----------------------
 <bar/><foo>abc</foo>
]]></screen>
    </para>
   </sect3>
   </sect2>

   <sect2 id="functions-xml-predicates">
    <title>XML Predicates</title>

    <para>
     The expressions described in this section check properties
     of <type>xml</type> values.
    </para>

   <sect3 id="functions-producing-xml-is-document">
    <title><literal>IS DOCUMENT</literal></title>

    <indexterm>
     <primary>IS DOCUMENT</primary>
    </indexterm>

<synopsis>
<type>xml</type> <literal>IS DOCUMENT</literal> <returnvalue>boolean</returnvalue>
</synopsis>

    <para>
     The expression <literal>IS DOCUMENT</literal> 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 <xref linkend="datatype-xml"/> about the difference
     between documents and content fragments.
    </para>
   </sect3>

   <sect3 id="functions-producing-xml-is-not-document">
    <title><literal>IS NOT DOCUMENT</literal></title>

    <indexterm>
     <primary>IS NOT DOCUMENT</primary>
    </indexterm>

<synopsis>
<type>xml</type> <literal>IS NOT DOCUMENT</literal> <returnvalue>boolean</returnvalue>
</synopsis>

    <para>
     The expression <literal>IS NOT DOCUMENT</literal> 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.
    </para>
   </sect3>

   <sect3 id="xml-exists">
    <title><literal>XMLEXISTS</literal></title>

    <indexterm>
     <primary>XMLEXISTS</primary>
    </indexterm>

<synopsis>
<function>XMLEXISTS</function> ( <type>text</type> <literal>PASSING</literal> <optional><literal>BY</literal> {<literal>REF</literal>|<literal>VALUE</literal>}</optional> <type>xml</type> <optional><literal>BY</literal> {<literal>REF</literal>|<literal>VALUE</literal>}</optional> ) <returnvalue>boolean</returnvalue>
</synopsis>

    <para>
     The function <function>xmlexists</function> evaluates an XPath 1.0
     expression (the first argument), with the passed XML value as its context
     item.  The function returns false if the result of that evaluation
     yields an empty node-set, true if it yields any other value.  The
     function returns null if any argument is null.  A nonnull value
     passed as the context item must be an XML document, not a content
     fragment or any non-XML value.
    </para>

    <para>
     Example:
     <screen><![CDATA[
SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY VALUE '<towns><town>Toronto</town><town>Ottawa</town></towns>');

 xmlexists
------------
 t
(1 row)
]]></screen>
    </para>

    <para>
     The <literal>BY REF</literal> and <literal>BY VALUE</literal> clauses
     are accepted in <productname>PostgreSQL</productname>, but are ignored,
     as discussed in <xref linkend="functions-xml-limits-postgresql"/>.
    </para>

    <para>
     In the SQL standard, the <function>xmlexists</function> function
     evaluates an expression in the XML Query language,
     but <productname>PostgreSQL</productname> allows only an XPath 1.0
     expression, as discussed in
     <xref linkend="functions-xml-limits-xpath1"/>.
    </para>
   </sect3>

   <sect3 id="xml-is-well-formed">
    <title><literal>xml_is_well_formed</literal></title>

    <indexterm>
     <primary>xml_is_well_formed</primary>
    </indexterm>

    <indexterm>
     <primary>xml_is_well_formed_document</primary>
    </indexterm>

    <indexterm>
     <primary>xml_is_well_formed_content</primary>
    </indexterm>

<synopsis>
<function>xml_is_well_formed</function> ( <type>text</type> ) <returnvalue>boolean</returnvalue>
<function>xml_is_well_formed_document</function> ( <type>text</type> ) <returnvalue>boolean</returnvalue>
<function>xml_is_well_formed_content</function> ( <type>text</type> ) <returnvalue>boolean</returnvalue>
</synopsis>

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

    <para>
     Examples:

<screen><![CDATA[
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)
]]></screen>

     The last example shows that the checks include whether
     namespaces are correctly matched.
    </para>
   </sect3>
  </sect2>

  <sect2 id="functions-xml-processing">
   <title>Processing XML</title>

   <para>
    To process values of data type <type>xml</type>, PostgreSQL offers
    the functions <function>xpath</function> and
    <function>xpath_exists</function>, which evaluate XPath 1.0
    expressions, and the <function>XMLTABLE</function>
    table function.
   </para>

   <sect3 id="functions-xml-processing-xpath">
    <title><literal>xpath</literal></title>

    <indexterm>
     <primary>XPath</primary>
    </indexterm>

<synopsis>
<function>xpath</function> ( <parameter>xpath</parameter> <type>text</type>, <parameter>xml</parameter> <type>xml</type> <optional>, <parameter>nsarray</parameter> <type>text[]</type> </optional> ) <returnvalue>xml[]</returnvalue>
</synopsis>

    <para>
     The function <function>xpath</function> evaluates the XPath 1.0
     expression <parameter>xpath</parameter> (given as text)
     against the XML value
     <parameter>xml</parameter>.  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.
    </para>

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

    <para>
     The optional third argument of the function is an array of namespace
     mappings.  This array should be a two-dimensional <type>text</type> 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 <function>xpath</function>
     function context, aliases are <emphasis>local</emphasis>).
    </para>

    <para>
     Example:
<screen><![CDATA[
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)
]]></screen>
    </para>

    <para>
     To deal with default (anonymous) namespaces, do something like this:
<screen><![CDATA[
SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a>',
             ARRAY[ARRAY['mydefns', 'http://example.com']]);

 xpath
--------
 {test}
(1 row)
]]></screen>
    </para>
   </sect3>

   <sect3 id="functions-xml-processing-xpath-exists">
    <title><literal>xpath_exists</literal></title>

    <indexterm>
     <primary>xpath_exists</primary>
    </indexterm>

<synopsis>
<function>xpath_exists</function> ( <parameter>xpath</parameter> <type>text</type>, <parameter>xml</parameter> <type>xml</type> <optional>, <parameter>nsarray</parameter> <type>text[]</type> </optional> ) <returnvalue>boolean</returnvalue>
</synopsis>

    <para>
     The function <function>xpath_exists</function> is a specialized form
     of the <function>xpath</function> function.  Instead of returning the
     individual XML values that satisfy the XPath 1.0 expression, this function
     returns a Boolean indicating whether the query was satisfied or not
     (specifically, whether it produced any value other than an empty node-set).
     This function is equivalent to the <literal>XMLEXISTS</literal> predicate,
     except that it also offers support for a namespace mapping argument.
    </para>

    <para>
     Example:
<screen><![CDATA[
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)
]]></screen>
    </para>
   </sect3>

   <sect3 id="functions-xml-processing-xmltable">
    <title><literal>xmltable</literal></title>

    <indexterm>
     <primary>xmltable</primary>
    </indexterm>

    <indexterm zone="functions-xml-processing-xmltable">
     <primary>table function</primary>
     <secondary>XMLTABLE</secondary>
    </indexterm>

<synopsis>
<function>XMLTABLE</function> (
    <optional> <literal>XMLNAMESPACES</literal> ( <replaceable>namespace_uri</replaceable> <literal>AS</literal> <replaceable>namespace_name</replaceable> <optional>, ...</optional> ), </optional>
    <replaceable>row_expression</replaceable> <literal>PASSING</literal> <optional><literal>BY</literal> {<literal>REF</literal>|<literal>VALUE</literal>}</optional> <replaceable>document_expression</replaceable> <optional><literal>BY</literal> {<literal>REF</literal>|<literal>VALUE</literal>}</optional>
    <literal>COLUMNS</literal> <replaceable>name</replaceable> { <replaceable>type</replaceable> <optional><literal>PATH</literal> <replaceable>column_expression</replaceable></optional> <optional><literal>DEFAULT</literal> <replaceable>default_expression</replaceable></optional> <optional><literal>NOT NULL</literal> | <literal>NULL</literal></optional>
                  | <literal>FOR ORDINALITY</literal> }
            <optional>, ...</optional>
) <returnvalue>setof record</returnvalue>
</synopsis>

    <para>
     The <function>xmltable</function> expression produces a table based
     on an XML value, an XPath filter to extract rows, and a
     set of column definitions.
     Although it syntactically resembles a function, it can only appear
     as a table in a query's <literal>FROM</literal> clause.
    </para>

    <para>
     The optional <literal>XMLNAMESPACES</literal> clause gives a
     comma-separated list of namespace definitions, where
     each <replaceable>namespace_uri</replaceable> is a <type>text</type>
     expression and each <replaceable>namespace_name</replaceable> is a simple
     identifier.  It specifies the XML namespaces used in the document and
     their aliases. A default namespace specification is not currently
     supported.
    </para>

    <para>
     The required <replaceable>row_expression</replaceable> argument is an
     XPath 1.0 expression (given as <type>text</type>) that is evaluated,
     passing the XML value <replaceable>document_expression</replaceable> as
     its context item, to obtain a set of XML nodes. These nodes are what
     <function>xmltable</function> transforms into output rows. No rows
     will be produced if the <replaceable>document_expression</replaceable>
     is null, nor if the <replaceable>row_expression</replaceable> produces
     an empty node-set or any value other than a node-set.
    </para>

    <para>
     <replaceable>document_expression</replaceable> provides the context
     item for the <replaceable>row_expression</replaceable>. It must be a
     well-formed XML document; fragments/forests are not accepted.
     The <literal>BY REF</literal> and <literal>BY VALUE</literal> clauses
     are accepted but ignored, as discussed in
     <xref linkend="functions-xml-limits-postgresql"/>.
    </para>

    <para>
     In the SQL standard, the <function>xmltable</function> function
     evaluates expressions in the XML Query language,
     but <productname>PostgreSQL</productname> allows only XPath 1.0
     expressions, as discussed in
     <xref linkend="functions-xml-limits-xpath1"/>.
    </para>

    <para>
     The required <literal>COLUMNS</literal> clause specifies the
     column(s) that will be produced in the output table.
     See the syntax summary above for the format.
     A name is required for each column, as is a data type
     (unless <literal>FOR ORDINALITY</literal> is specified, in which case
     type <type>integer</type> is implicit).  The path, default and
     nullability clauses are optional.
    </para>

    <para>
     A column marked <literal>FOR ORDINALITY</literal> will be populated
     with row numbers, starting with 1, in the order of nodes retrieved from
     the <replaceable>row_expression</replaceable>'s result node-set.
     At most one column may be marked <literal>FOR ORDINALITY</literal>.
    </para>

    <note>
     <para>
      XPath 1.0 does not specify an order for nodes in a node-set, so code
      that relies on a particular order of the results will be
      implementation-dependent.  Details can be found in
      <xref linkend="xml-xpath-1-specifics"/>.
     </para>
    </note>

    <para>
     The <replaceable>column_expression</replaceable> for a column is an
     XPath 1.0 expression that is evaluated for each row, with the current
     node from the <replaceable>row_expression</replaceable> result as its
     context item, to find the value of the column.  If
     no <replaceable>column_expression</replaceable> is given, then the
     column name is used as an implicit path.
    </para>

    <para>
     If a column's XPath expression returns a non-XML value (which is limited
     to string, boolean, or double in XPath 1.0) and the column has a
     PostgreSQL type other than <type>xml</type>, the column will be set
     as if by assigning the value's string representation to the PostgreSQL
     type.  (If the value is a boolean, its string representation is taken
     to be <literal>1</literal> or <literal>0</literal> if the output
     column's type category is numeric, otherwise <literal>true</literal> or
     <literal>false</literal>.)
    </para>

    <para>
     If a column's XPath expression returns a non-empty set of XML nodes
     and the column's PostgreSQL type is <type>xml</type>, the column will
     be assigned the expression result exactly, if it is of document or
     content form.
     <footnote>
      <para>
       A result containing more than one element node at the top level, or
       non-whitespace text outside of an element, is an example of content form.
       An XPath result can be of neither form, for example if it returns an
       attribute node selected from the element that contains it. Such a result
       will be put into content form with each such disallowed node replaced by
       its string value, as defined for the XPath 1.0
       <function>string</function> function.
      </para>
     </footnote>
    </para>

    <para>
     A non-XML result assigned to an <type>xml</type> output column produces
     content, a single text node with the string value of the result.
     An XML result assigned to a column of any other type may not have more than
     one node, or an error is raised. If there is exactly one node, the column
     will be set as if by assigning the node's string
     value (as defined for the XPath 1.0 <function>string</function> function)
     to the PostgreSQL type.
    </para>

    <para>
     The string value of an XML element is the concatenation, in document order,
     of all text nodes contained in that element and its descendants. The string
     value of an element with no descendant text nodes is an
     empty string (not <literal>NULL</literal>).
     Any <literal>xsi:nil</literal> attributes are ignored.
     Note that the whitespace-only <literal>text()</literal> node between two non-text
     elements is preserved, and that leading whitespace on a <literal>text()</literal>
     node is not flattened.
     The XPath 1.0 <function>string</function> function may be consulted for the
     rules defining the string value of other XML node types and non-XML values.
    </para>

    <para>
     The conversion rules presented here are not exactly those of the SQL
     standard, as discussed in <xref linkend="functions-xml-limits-casts"/>.
    </para>

    <para>
     If the path expression returns an empty node-set
     (typically, when it does not match)
     for a given row, the column will be set to <literal>NULL</literal>, unless
     a <replaceable>default_expression</replaceable> is specified; then the
     value resulting from evaluating that expression is used.
    </para>

    <para>
     A <replaceable>default_expression</replaceable>, rather than being
     evaluated immediately when <function>xmltable</function> is called,
     is evaluated each time a default is needed for the column.
     If the expression qualifies as stable or immutable, the repeat
     evaluation may be skipped.
     This means that you can usefully use volatile functions like
     <function>nextval</function> in
     <replaceable>default_expression</replaceable>.
    </para>

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

    <para>
     Examples:
  <screen><![CDATA[
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
]]></screen>

     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:

  <screen><![CDATA[
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   bbbxxxCC
]]></screen>
    </para>

    <para>
     The following example illustrates how
     the <literal>XMLNAMESPACES</literal> clause can be used to specify
     a list of namespaces
     used in the XML document as well as in the XPath expressions:

  <screen><![CDATA[
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)
]]></screen>
    </para>
   </sect3>
  </sect2>

  <sect2 id="functions-xml-mapping">
   <title>Mapping Tables to XML</title>

   <indexterm zone="functions-xml-mapping">
    <primary>XML export</primary>
   </indexterm>

   <para>
    The following functions map the contents of relational tables to
    XML values.  They can be thought of as XML export functionality:
<synopsis>
<function>table_to_xml</function> ( <parameter>table</parameter> <type>regclass</type>, <parameter>nulls</parameter> <type>boolean</type>,
               <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>
<function>query_to_xml</function> ( <parameter>query</parameter> <type>text</type>, <parameter>nulls</parameter> <type>boolean</type>,
               <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>
<function>cursor_to_xml</function> ( <parameter>cursor</parameter> <type>refcursor</type>, <parameter>count</parameter> <type>integer</type>, <parameter>nulls</parameter> <type>boolean</type>,
                <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>
</synopsis>
   </para>

   <para>
    <function>table_to_xml</function> maps the content of the named
    table, passed as parameter <parameter>table</parameter>.  The
    <type>regclass</type> type accepts strings identifying tables using the
    usual notation, including optional schema qualification and
    double quotes (see <xref linkend="datatype-oid"/> for details).
    <function>query_to_xml</function> executes the
    query whose text is passed as parameter
    <parameter>query</parameter> and maps the result set.
    <function>cursor_to_xml</function> fetches the indicated number of
    rows from the cursor specified by the parameter
    <parameter>cursor</parameter>.  This variant is recommended if
    large tables have to be mapped, because the result value is built
    up in memory by each function.
   </para>

   <para>
    If <parameter>tableforest</parameter> is false, then the resulting
    XML document looks like this:
<screen><![CDATA[
<tablename>
  <row>
    <columnname1>data</columnname1>
    <columnname2>data</columnname2>
  </row>

  <row>
    ...
  </row>

  ...
</tablename>
]]></screen>

    If <parameter>tableforest</parameter> is true, the result is an
    XML content fragment that looks like this:
<screen><![CDATA[
<tablename>
  <columnname1>data</columnname1>
  <columnname2>data</columnname2>
</tablename>

<tablename>
  ...
</tablename>

...
]]></screen>

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

   <para>
    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
    <function>cursor_to_xml</function> function if the result values are to be
    reassembled into one document later on.  The functions for
    producing XML content discussed above, in particular
    <function>xmlelement</function>, can be used to alter the results
    to taste.
   </para>

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

   <para>
    The parameter <parameter>nulls</parameter> determines whether null
    values should be included in the output.  If true, null values in
    columns are represented as:
<screen><![CDATA[
<columnname xsi:nil="true"/>
]]></screen>
    where <literal>xsi</literal> 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.
   </para>

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

   <para>
    The following functions return XML Schema documents describing the
    mappings performed by the corresponding functions above:
<synopsis>
<function>table_to_xmlschema</function> ( <parameter>table</parameter> <type>regclass</type>, <parameter>nulls</parameter> <type>boolean</type>,
                     <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>
<function>query_to_xmlschema</function> ( <parameter>query</parameter> <type>text</type>, <parameter>nulls</parameter> <type>boolean</type>,
                     <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>
<function>cursor_to_xmlschema</function> ( <parameter>cursor</parameter> <type>refcursor</type>, <parameter>nulls</parameter> <type>boolean</type>,
                      <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>
</synopsis>
    It is essential that the same parameters are passed in order to
    obtain matching XML data mappings and XML Schema documents.
   </para>

   <para>
    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:
<synopsis>
<function>table_to_xml_and_xmlschema</function> ( <parameter>table</parameter> <type>regclass</type>, <parameter>nulls</parameter> <type>boolean</type>,
                             <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>
<function>query_to_xml_and_xmlschema</function> ( <parameter>query</parameter> <type>text</type>, <parameter>nulls</parameter> <type>boolean</type>,
                             <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>
</synopsis>
   </para>

   <para>
    In addition, the following functions are available to produce
    analogous mappings of entire schemas or the entire current
    database:
<synopsis>
<function>schema_to_xml</function> ( <parameter>schema</parameter> <type>name</type>, <parameter>nulls</parameter> <type>boolean</type>,
                <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>
<function>schema_to_xmlschema</function> ( <parameter>schema</parameter> <type>name</type>, <parameter>nulls</parameter> <type>boolean</type>,
                      <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>
<function>schema_to_xml_and_xmlschema</function> ( <parameter>schema</parameter> <type>name</type>, <parameter>nulls</parameter> <type>boolean</type>,
                              <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>

<function>database_to_xml</function> ( <parameter>nulls</parameter> <type>boolean</type>,
                  <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>
<function>database_to_xmlschema</function> ( <parameter>nulls</parameter> <type>boolean</type>,
                        <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>
<function>database_to_xml_and_xmlschema</function> ( <parameter>nulls</parameter> <type>boolean</type>,
                                <parameter>tableforest</parameter> <type>boolean</type>, <parameter>targetns</parameter> <type>text</type> ) <returnvalue>xml</returnvalue>
</synopsis>

    These functions ignore tables that are not readable by the current user.
    The database-wide functions additionally ignore schemas that the current
    user does not have <literal>USAGE</literal> (lookup) privilege for.
   </para>

   <para>
    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.
   </para>

   <para>
    The result of a schema content mapping looks like this:

<screen><![CDATA[
<schemaname>

table1-mapping

table2-mapping

...

</schemaname>]]></screen>

    where the format of a table mapping depends on the
    <parameter>tableforest</parameter> parameter as explained above.
   </para>

   <para>
    The result of a database content mapping looks like this:

<screen><![CDATA[
<dbname>

<schema1name>
  ...
</schema1name>

<schema2name>
  ...
</schema2name>

...

</dbname>]]></screen>

    where the schema mapping is as above.
   </para>

   <para>
    As an example of using the output produced by these functions,
    <xref linkend="xslt-xml-html"/> shows an XSLT stylesheet that
    converts the output of
    <function>table_to_xml_and_xmlschema</function> 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.
   </para>

   <example id="xslt-xml-html">
    <title>XSLT Stylesheet for Converting SQL/XML Output to HTML</title>
<programlisting><![CDATA[
<?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>
]]></programlisting>
   </example>
  </sect2>
 </sect1>