PostgreSQL中的XML操作函數(shù)代碼

XML內(nèi)容生成部分
SQL數(shù)據(jù)生成XML的函數(shù)。
1. xmlcomment：生成注釋函數(shù)。
xmlcomment(text )
例：

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

2. xmlconcat：XML連接函數(shù)
xmlconcat(xml [, ...])
例：
SELECT xmlconcat('abc/>', 'bar>foo/bar>');

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

連接的XML數(shù)據(jù)中如果有多個版本聲明的話，連接后的XML只有一個版本聲明。
例：
SELECT xmlconcat('?xml version="1.1"?>foo/>', '?xml version="1.1" standalone="no"?>bar/>');

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

3. xmlelement：生成XML元素函數(shù)
xmlelement(name name [, xmlattributes( value [AS attname ] [, ... ])] [ , content, ... ])
例：
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>

如果有非法字符的話，非常字符會用 16進(jìn)制的數(shù)字表示出來。
例子：SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "ab"));
xmlelement
----------------------------------
foo_x0024_bar a_x0026_b="xyz"/>

4. xmlforest：生成XML FOREST函數(shù)
xmlforest(content [AS name ] [, ...])
例：
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：生成XML處理命令函數(shù)。
xmlpi(name target [, content ])
例：
SELECT xmlpi(name php, 'echo "hello world";');
xmlpi
-----------------------------
?php echo "hello world";?>

6. xmlroot：修改XML值的根節(jié)點屬性函數(shù)
xmlroot(xml , version text |no value [, standalone yes|no|no value])
例子：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是集約函數(shù)
xmlagg(xml )
例：
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/>

可以用下面類型的方法改變連接順序。
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
xmlagg
----------------------
bar/>foo>abc/foo>

Processing XML
為了處理XML數(shù)據(jù)，PostgreSL中提供了xpath函數(shù)。
xpath(xpath , xml [, nsarray ])

例：
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)

XML和table的映射
以下函數(shù)可以導(dǎo)出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)
這些函數(shù)的返回值都是XML類型。

還有以下函數(shù)。具體內(nèi)容可以參看用戶手冊。
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)