XML operation function code in PostgreSQL

XML content generation part
SQL data generates XML functions.
1. xmlcomment: Generate annotation function.
xmlcomment(text )
example:

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

2. xmlconcat: XML connection function
xmlconcat(xml [, ...])
example:
SELECT xmlconcat('<abc/>', '<bar>foo</bar>');

xmlconcat
----------------------
<abc/><bar>foo</bar>

If there are multiple version declarations in the connected XML data, there is only one version declaration for the connected XML.
example:
SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');

xmlconcat
-----------------------------------
<?xml version="1.1"?><foo/><bar/>

3. xmlelement: Generate XML element functions
xmlelement(name name [, xmlattributes( value [AS attname ] [, ... ])] [ , content, ... ])
example:
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>

If there are illegal characters, the extraordinary characters will be represented by hexadecimal numbers.
Example: SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b"));
xmlelement
----------------------------------
<foo_x0024_bar a_x0026_b="xyz"/>

4. xmlforest: Generate XML FOREST function
xmlforest(content [AS name ] [, ...])
example:
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>
...
5. xmlpi: Generate XML processing command functions.
xmlpi(name target [, content ])
example:
SELECT xmlpi(name php, 'echo "hello world";');
xmlpi
-----------------------------
<?php echo "hello world";?>

6. xmlroot: modify the root node attribute function of XML value
xmlroot(xml , version text |no value [, standalone yes|no|no value])
Example: 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>

7. xmlagg: xmlagg is an intensive function
xmlagg(xml )
example:
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/>

The connection order can be changed using the following method.
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
xmlagg
----------------------
<bar/><foo>abc</foo>

Processing XML
To process XML data, the xpath function is provided in PostgreSL.
xpath(xpath , xml [, nsarray ])

example:
SELECT xpath('/my:a/text()', '<my:a xmlns:my="">test</my:a>',
ARRAY[ARRAY['my', '']]);
xpath
--------
{test}
(1 row)

XML and table mapping
The following functions can export XML.
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)
The return values ​​of these functions are all XML.

There are also the following functions. For details, please refer to the user manual.
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)
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)
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)