<script type="text/javascript"> </script> <script src="http://www.google-analytics.com/ga.js" type="text/javascript"></script> <script type="text/javascript"> </script> XML内容生成部分 |
SQL数据生成XML的函数。 |
1. xmlcomment:生成注释函数。 |
xmlcomment(text ) |
例: |
SELECT xmlcomment('hello'); |
xmlcomment |
-------------- |
<!--hello--> |
2. xmlconcat:XML连接函数 |
xmlconcat(xml [, ...]) |
例: |
SELECT xmlconcat('<abc/>', '<bar>foo</bar>'); |
xmlconcat |
---------------------- |
<abc/><bar>foo</bar> |
连接的XML数据中如果有多个版本声明的话,连接后的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元素函数 |
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进制的数字表示出来。 |
例子:SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b")); |
xmlelement |
---------------------------------- |
<foo_x0024_bar a_x0026_b="xyz"/> |
4. xmlforest:生成XML FOREST函数 |
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处理命令函数。 |
xmlpi(name target [, content ]) |
例: |
SELECT xmlpi(name php, 'echo "hello world";'); |
xmlpi |
----------------------------- |
<?php echo "hello world";?> |
6. xmlroot:修改XML值的根节点属性函数 |
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是集约函数 |
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数据,PostgreSL中提供了xpath函数。 |
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的映射 |
以下函数可以导出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) |
这些函数的返回值都是XML类型。 |
还有以下函数。具体内容可以参看用户手册。 |
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) |