PostgreSQL XML Functions

Last update on November 13 2023 09:59:21 (UTC/GMT +8 hours)

Introduction

This tutorial is about using functions and function like expressions of PostgreSQL (we have used version 9.3) which work on xml data type. XML data type is used to store XML documents.

The purpose of these functions (and function like expressions) are to generate XML documents from SQL data. XML documents thus produced mat be used for further processing by other applications.

xmlcomment() function

this function creates a commented XML value. '--' and '-' are added before and after the value to make it a valid XML comment. If the argument is null, the result is null.

Syntax:

xmlcomment(text)

Example

Code:

SELECT xmlcomment('this is a comment');

Here is the result.

Sample Output:

     xmlcomment
--------------------
 <!--This is a comment-->
(1 row)

xmlconcat() function

A list of XML values is joined together to create a single value by using this function. Null values are not considered in this function. The result is only null if there are no non null arguments.

Syntax:

xmlconcat(xml[, ...])

Example

Code:

SELECT xmlconcat('<books/>', '<book>w3resource tutorials</book>');

Here is the result.

Sample Output:

 xmlconcat                 
-------------------------------------------
 <books/><book>w3resource tutorials</book>
(1 row)

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.

if any XML declarations are present, they are used as follows:

If a version (of XML declarations) is present in all argument values, that version is used
If all argument values have standalone declaration values yes, that value is used
If all argument values have standalone declaration values no, that value is used
if 2 and 3 are not satisfied, a not declaration is used in resulting XML
If the result is determined to require a standalone declaration but no version declaration, a version declaration with version 1.0 will be used
Encoding declarations, if found, is ignored and not used.

Example

Code:

SELECT xmlconcat('<?xml version="1.1"?><books/>', '<?xml version="1.1" standalone="no"?><book/>');

Here is the result.

Sample Output:

             xmlconcat
-----------------------------------
 <?xml version="1.1"?><books/><book/>
(1 row)

xmlelement() function

This function generates an XML element with the given name, attributes, and content.

Syntax:

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

Example

Code:

SELECT xmlelement(name books);

Here is the result.

Sample Output:

 xmlelement
------------
 <books/>
(1 row)

Here is another example

Code:

SELECT xmlelement(name books, xmlattributes('title' as w3resource));

Here is the result.

Sample Output:

    xmlelement
------------------
 <books title="w3resource"/>
(1 row)

Here is another example.

Code:

SELECT xmlelement(name calendar, xmlattributes(current_date as date), 'current', ' date');

Here is the result.

Sample Output:

                xmlelement
-------------------------------------------
 <calendar date="2014-11-03">current date </abc>
(1 row)

if any value does not satisfy conditions to be a valid XML name or attribute, non valid XML characters are replaced by the sequence _xHHHH_, where HHHH is the character's Unicode code point in hexadecimal notation.

Example

Code:

SELECT xmlelement(name "book$name", xmlattributes('title' as "w3r%esource"));

Here is the result.

Sample Output:

            xmlelement
----------------------------------
 <book_x0024_name w3r_x0025_esource="title"/>
(1 row)

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.

If the attribute value is a column reference, an attribute name can not be used explicitly. Otherwise, attribute name must have an explicit name.

Example

Code:

CREATE TABLE demo_table (p xml, q xml);
SELECT xmlelement(name demo_table, xmlattributes(p, q)) FROM demo_table;

Here is the result.

Sample Output:

 xmlelement
------------
(0 rows)

But these are not:

Example

Code:

SELECT xmlelement(name demo_table, xmlattributes('hello'), p, q) FROM demo_table;
SELECT xmlelement(name demo_table, xmlattributes(func(p, q))) FROM demo_table;

Element contents are formatted following it's data type. If the content itself is of type XML, complex XML documents may be generated.

Example

Code:

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

Here is the result.

Sample Output:

                         xmlelement
------------------------------------------------------------
<books title="w3r"><w3r/><!--demo--><title/></books>
(1 row)

Other types are formatted into valid XML character data. Following this rule, <, > and & will be converted to entities. Binary data (data type byte) 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() function

The xmlforest expression produces an XML forest (i.e. sequence) of elements they use the given names and content.

Syntax:

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

Example

Code:

SELECT xmlforest('book' AS newelement, 123 AS number);

Here is the result.

Sample Output:

                 xmlforest
--------------------------------------------
<newelement>book</newelement><number>123</number>
(1 row)

Example

Code:

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

The example above shows that if the content value of the element is a column reference, column name is used by default. Else a name must be mentioned.

Element names those are not valid XML names are escaped. if not type XML, content data is escaped to make valid XML content.

XML forests, if containing more than one XML element, are not valid XML documents.It is useful thus, to wrap xmlforest expressions in xmlelement.

xmlpi() function

XML processing instructions are generated using xmlpi function. if the processing instruction contains the character sequence ?>, then it is not generated.

Syntax:

xmlpi(name target [, content])

Example

Code:

SELECT xmlpi(name php, 'echo "this is w3resource";');

Here is the result.

Sample Output:

               xmlpi                
------------------------------------
 <?php echo "this is w3resource";?>
(1 row)

xmlroot() function

The xmlroot expression is used to alter 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.

Syntax:

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

Example

Code:

SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>w3resource</content>'),
version '1.0', standalone yes);

Here is the result.

Sample Output:

 xmlroot                               
---------------------------------------------------------------------
 <?xml version="1.0" standalone="yes"?><content>w3resource</content>
(1 row)

xmlagg() function

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.

Syntax:

xmlagg(xml)

Example

Code:

CREATE TABLE demo (p int, q xml);
INSERT INTO demo VALUES (1, '<foo>abc</foo>');
INSERT INTO demo VALUES (2, '<bar/>');
SELECT xmlagg(p) FROM demo;

Here is the result.

Sample Output:

xmlagg                                 
------------------------------------------------------------------------
 <foo>abc</foo><bar/><bar/><bar/><foo>abc</foo><bar/>
(1 row)

an ORDER BY clause may be used to determine the order of concatenation.

Example

Code:

SELECT xmlagg(q ORDER BY p DESC) FROM demo;

Here is the result.

Sample Output:

 xmlagg                                 
------------------------------------------------------------------------
 <bar/><\bar/><bar/><bar/><foo>abc</foo><foo>abc</foo>
(1 row)

Example

Code:

SELECT xmlagg(q) FROM (SELECT * FROM demo ORDER BY p DESC) AS tab;

Here is the result.

Sample Output:

 xmlagg                                 
------------------------------------------------------------------------
 <bar/><bar/><bar/><bar/><foo>abc</foo><foo>abc</foo>
(1 row)

Though non-standard, the following approach was suggested in previous versions. it may still be useful.

XML Predicates

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

IS DOCUMENT

if the argument XML value is a proper XML document, The IS DOCUMENT expression returns true. If it is a content fragment it returns false. null is returned if the argument is null.

Syntax:

xml IS DOCUMENT

XMLEXISTS

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

Syntax:

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

Example

Code:

SELECT xmlexists('//games[text() = ''Soccer'']' PASSING BY REF '<games><game>Soccer</game><game>football</game></games>');

Here is the result.

Sample Output:

xmlexists 
-----------
 f
(1 row)

Though it does not have any effect in PostgreSQL, it is included for the conformity to the SQL standard. The first BY REF is required but the second one is optional. Since PostgreSQL currently supports XPath(which is a subset of xQuery) only, despite SQL standards say that it must use XQuery, XPath is used.

xml_is_well_formed() function

xml_is_well_formed() function checks whether a text string is well-formed XML. It returns a Boolean result. xml_is_well_formed_document checks for a well-formed document. 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.

Syntax:

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

Example

Code:

SET xmloption TO DOCUMENT;
SELECT xml_is_well_formed('<>');

Here is the result.

Sample Output:

SET xmloption TO DOCUMENT;
SELECT xml_is_well_formed('<>');

Example

Code:

SELECT xml_is_well_formed('');

Here is the result.

Sample Output:

xml_is_well_formed 
--------------------
 t
(1 row)

Example

Code:

SET xmloption TO CONTENT;
SELECT xml_is_well_formed('w3resource');

Here is the result.

Sample Output:

 xml_is_well_formed 
--------------------
 t
(1 row)

Example

Code:

SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>');

Example

Code:

SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>');

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

Processing XML

PostgreSQL provides developers with functions xpath and xpath_exists to process values of data type XML.

xpath() function

Syntax:

xpath(xpath, xml [, nsarray])

The xpath function evaluates XPath expression. it returns an array of XML values. These values are related to the node set generated by the associated XPath expression. If it returns a scalar value, instead of node set, an array with a sole element is required.

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

The third argument of the function is an array of namespace mappings. This argument is optional. This must be a two-dimensional text array. The length of the second axis must be equal to 2. namespace name and namespace URI is first and second entries of the array. It is not required that aliases provided in this array be the same as those being used in the XML document itself

Example

Code:

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

Here is the result.

Sample Output:

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

Following example shows how to handle default namespaces

Example

Code:

SELECT xpath('//mydefns:b/text()', '<x xmlns="http://example.com"><y>test</y></x>',
ARRAY[ARRAY['mydefns', 'http://example.com']]);

Here is the result.

Sample Output:

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

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.

Syntax:

xpath_exists(xpath, xml [, nsarray])

Example

Code:

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

Here is the result.

Sample Output:

Return

Mapping Tables to XML

The following functions map the contents of relational tables to XML values. They can be thought of as XML export functionality:

Syntax:

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)

Return Type

The return type of each function is xml.

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

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

Example

Code:

<tablename>
  <row>
    <columnname1>data</columnname1>
    <columnname2>data</columnname2>
  </row>

  <row>
    ...
  </row>

  ...
</tablename>

If tableforest is true, the result is an XML content fragment that looks like this:

Example

Code:

<tablename>
  <columnname1>data</columnname1>
  <columnname2>data</columnname2>
</tablename>

<tablename>
  ...
</tablename>

...

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

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

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

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

<columnname xsi:nil="true"/>

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

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

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

Example

Code:

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)

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

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

Example

Code:

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)

In addition, the following functions are available to produce analogous mappings of entire schemas or the entire current database:

Example

Code:

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)

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

The result of a schema content mapping looks like this:

Here is the result.

Sample Output:

<schemaname>

table1-mapping

table2-mapping

...

</schemaname>

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

The result of a database content mapping looks like this:

Here is the result.

Sample Output:

<dbname>

<schema1name>
  ...
</schema1name>

<schema2name>
  ...
</schema2name>

...

</dbname>

where the schema mapping is as above.

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

Previous: Range Functions and Operators
Next: Text Search Functions and Operators