The XML operation function code in PostgreSQL

XML content generation part
SQL data generates XML functions.
1.xmlcomment: generates comment functions.
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, the connected XML has only one version declaration.
Example:
SELECT xmlconcat(' < ?xml version="1.1"? > < foo/ > ', ' < ?xml version="1.1" standalone="no"? > < bar/ > ');

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

3. xmlelement: generates the XML element function
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 unusual characters are represented in hexadecimal digits.
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: generates the XML processing command function.
xmlpi(name target [, content ])
Example:
SELECT xmlpi(name php, 'echo "hello world";');
xmlpi
-----------------------------
< ?php echo "hello world";? >

6.xmlroot: changes the root node attribute function
for the 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 the 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/ >

You can change the join order using the following types of methods.
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
xmlagg
----------------------
< bar/ > < foo > abc < /foo >

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

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

Mapping
to XML and table The following function exports 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 of type XML.

There are also the following functions. Specific content can refer to 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)