| PostgreSQL 9.6.0 锟街诧拷
| |||
|---|---|---|---|
| 锟斤拷一页 | 锟斤拷一锟斤拷 | 锟斤拷 9. 锟斤拷锟斤拷锟酵诧拷锟斤拷锟斤拷 | 锟斤拷一页 |
锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟侥猴拷锟斤拷锟皆硷拷锟洁函锟斤拷锟侥憋拷锟斤拷式锟斤拷锟斤拷锟斤拷锟斤拷xml锟斤拷值锟较诧拷锟斤拷锟斤拷锟斤拷锟斤拷xml锟斤拷锟斤拷细锟斤拷息锟斤拷渭锟�锟斤拷 8.13 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷值锟斤拷锟斤拷锟斤拷xml之锟斤拷转锟斤拷锟斤拷锟洁函锟斤拷锟侥憋拷锟斤拷式xmlparse锟斤拷xmlserialize锟酵诧拷锟斤拷锟斤拷锟斤拷锟截革拷锟斤拷锟杰★拷使锟矫大部凤拷锟斤拷些锟斤拷锟斤拷要锟斤拷装时使锟斤拷锟斤拷configure --with-libxml锟斤拷锟叫憋拷锟诫。
锟斤拷一锟介函锟斤拷锟斤拷锟洁函锟斤拷锟侥憋拷锟斤拷式锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 SQL 锟斤拷锟捷诧拷锟斤拷 XML 锟斤拷锟捷★拷锟斤拷锟斤拷锟截憋拷锟绞猴拷锟节斤拷锟斤拷询锟斤拷锟斤拷锟绞斤拷锟斤拷锟� XML 锟侥碉拷锟皆憋拷锟斤拷锟节客伙拷锟斤拷应锟斤拷锟叫达拷锟斤拷锟斤拷
xmlcomment(text) 锟斤拷锟斤拷xmlcomment锟斤拷锟斤拷锟斤拷一锟斤拷 XML 值锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷使锟斤拷指锟斤拷锟侥憋拷锟斤拷为锟斤拷锟捷碉拷 XML 注锟酵★拷锟斤拷锟侥憋拷锟斤拷锟斤拷锟斤拷"--"锟斤拷锟斤拷也锟斤拷锟斤拷锟斤拷一锟斤拷"-"锟斤拷尾锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷慕峁癸拷锟揭伙拷锟斤拷戏锟斤拷锟� XML 注锟酵★拷锟斤拷锟斤拷锟斤拷锟轿拷眨锟斤拷锟斤拷也为锟秸★拷
锟斤拷锟接ｏ拷
SELECT xmlcomment('hello');
xmlcomment
--------------
<!--hello-->
xmlconcat(xml[, ...]) 锟斤拷锟斤拷xmlconcat锟斤拷锟缴碉拷锟斤拷 XML 值锟斤拷傻锟斤拷斜锟斤拷锟斤拷映锟揭伙拷锟斤拷锟斤拷锟斤拷锟街碉拷锟斤拷锟斤拷值锟斤拷锟斤拷一锟斤拷 XML 锟斤拷锟斤拷片锟较★拷锟斤拷值锟结被锟斤拷锟皆ｏ拷只锟叫碉拷没锟叫诧拷锟斤拷为锟角匡拷时锟斤拷锟斤拷锟轿拷铡锟�
锟斤拷锟接ｏ拷
SELECT xmlconcat('<abc/>', '<bar>foo</bar>');
xmlconcat
----------------------
<abc/><bar>foo</bar>
锟斤拷锟� XML 锟斤拷锟斤拷锟斤拷锟节ｏ拷锟斤拷锟角会按锟斤拷锟斤拷锟斤拷姆锟绞斤拷锟斤拷锟较★拷锟斤拷锟斤拷锟斤拷械牟锟斤拷锟街碉拷锟斤拷锟斤拷锟酵拷锟� XML 锟芥本锟斤拷锟斤拷锟斤拷锟矫版本锟斤拷锟斤拷锟斤拷锟节斤拷锟斤拷校锟斤拷锟斤拷蚪锟绞癸拷冒姹撅拷锟斤拷锟斤拷锟斤拷锟叫诧拷锟斤拷值锟叫讹拷锟斤拷锟斤拷锟斤拷值"yes"锟斤拷锟斤拷么锟斤拷值锟斤拷锟斤拷锟斤拷锟节斤拷锟斤拷小锟斤拷锟斤拷锟斤拷锟叫诧拷锟斤拷值锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷值锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷为"no"锟斤拷锟斤拷"no"锟斤拷锟斤拷锟节斤拷锟斤拷小锟斤拷锟斤拷锟斤拷锟斤拷薪锟矫伙拷卸锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟揭拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟矫伙拷邪姹撅拷锟斤拷锟斤拷锟斤拷锟斤拷锟绞癸拷锟揭伙拷锟斤拷姹� 1.0 锟侥版本锟斤拷锟斤拷锟斤拷锟斤拷为 XML 要锟斤拷一锟斤拷 XML 锟斤拷锟斤拷要锟斤拷锟斤拷一锟斤拷锟芥本锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟结被锟斤拷锟皆诧拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷卸锟斤拷岜伙拷瞥锟斤拷锟�
锟斤拷锟接ｏ拷
SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');
xmlconcat
-----------------------------------
<?xml version="1.1"?><foo/><bar/>
xmlelement(name name [, xmlattributes(value [AS attname] [, ... ])] [, content, ...]) 锟斤拷锟斤拷式xmlelement使锟矫革拷锟斤拷锟斤拷锟狡★拷锟斤拷锟皆猴拷锟斤拷锟捷诧拷锟斤拷一锟斤拷 XML 元锟截★拷
锟斤拷锟接ｏ拷
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>
锟斤拷锟角合凤拷 XML 锟斤拷锟街碉拷元锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟捷ｏ拷锟斤拷锟捷的凤拷锟斤拷锟角斤拷违锟斤拷锟斤拷锟街凤拷锟斤拷锟斤拷锟斤拷_xHHHH_锟芥换锟斤拷锟斤拷锟斤拷HHHH锟角憋拷锟芥换锟街凤拷锟斤拷 Unicode 锟斤拷锟斤拷锟斤拷十锟斤拷锟斤拷锟狡憋拷示锟斤拷锟斤拷锟界：
SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b"));
xmlelement
----------------------------------
<foo_x0024_bar a_x0026_b="xyz"/>
锟斤拷锟斤拷锟斤拷锟街碉拷锟揭伙拷锟斤拷锟斤拷锟斤拷茫锟斤拷锟斤拷锟揭革拷锟揭伙拷锟斤拷锟绞斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟叫碉拷锟斤拷锟街斤拷锟斤拷默锟斤拷锟斤拷锟斤拷锟斤拷锟皆碉拷锟斤拷锟街★拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷拢锟斤拷锟斤拷员锟斤拷氡伙拷锟斤拷锟揭伙拷锟斤拷锟绞斤拷锟斤拷啤锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷呛戏锟斤拷模锟�
CREATE TABLE test (a xml, b xml); SELECT xmlelement(name test, xmlattributes(a, b)) FROM test;
锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷些锟斤拷锟较凤拷锟斤拷
SELECT xmlelement(name test, xmlattributes('constant'), a, b) FROM test;
SELECT xmlelement(name test, xmlattributes(func(a, b))) FROM test;
锟斤拷锟街革拷锟斤拷锟皆拷锟斤拷锟斤拷荩锟斤拷锟斤拷墙锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷透锟绞斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟捷憋拷锟斤拷也锟斤拷锟斤拷锟斤拷xml锟斤拷锟酵匡拷锟皆癸拷锟斤拷锟斤拷锟接碉拷 XML 锟侥碉拷锟斤拷锟斤拷锟界：
SELECT xmlelement(name foo, xmlattributes('xyz' as bar),
xmlelement(name abc),
xmlcomment('test'),
xmlelement(name xyz));
xmlelement
----------------------------------------------
<foo bar="xyz"><abc/><!--test--><xyz/></foo>锟斤拷锟斤拷锟斤拷锟酵碉拷锟斤拷锟捷斤拷锟斤拷锟斤拷式锟斤拷为锟较凤拷锟斤拷 XML 锟街凤拷锟斤拷锟捷★拷锟斤拷锟斤拷味锟斤拷锟街凤拷 <, >, 锟斤拷 & 锟斤拷锟斤拷转锟斤拷为实锟藉。锟斤拷锟斤拷锟斤拷锟斤拷锟捷ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷bytea锟斤拷锟斤拷锟斤拷锟斤拷示锟斤拷 base64 锟斤拷十锟斤拷锟斤拷锟狡憋拷锟诫，锟斤拷锟斤拷取锟斤拷锟斤拷锟斤拷锟矫诧拷锟斤拷xmlbinary锟斤拷锟斤拷锟矫★拷为锟剿斤拷 SQL 锟斤拷 PostgreSQL 锟斤拷锟斤拷锟斤拷锟酵猴拷 XML 模式锟斤拷锟斤拷锟斤拷锟诫，锟斤拷锟斤拷锟节达拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟酵碉拷锟截讹拷锟斤拷为锟杰癸拷锟侥斤拷锟斤拷锟斤拷锟斤拷时锟斤拷锟斤拷锟斤拷锟揭伙拷锟斤拷锟轿拷锟饺凤拷锟斤拷锟斤拷锟斤拷锟�
xmlforest(content [AS name] [, ...]) 锟斤拷锟斤拷式xmlforest使锟矫革拷锟斤拷锟斤拷锟狡猴拷锟斤拷锟捷诧拷锟斤拷一锟斤拷元锟截碉拷 XML 森锟街ｏ拷锟斤拷锟叫ｏ拷锟斤拷
锟斤拷锟接ｏ拷
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>
...锟斤拷锟斤拷锟斤拷锟节第讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟街碉拷锟揭伙拷锟斤拷锟斤拷锟斤拷茫锟皆拷锟斤拷锟斤拷瓶锟斤拷员锟斤拷锟斤拷裕锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷默锟斤拷使锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟津，憋拷锟斤拷指锟斤拷一锟斤拷锟斤拷锟街★拷
锟斤拷锟斤拷锟斤拷xmlelement锟斤拷示锟斤拷锟角凤拷 XML 锟斤拷锟街碉拷元锟斤拷锟斤拷锟结被锟斤拷锟捷★拷锟斤拷锟狡地ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷也锟结被锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟较凤拷锟斤拷 XML 锟斤拷锟捷ｏ拷锟斤拷锟斤拷锟斤拷锟窖撅拷锟斤拷一锟斤拷xml锟斤拷锟酵★拷
注锟斤拷锟斤拷锟� XML 森锟斤拷锟缴讹拷锟斤拷一锟斤拷元锟斤拷锟斤拷桑锟斤拷锟矫达拷锟斤拷锟斤拷呛戏锟斤拷锟� XML 锟侥碉拷锟斤拷锟斤拷锟斤拷锟�xmlelement锟叫帮拷装xmlforest锟斤拷锟斤拷式锟斤拷锟斤拷锟矫达拷锟斤拷
xmlpi(name target [, content]) 锟斤拷锟斤拷式xmlpi锟斤拷锟斤拷一锟斤拷 XML 锟斤拷锟斤拷指锟筋。锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷荩锟斤拷锟斤拷莶锟斤拷馨锟斤拷锟斤拷址锟斤拷锟斤拷锟�?>锟斤拷
锟斤拷锟接ｏ拷
SELECT xmlpi(name php, 'echo "hello world";');
xmlpi
-----------------------------
<?php echo "hello world";?>
xmlroot(xml, version text | no value [, standalone yes|no|no value]) 锟斤拷锟斤拷式xmlroot锟睫革拷一锟斤拷 XML 值锟侥革拷锟斤拷锟斤拷锟斤拷锟皆★拷锟斤拷锟街革拷锟斤拷锟揭伙拷锟斤拷姹撅拷锟斤拷锟斤拷锟斤拷婊伙拷锟斤拷诘锟侥版本锟斤拷锟斤拷锟叫碉拷值锟斤拷锟斤拷锟街革拷锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟斤拷茫锟斤拷锟斤拷锟斤拷婊伙拷锟斤拷诘锟侥讹拷锟斤拷锟斤拷锟斤拷锟叫碉拷值锟斤拷
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>
xmlagg(xml) 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷同锟斤拷锟斤拷锟斤拷xmlagg锟斤拷一锟斤拷锟桔硷拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟桔硷拷锟斤拷锟斤拷锟斤拷锟矫碉拷锟斤拷锟斤拷值锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟角筹拷锟斤拷xmlconcat锟斤拷锟斤拷锟斤拷锟斤拷锟介，锟斤拷锟剿达拷锟斤拷锟角匡拷锟叫凤拷锟斤拷锟侥讹拷锟斤拷锟斤拷锟节碉拷一锟叫的讹拷锟斤拷锟斤拷锟绞斤拷戏锟斤拷锟斤拷锟斤拷奂锟斤拷锟斤拷锟绞斤拷母锟斤拷锟斤拷锟较拷锟斤拷锟斤拷 9.20 锟斤拷锟斤拷
锟斤拷锟接ｏ拷
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/>
为锟剿撅拷锟斤拷锟斤拷锟接碉拷顺锟津，匡拷锟斤拷为锟桔硷拷锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷ORDER BY锟接句，锟斤拷锟斤拷 4.2.7 锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟界：
SELECT xmlagg(x ORDER BY y DESC) FROM test;
xmlagg
----------------------
<bar/><foo>abc</foo>
锟斤拷锟斤拷锟狡硷拷锟斤拷锟斤拷前锟侥版本锟斤拷使锟斤拷锟斤拷锟叫非憋拷准锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟截讹拷锟斤拷锟斤拷锟斤拷锟饺伙拷锟斤拷茫锟�
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
xmlagg
----------------------
<bar/><foo>abc</foo>
锟斤拷一锟斤拷锟斤拷锟斤拷锟侥憋拷锟斤拷式锟斤拷锟�xml值锟斤拷锟斤拷锟皆★拷
xml IS DOCUMENT
锟斤拷锟斤拷锟斤拷锟� XML 值锟斤拷一锟斤拷锟斤拷确锟斤拷 XML 锟侥碉拷锟斤拷锟斤拷IS DOCUMENT锟斤拷锟斤拷锟芥，锟斤拷锟斤拷锟斤拷锟斤拷蚍祷丶伲锟斤拷锟斤拷锟斤拷锟揭伙拷锟斤拷锟斤拷锟狡拷希锟斤拷锟斤拷锟斤拷锟斤拷遣锟斤拷锟轿拷锟绞憋拷锟斤拷乜铡锟斤拷牡锟斤拷锟斤拷锟斤拷锟狡拷锟街拷锟斤拷锟斤拷锟斤拷锟斤拷锟�锟斤拷 8.13 锟斤拷锟斤拷
XMLEXISTS(text PASSING [BY REF] xml [BY REF]) 锟斤拷锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷械锟� XPath 锟斤拷锟斤拷式锟斤拷锟斤拷锟轿何斤拷悖拷锟斤拷锟�xmlexists锟斤拷锟斤拷锟芥，锟斤拷锟津返回假ｏ拷锟斤拷锟斤拷锟揭伙拷锟斤拷锟斤拷锟轿拷眨锟斤拷锟斤拷锟斤拷锟轿拷眨锟斤拷锟�
锟斤拷锟接ｏ拷
SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Toronto</town><town>Ottawa</town></towns>');
xmlexists
------------
t
(1 row)
BY REF锟接撅拷锟斤拷 PostgreSQL 锟斤拷没锟斤拷锟斤拷锟矫ｏ拷锟斤拷锟斤拷为锟剿猴拷锟斤拷锟斤拷实锟街憋拷锟斤拷 SQL 一锟斤拷锟皆和硷拷锟斤拷锟皆伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟街★拷每一锟斤拷 SQL 锟斤拷准锟斤拷锟斤拷一锟斤拷BY REF锟角憋拷要锟斤拷模锟斤拷锟斤拷诙锟斤拷锟斤拷锟斤拷强锟窖★拷摹锟揭惨拷锟� SQL 锟斤拷准指锟斤拷xmlexists锟结构锟斤拷锟斤拷一锟斤拷 XQuery 锟斤拷锟斤拷式锟斤拷为锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷 PostgreSQL 目前只支锟斤拷 XPath锟斤拷锟斤拷锟斤拷 XQuery锟斤拷一锟斤拷锟接硷拷锟斤拷
xml_is_well_formed(text)xml_is_well_formed_document(text)xml_is_well_formed_content(text)
锟斤拷些锟斤拷锟斤拷锟斤拷锟揭伙拷锟�text锟斤拷锟角诧拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷 XML锟斤拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�xml_is_well_formed_document锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟斤拷牡锟斤拷锟斤拷锟�xml_is_well_formed_content锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷荨锟斤拷锟斤拷xmloption锟斤拷锟矫诧拷锟斤拷锟斤拷锟斤拷锟斤拷为DOCUMENT锟斤拷xml_is_well_formed锟斤拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟侥癸拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷貌锟斤拷锟斤拷锟斤拷锟斤拷锟轿�CONTENT锟斤拷xml_is_well_formed锟斤拷锟斤拷锟节讹拷锟斤拷锟斤拷锟斤拷锟侥癸拷锟斤拷锟斤拷锟斤拷锟斤拷味锟斤拷xml_is_well_formed锟斤拷锟节硷拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟�xml锟侥硷拷锟斤拷锟斤拷锟角凤拷锟缴癸拷锟角筹拷锟斤拷锟矫ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟节硷拷锟�XMLPARSE锟侥讹拷应锟斤拷锟斤拷锟角凤拷锟缴癸拷锟斤拷锟矫★拷
锟斤拷锟接ｏ拷
SET xmloption TO DOCUMENT;
SELECT xml_is_well_formed('<>');
xml_is_well_formed
--------------------
f
(1 row)
SELECT xml_is_well_formed('<abc/>');
xml_is_well_formed
--------------------
t
(1 row)
SET xmloption TO CONTENT;
SELECT xml_is_well_formed('abc');
xml_is_well_formed
--------------------
t
(1 row)
SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>');
xml_is_well_formed_document
-----------------------------
t
(1 row)
SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>');
xml_is_well_formed_document
-----------------------------
f
(1 row)锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟绞撅拷锟斤拷锟叫╋拷锟斤拷也锟斤拷锟斤拷锟斤拷锟街空硷拷锟角凤拷锟斤拷确锟斤拷匹锟戒。
要锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷xml锟斤拷值锟斤拷 PostgreSQL 锟结供锟剿猴拷锟斤拷xpath锟斤拷xpath_exists锟斤拷锟斤拷锟角硷拷锟斤拷 XPath 1.0 锟斤拷锟斤拷式锟斤拷
xpath(xpath, xml [, nsarray]) 锟斤拷锟斤拷xpath锟斤拷 XML 值xml锟较硷拷锟斤拷 XPath 锟斤拷锟斤拷式xpath (a text value)锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷 XML 值锟斤拷锟斤拷锟介，锟斤拷锟斤拷锟斤拷锟接︼拷诟锟� XPath 锟斤拷锟斤拷式锟斤拷锟斤拷锟侥斤拷慵拷稀锟斤拷锟斤拷锟斤拷 XPath 锟斤拷锟斤拷式锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷值锟斤拷锟斤拷锟斤拷一锟斤拷锟斤拷慵拷希锟斤拷锟斤拷岱碉拷锟揭伙拷锟斤拷锟揭辉拷氐锟斤拷锟斤拷椤�
锟节讹拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷 XML 锟侥碉拷锟斤拷锟斤拷锟斤拷兀锟斤拷锟斤拷锟斤拷锟斤拷锟揭伙拷锟斤拷锟揭伙拷锟斤拷锟斤拷元锟截★拷
锟矫猴拷锟斤拷锟斤拷选锟侥碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷锟斤拷锟街空硷拷映锟斤拷锟斤拷锟斤拷椤ｏ拷锟斤拷锟斤拷锟斤拷应锟斤拷锟斤拷一锟斤拷锟斤拷维text锟斤拷锟介，锟斤拷诙锟斤拷岢わ拷鹊锟斤拷锟�2锟斤拷锟斤拷锟斤拷应锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷椋拷锟斤拷锟矫恳伙拷锟斤拷锟斤拷筛蘸锟� 2 锟斤拷元锟斤拷锟斤拷桑锟斤拷锟矫匡拷锟斤拷锟斤拷锟斤拷锟侥碉拷一锟斤拷元锟斤拷锟斤拷锟斤拷锟街空硷拷锟斤拷锟斤拷疲锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷诙锟斤拷锟皆拷锟斤拷锟斤拷锟斤拷挚占锟斤拷 URI锟斤拷锟斤拷锟斤拷要锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷峁╋拷谋锟斤拷锟斤拷锟斤拷锟� XML 锟侥碉拷锟斤拷锟斤拷锟斤拷使锟矫碉拷锟斤拷些锟斤拷锟街空硷拷锟斤拷同锟斤拷锟斤拷锟戒话说锟斤拷锟斤拷 XML 锟侥碉拷锟叫猴拷锟斤拷xpath锟斤拷锟斤拷锟斤拷锟斤拷锟叫ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟截碉拷锟斤拷锟斤拷
锟斤拷锟接ｏ拷
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)
要锟斤拷锟斤拷默锟较ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟秸间，锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟介：
SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a>',
ARRAY[ARRAY['mydefns', 'http://example.com']]);
xpath
--------
{test}
(1 row)
xpath_exists(xpath, xml [, nsarray]) 锟斤拷锟斤拷xpath_exists锟斤拷xpath锟斤拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷式锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷欠锟斤拷锟斤拷锟斤拷锟� XPath 锟侥碉拷一 XML 值锟斤拷锟斤拷锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷值锟斤拷示锟斤拷询锟角凤拷锟斤拷锟姐。锟斤拷锟斤拷锟斤拷锟斤拷燃锟斤拷诒锟阶硷拷锟�XMLEXISTS谓锟绞ｏ拷锟斤拷锟斤拷锟斤拷锟斤拷锟结供锟剿讹拷一锟斤拷锟斤拷锟街空硷拷映锟斤拷锟斤拷锟斤拷锟街э拷帧锟�
锟斤拷锟接ｏ拷
SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);
xpath_exists
--------------
t
(1 row)
锟斤拷锟斤拷暮锟斤拷锟斤拷锟斤拷锟窖癸拷系锟斤拷锟斤拷锟斤拷锟斤拷映锟斤拷锟� XML 值锟斤拷锟斤拷锟角匡拷锟皆憋拷锟斤拷锟斤拷锟斤拷 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_xml映锟斤拷锟缴诧拷锟斤拷tbl锟斤拷锟捷碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟捷★拷regclass锟斤拷锟酵斤拷锟斤拷使锟矫筹拷锟斤拷锟斤拷潜锟绞讹拷锟斤拷锟斤拷址锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟窖★拷锟侥Ｊ斤拷薅锟斤拷锟剿拷锟斤拷拧锟�query_to_xml执锟斤拷锟缴诧拷锟斤拷query锟斤拷锟捷的诧拷询锟斤拷锟斤拷映锟斤拷锟斤拷锟斤拷锟斤拷cursor_to_xml锟斤拷cursor指锟斤拷锟斤拷锟轿憋拷锟斤拷取锟斤拷指锟斤拷锟斤拷锟斤拷锟斤拷锟叫★拷锟斤拷锟斤拷锟揭筹拷锟揭伙拷锟斤拷锟斤拷偷谋锟斤拷锟斤拷锟斤拷锟斤拷萍锟斤拷锟斤拷直锟斤拷澹拷锟轿恳伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷诖锟斤拷泄锟斤拷锟斤拷锟斤拷值锟侥★拷
锟斤拷锟�tableforest为锟劫ｏ拷锟斤拷锟斤拷锟斤拷 XML 锟侥碉拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷
<tablename>
<row>
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</row>
<row>
...
</row>
...
</tablename>锟斤拷锟�tableforest为锟芥，锟斤拷锟斤拷锟揭伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟� XML 锟斤拷锟斤拷片锟较ｏ拷
<tablename> <columnname1>data</columnname1> <columnname2>data</columnname2> </tablename> <tablename> ... </tablename> ...
锟斤拷锟矫伙拷斜锟斤拷锟斤拷锟斤拷茫锟斤拷锟接筹拷锟揭伙拷锟斤拷锟窖拷锟揭伙拷锟斤拷伪锟绞憋拷锟斤拷诘锟揭伙拷指锟绞斤拷锟绞癸拷么锟�table锟斤拷锟节第讹拷锟街革拷式锟斤拷使锟斤拷row锟斤拷
锟解几锟街革拷式锟斤拷选锟斤拷锟斤拷锟矫伙拷锟斤拷锟斤拷锟斤拷锟斤拷一锟街革拷式锟斤拷一锟斤拷锟斤拷确锟斤拷 XML 锟侥碉拷锟斤拷锟斤拷锟节很讹拷应锟斤拷锟叫讹拷锟斤拷锟斤拷要锟斤拷锟斤拷锟斤拷锟斤拷值要锟斤拷锟斤拷锟斤拷为一锟斤拷锟侥碉拷锟斤拷锟节讹拷锟街革拷式锟斤拷cursor_to_xml锟斤拷锟斤拷锟叫革拷锟斤拷锟矫★拷前锟斤拷锟斤拷锟桔的诧拷锟斤拷 XML 锟斤拷锟捷的猴拷锟斤拷锟斤拷锟截憋拷锟斤拷xmlelement锟斤拷锟斤拷锟皆憋拷锟斤拷锟斤拷锟窖斤拷锟斤拷薷某煞锟斤拷锟斤拷没锟斤拷锟揭拷锟�
锟斤拷锟斤拷值锟结被锟斤拷前锟侥的猴拷锟斤拷xmlelement锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷同锟斤拷锟斤拷映锟戒。
锟斤拷锟斤拷nulls锟斤拷锟斤拷锟斤拷值锟角凤拷岜伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟叫★拷锟斤拷锟轿拷妫拷锟斤拷械目锟街碉拷锟斤拷锟绞疚拷锟�
<columnname xsi:nil="true"/>
锟斤拷锟斤拷xsi锟斤拷 XML 模式实锟斤拷锟斤拷 XML 锟斤拷锟街空硷拷前缀锟斤拷一锟斤拷锟斤拷锟绞碉拷锟斤拷锟街空硷拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟诫到锟斤拷锟街碉拷小锟斤拷锟斤拷为锟劫ｏ拷锟斤拷锟斤拷锟斤拷值锟斤拷锟叫斤拷锟斤拷锟斤拷锟斤拷锟斤拷泻锟斤拷缘锟斤拷锟�
锟斤拷锟斤拷targetns指锟斤拷锟斤拷要锟侥斤拷锟斤拷锟� XML 锟斤拷锟街空间。锟斤拷锟矫伙拷锟斤拷锟揭拷锟斤拷囟锟斤拷锟斤拷挚占洌拷锟斤拷岽拷锟揭伙拷锟斤拷沾锟斤拷锟�
锟斤拷锟斤拷暮锟斤拷锟斤拷锟斤拷锟� 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)
锟斤拷锟斤拷要锟斤拷锟斤拷锟斤拷同锟侥诧拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟狡ワ拷锟斤拷 XML 锟斤拷锟斤拷映锟斤拷锟� XML 模式锟侥碉拷锟斤拷
锟斤拷锟斤拷暮锟斤拷锟斤拷锟斤拷锟� XML 锟斤拷锟斤拷映锟斤拷投锟接︼拷锟� XML 模式锟斤拷锟斤拷锟窖诧拷锟斤拷锟侥斤拷锟斤拷锟斤拷锟斤拷锟揭伙拷锟斤拷锟斤拷一锟斤拷锟侥碉拷锟斤拷锟斤拷森锟街ｏ拷锟叫★拷锟斤拷要锟斤拷锟皆帮拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟侥斤拷锟斤拷锟斤拷锟斤拷欠浅锟斤拷锟斤拷茫锟�
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)
注锟斤拷锟斤拷些锟斤拷锟斤拷锟斤拷锟杰诧拷锟斤拷锟杰讹拷锟斤拷锟捷ｏ拷锟斤拷锟角讹拷锟斤拷要锟斤拷锟节达拷锟叫憋拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟侥Ｊ斤拷锟斤拷锟斤拷菘锟斤拷锟斤拷锟斤拷映锟斤拷时锟斤拷锟斤拷锟皆匡拷锟角分憋拷映锟斤拷每一锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷通锟斤拷一锟斤拷锟轿憋拷锟斤拷映锟戒。
一锟斤拷模式锟斤拷锟斤拷映锟斤拷慕锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷
<schemaname> table1-mapping table2-mapping ... </schemaname>
锟斤拷锟斤拷一锟斤拷锟斤拷映锟斤拷母锟绞饺★拷锟斤拷锟斤拷锟斤拷慕锟斤拷偷锟�tableforest锟斤拷锟斤拷锟斤拷
一锟斤拷锟斤拷锟捷匡拷锟斤拷锟斤拷映锟斤拷慕锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷
<dbname> <schema1name> ... </schema1name> <schema2name> ... </schema2name> ... </dbname>
锟斤拷锟叫碉拷模式映锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷
锟斤拷为一锟斤拷使锟斤拷锟斤拷些锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷樱锟�图 9-1展示锟斤拷一锟斤拷 XSLT 锟斤拷式锟斤拷锟斤拷锟斤拷锟斤拷table_to_xml_and_xmlschema锟斤拷锟斤拷锟阶拷锟轿伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷莸谋锟狡阶★拷锟� HTML 锟侥碉拷锟斤拷锟斤拷一锟斤拷锟斤拷锟狡的凤拷式锟斤拷锟斤拷些锟斤拷锟斤拷锟侥斤拷锟斤拷锟斤拷员锟阶拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟� XML 锟侥革拷式锟斤拷
图 9-1. 转锟斤拷 SQL/XML 锟斤拷锟斤拷锟� HTML 锟斤拷 XSLT 锟斤拷式锟斤拷
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.w3.org/1999/xhtml"
>
<xsl:output method="xml"
doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"
doctype-public="-//W3C/DTD XHTML 1.0 Strict//EN"
indent="yes"/>
<xsl:template match="/*">
<xsl:variable name="schema" select="//xsd:schema"/>
<xsl:variable name="tabletypename"
select="$schema/xsd:element[@name=name(current())]/@type"/>
<xsl:variable name="rowtypename"
select="$schema/xsd:complexType[@name=$tabletypename]/xsd:sequence/xsd:element[@name='row']/@type"/>
<html>
<head>
<title><xsl:value-of select="name(current())"/></title>
</head>
<body>
<table>
<tr>
<xsl:for-each select="$schema/xsd:complexType[@name=$rowtypename]/xsd:sequence/xsd:element/@name">
<th><xsl:value-of select="."/></th>
</xsl:for-each>
</tr>
<xsl:for-each select="row">
<tr>
<xsl:for-each select="*">
<td><xsl:value-of select="."/></td>
</xsl:for-each>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>