Generating XML Content

XMLPARSE ( { DOCUMENT | CONTENT } value)

Description: Generates an XML value from character data.

Return type: XML

Example:

1
2
3
4
5

SELECT xmlparse(document '<foo>bar</foo>');
xmlparse
----------------
<foo>bar</foo>
(1 row)

XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type

Description: Generates a string from XML values.

Return type: type, which can be character, character varying, or text (or its alias)

Example:

1
2
3
4
5

SELECT xmlserialize(content 'good' AS CHAR(10));
xmlserialize
--------------
good
(1 row)

xmlcomment(text)

Description: Creates an XML note that uses the specified text as the content. The text cannot contain two consecutive hyphens (--) or end with a hyphen (-). If the parameter is null, the result is also null.

Return type: XML

Example:

1
2
3
4
5

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

xmlconcat(xml[, ...])

Description: Concatenates a list of XML values into a single value. Null values are ignored. If all parameters are null, the result is also null.

Return type: XML

Example:

1
2
3
4
5

SELECT xmlconcat('<abc/>', '<bar>foo</bar>');
xmlconcat
----------------------
<abc/><bar>foo</bar>
(1 row)

Note: If XML declarations exist and they are the same XML version, the result will use the version. Otherwise, the result does not use any version. If all XML values have the standalone attribute whose status is yes, the standalone attribute in the result is yes. If at least one XML value's standalone attribute is no, the standalone attribute in the result is no. Otherwise, the result does not contain the standalone attribute.

Example:

1
2
3
4
5

SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');
xmlconcat
-----------------------------------
<?xml version="1.1"?><foo/><bar/>
(1 row)

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

Description: Generates an XML element with the given name, attribute, and content.

Return type: XML

Example:

1
2
3
4
5

SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent');
xmlelement
-------------------------------------
<foo bar="2020-08-15">content</foo>
(1 row)

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

Description: Generates an XML forest (sequence) of an element with a given name and content.

Return type: XML

Example:

1
2
3
4
5

SELECT xmlforest('abc' AS foo, 123 AS bar);
xmlforest
------------------------------
<foo>abc</foo><bar>123</bar>
(1 row)

xmlpi(name target [, content])

Description: Creates an XML processing instruction. The content cannot contain the character sequence of ?>.

Return type: XML

Example:

1
2
3
4
5

SELECT xmlpi(name php, 'echo "hello world";');
xmlpi
-----------------------------
<?php echo "hello world";?>
(1 row)

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

Description: Modifies the attributes of the root node of an XML value. If a version is specified, it replaces the value in the version declaration of the root node. If a standalone value is specified, it replaces the standalone value in the root node.

Return type: XML

Example:

1
2
3
4
5

SELECT xmlroot(xmlparse(document '<?xml version="1.0" standalone="no"?><content>abc</content>'), version '1.1', standalone yes);
xmlroot
--------------------------------------------------------------
<?xml version="1.1" standalone="yes"?><content>abc</content>
(1 row)

xmlagg(xml)

Description: The xmlagg function is an aggregate function that concatenates input values.

Return type: XML

Example:

1
2
3
4
5
6
7
8

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/>
(1 row)

To determine the concatenation sequence, you can add an ORDER BY clause for an aggregate call, for example:

1
2
3
4
5

SELECT xmlagg(x ORDER BY y DESC) FROM test;
xmlagg
----------------------
<bar/><foo>abc</foo>
(1 row)