XML operation function code in PostgreSQL

XML content generation
SQL data generation XML function.
1. xmlcomment: generate the 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, the connected XML only has 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: XML element generation function
Xmlelement (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 an invalid character exists, the special character is represented by a hexadecimal number.
Example: SELECT xmlelement (name "foo $ bar", xmlattributes ('xyz' as "a & B "));
Xmlelement
----------------------------------
<Foo_x0024_bar a_x0026_ B = "xyz"/>

4. xmlforest: generate the 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
Bytes -------------------------------------------------------------------------------------------
<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: Root Node attribute function for modifying XML values
Xmlroot (xml, version text | no value [, standalone yes | 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. xmldetail: xmldetail is an intensive function.
Xmlworkflow (xml)
Example:
Create table test (y int, x xml );
Insert into test VALUES (1, '<foo> abc </foo> ');
Insert into test VALUES (2, '<bar/> ');
SELECT xmltransform (x) FROM test;
Xmlparts
----------------------
<Foo> abc </foo> <bar/>

You can use the following method to change the connection sequence.
SELECT xmlsort (x) FROM (SELECT * FROM test order by y DESC) AS tab;
Xmlparts
----------------------
<Bar/> <foo> abc </foo>

Processing XML
To process XML data, objective SL provides the xpath function.
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)

Ing between XML and table
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 of the XML type.

There are also the following functions. For more information, see 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)