9.14. XML Functions

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
--------------
 

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.

Example:

SELECT xmlconcat('', 'foo');

      xmlconcat
----------------------
 foo

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.

Example:

SELECT xmlconcat('', '');

             xmlconcat
-----------------------------------
 

xmlelement(name name [, xmlattributes(value [AS attname] [, ... ])] [, content, ...])

The xmlelement expression produces an XML element with the given name, attributes, and content.

Examples:

SELECT xmlelement(name foo);

 xmlelement
------------
 

SELECT xmlelement(name foo, xmlattributes('xyz' as bar));

    xmlelement
------------------
 

SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent');

             xmlelement
-------------------------------------
 content

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

SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b"));

            xmlelement
----------------------------------
 

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:

CREATE TABLE test (a xml, b xml);
SELECT xmlelement(name test, xmlattributes(a, b)) FROM test;

But these are not:

SELECT xmlelement(name test, xmlattributes('constant'), a, b) FROM test;
SELECT xmlelement(name test, xmlattributes(func(a, b))) FROM test;

Element content, if specified, will be formatted according to its data type. If the content is itself of type xml , complex XML documents can be constructed. For example:

SELECT xmlelement(name foo, xmlattributes('xyz' as bar),
                            xmlelement(name abc),
                            xmlcomment('test'),
                            xmlelement(name xyz));

                  xmlelement
----------------------------------------------
 

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.

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

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

Examples:

SELECT xmlforest('abc' AS foo, 123 AS bar);

          xmlforest
------------------------------
 abc123


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

                                         xmlforest
-------------------------------------------------------------------------------------------
 pg_authidrolname
 pg_authidrolsuper
 ...

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.

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

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

xmlpi(name target [, content])

The xmlpi expression creates an XML processing instruction. The content, if present, must not contain the character sequence ?> .

Example:

SELECT xmlpi(name php, 'echo "hello world";');

            xmlpi
-----------------------------
 

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.

SELECT xmlroot(xmlparse(document 'abc'),
               version '1.0', standalone yes);

                xmlroot
----------------------------------------
 
 abc

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.

Example:

CREATE TABLE test (y int, x xml);
INSERT INTO test VALUES (1, 'abc');
INSERT INTO test VALUES (2, '');
SELECT xmlagg(x) FROM test;
        xmlagg
----------------------
 abc

To determine the order of the concatenation, an ORDER BY clause may be added to the aggregate call as described in Section 4.2.7 . For example:

SELECT xmlagg(x ORDER BY y DESC) FROM test;
        xmlagg
----------------------
 abc

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
----------------------
 abc

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.

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.

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.)

Example:

SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF 'TorontoOttawa');

 xmlexists
------------
 t
(1 row)

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.

xml_is_well_formed(text)
xml_is_well_formed_document(text)
xml_is_well_formed_content(text)

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.

Examples:

SET xmloption TO DOCUMENT;
SELECT xml_is_well_formed('<>');
 xml_is_well_formed 
--------------------
 f
(1 row)

SELECT xml_is_well_formed('');
 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('bar');
 xml_is_well_formed_document 
-----------------------------
 t
(1 row)

SELECT xml_is_well_formed_document('bar');
 xml_is_well_formed_document 
-----------------------------
 f
(1 row)

The last example shows that the checks include whether namespaces are correctly matched.

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 ).

Example:

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

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

To deal with default (anonymous) namespaces, do something like this:

SELECT xpath('//mydefns:b/text()', 'test',
             ARRAY[ARRAY['mydefns', 'http://example.com']]);

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

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.

Example:

SELECT xpath_exists('/my:a/text()', 'test',
                     ARRAY[ARRAY['my', 'http://example.com']]);

 xpath_exists  
--------------
 t
(1 row)

xmltable( [XMLNAMESPACES(namespace uri AS namespace name[, ...]), ]
          row_expression PASSING [BY REF] document_expression [BY REF]
          COLUMNS name { type [PATH column_expression] [DEFAULT default_expression] [NOT NULL | NULL]
                        | FOR ORDINALITY }
                   [, ...]
)

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.

Examples:

CREATE TABLE xmldata AS SELECT
xml $$

  
    AU
    Australia
  
  
    JP
    Japan
    Shinzo Abe
    145935
  
  
    SG
    Singapore
    697
  

$$ 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

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:

CREATE TABLE xmlelements AS SELECT
xml $$
  
     Hello2a2   bbbxxxCC  
  
$$ AS data;

SELECT xmltable.*
  FROM xmlelements, XMLTABLE('/root' PASSING data COLUMNS element text);
       element        
----------------------
   Hello2a2   bbbCC  

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

WITH xmldata(data) AS (VALUES ('

 
 
 
'::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)