5 INSERT INTO xmltest VALUES (1, '<value>one</value>');
6 INSERT INTO xmltest VALUES (2, '<value>two</value>');
7 INSERT INTO xmltest VALUES (3, '<wrong');
8 ERROR: invalid XML content
9 LINE 1: INSERT INTO xmltest VALUES (3, '<wrong');
11 DETAIL: line 1: Couldn't find end of Start Tag wrong line 1
12 SELECT * FROM xmltest;
14 ----+--------------------
15 1 | <value>one</value>
16 2 | <value>two</value>
19 SELECT xmlcomment('test');
25 SELECT xmlcomment('-test');
31 SELECT xmlcomment('test-');
32 ERROR: invalid XML comment
33 SELECT xmlcomment('--test');
34 ERROR: invalid XML comment
35 SELECT xmlcomment('te st');
41 SELECT xmlconcat(xmlcomment('hello'),
42 xmlelement(NAME qux, 'foo'),
45 ----------------------------------------
46 <!--hello--><qux>foo</qux><!--world-->
49 SELECT xmlconcat('hello', 'you');
55 SELECT xmlconcat(1, 2);
56 ERROR: argument of XMLCONCAT must be type xml, not type integer
57 LINE 1: SELECT xmlconcat(1, 2);
59 SELECT xmlconcat('bad', '<syntax');
60 ERROR: invalid XML content
61 LINE 1: SELECT xmlconcat('bad', '<syntax');
63 DETAIL: line 1: Couldn't find end of Start Tag syntax line 1
64 SELECT xmlconcat('<foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
70 SELECT xmlconcat('<?xml version="1.1"?><foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
72 -----------------------------------
73 <?xml version="1.1"?><foo/><bar/>
76 SELECT xmlconcat(NULL);
82 SELECT xmlconcat(NULL, NULL);
88 SELECT xmlelement(name element,
89 xmlattributes (1 as one, 'deuce' as two),
92 ------------------------------------------------
93 <element one="1" two="deuce">content</element>
96 SELECT xmlelement(name element,
97 xmlattributes ('unnamed and wrong'));
98 ERROR: unnamed XML attribute value must be a column reference
99 LINE 2: xmlattributes ('unnamed and wrong'));
101 SELECT xmlelement(name element, xmlelement(name nested, 'stuff'));
103 -------------------------------------------
104 <element><nested>stuff</nested></element>
107 SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp;
109 ----------------------------------------------------------------------
110 <employee><name>sharon</name><age>25</age><pay>1000</pay></employee>
111 <employee><name>sam</name><age>30</age><pay>2000</pay></employee>
112 <employee><name>bill</name><age>20</age><pay>1000</pay></employee>
113 <employee><name>jeff</name><age>23</age><pay>600</pay></employee>
114 <employee><name>cim</name><age>30</age><pay>400</pay></employee>
115 <employee><name>linda</name><age>19</age><pay>100</pay></employee>
118 SELECT xmlelement(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a));
119 ERROR: XML attribute name "a" appears more than once
120 LINE 1: ...ment(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a));
122 SELECT xmlelement(name num, 37);
128 SELECT xmlelement(name foo, text 'bar');
134 SELECT xmlelement(name foo, xml 'bar');
140 SELECT xmlelement(name foo, text 'b<a/>r');
142 -------------------------
143 <foo>b<a/>r</foo>
146 SELECT xmlelement(name foo, xml 'b<a/>r');
152 SELECT xmlelement(name foo, array[1, 2, 3]);
154 -------------------------------------------------------------------------
155 <foo><element>1</element><element>2</element><element>3</element></foo>
158 SET xmlbinary TO base64;
159 SELECT xmlelement(name foo, bytea 'bar');
165 SET xmlbinary TO hex;
166 SELECT xmlelement(name foo, bytea 'bar');
172 SELECT xmlelement(name foo, xmlattributes(true as bar));
178 SELECT xmlelement(name foo, xmlattributes('2009-04-09 00:24:37'::timestamp as bar));
180 ----------------------------------
181 <foo bar="2009-04-09T00:24:37"/>
184 SELECT xmlelement(name foo, xmlattributes('infinity'::timestamp as bar));
185 ERROR: timestamp out of range
186 DETAIL: XML does not support infinite timestamp values.
187 SELECT xmlelement(name foo, xmlattributes('<>&"''' as funny, xml 'b<a/>r' as funnier));
189 ------------------------------------------------------------
190 <foo funny="<>&"'" funnier="b<a/>r"/>
193 SELECT xmlparse(content '');
199 SELECT xmlparse(content ' ');
205 SELECT xmlparse(content 'abc');
211 SELECT xmlparse(content '<abc>x</abc>');
217 SELECT xmlparse(content '<invalidentity>&</invalidentity>');
218 ERROR: invalid XML content
219 DETAIL: line 1: xmlParseEntityRef: no name
220 <invalidentity>&</invalidentity>
222 line 1: chunk is not well balanced
223 SELECT xmlparse(content '<undefinedentity>&idontexist;</undefinedentity>');
224 ERROR: invalid XML content
225 DETAIL: line 1: Entity 'idontexist' not defined
226 <undefinedentity>&idontexist;</undefinedentity>
228 line 1: chunk is not well balanced
229 SELECT xmlparse(content '<invalidns xmlns=''<''/>');
231 ---------------------------
232 <invalidns xmlns='<'/>
235 SELECT xmlparse(content '<relativens xmlns=''relative''/>');
237 --------------------------------
238 <relativens xmlns='relative'/>
241 SELECT xmlparse(content '<twoerrors>&idontexist;</unbalanced>');
242 ERROR: invalid XML content
243 DETAIL: line 1: Entity 'idontexist' not defined
244 <twoerrors>&idontexist;</unbalanced>
246 line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced
247 line 1: chunk is not well balanced
248 SELECT xmlparse(content '<nosuchprefix:tag/>');
250 ---------------------
254 SELECT xmlparse(document ' ');
255 ERROR: invalid XML document
256 DETAIL: line 1: Start tag expected, '<' not found
257 SELECT xmlparse(document 'abc');
258 ERROR: invalid XML document
259 DETAIL: line 1: Start tag expected, '<' not found
262 SELECT xmlparse(document '<abc>x</abc>');
268 SELECT xmlparse(document '<invalidentity>&</abc>');
269 ERROR: invalid XML document
270 DETAIL: line 1: xmlParseEntityRef: no name
271 <invalidentity>&</abc>
273 line 1: Opening and ending tag mismatch: invalidentity line 1 and abc
274 SELECT xmlparse(document '<undefinedentity>&idontexist;</abc>');
275 ERROR: invalid XML document
276 DETAIL: line 1: Entity 'idontexist' not defined
277 <undefinedentity>&idontexist;</abc>
279 line 1: Opening and ending tag mismatch: undefinedentity line 1 and abc
280 SELECT xmlparse(document '<invalidns xmlns=''<''/>');
282 ---------------------------
283 <invalidns xmlns='<'/>
286 SELECT xmlparse(document '<relativens xmlns=''relative''/>');
288 --------------------------------
289 <relativens xmlns='relative'/>
292 SELECT xmlparse(document '<twoerrors>&idontexist;</unbalanced>');
293 ERROR: invalid XML document
294 DETAIL: line 1: Entity 'idontexist' not defined
295 <twoerrors>&idontexist;</unbalanced>
297 line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced
298 SELECT xmlparse(document '<nosuchprefix:tag/>');
300 ---------------------
304 SELECT xmlpi(name foo);
310 SELECT xmlpi(name xml);
311 ERROR: invalid XML processing instruction
312 DETAIL: XML processing instruction target name cannot be "xml".
313 SELECT xmlpi(name xmlstuff);
319 SELECT xmlpi(name foo, 'bar');
325 SELECT xmlpi(name foo, 'in?>valid');
326 ERROR: invalid XML processing instruction
327 DETAIL: XML processing instruction cannot contain "?>".
328 SELECT xmlpi(name foo, null);
334 SELECT xmlpi(name xml, null);
335 ERROR: invalid XML processing instruction
336 DETAIL: XML processing instruction target name cannot be "xml".
337 SELECT xmlpi(name xmlstuff, null);
343 SELECT xmlpi(name "xml-stylesheet", 'href="mystyle.css" type="text/css"');
345 -------------------------------------------------------
346 <?xml-stylesheet href="mystyle.css" type="text/css"?>
349 SELECT xmlpi(name foo, ' bar');
355 SELECT xmlroot(xml '<foo/>', version no value, standalone no value);
361 SELECT xmlroot(xml '<foo/>', version '2.0');
363 -----------------------------
364 <?xml version="2.0"?><foo/>
367 SELECT xmlroot(xml '<foo/>', version no value, standalone yes);
369 ----------------------------------------------
370 <?xml version="1.0" standalone="yes"?><foo/>
373 SELECT xmlroot(xml '<?xml version="1.1"?><foo/>', version no value, standalone yes);
375 ----------------------------------------------
376 <?xml version="1.0" standalone="yes"?><foo/>
379 SELECT xmlroot(xmlroot(xml '<foo/>', version '1.0'), version '1.1', standalone no);
381 ---------------------------------------------
382 <?xml version="1.1" standalone="no"?><foo/>
385 SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no);
387 ---------------------------------------------
388 <?xml version="1.0" standalone="no"?><foo/>
391 SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no value);
397 SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value);
399 ----------------------------------------------
400 <?xml version="1.0" standalone="yes"?><foo/>
419 ------------------------------------------------------------------------------------------
420 <?xml version="1.0" standalone="yes"?><gazonk name="val" num="2"><qux>foo</qux></gazonk>
423 SELECT xmlserialize(content data as character varying(20)) FROM xmltest;
430 SELECT xmlserialize(content 'good' as char(10));
436 SELECT xmlserialize(document 'bad' as text);
437 ERROR: not an XML document
438 SELECT xml '<foo>bar</foo>' IS DOCUMENT;
444 SELECT xml '<foo>bar</foo><bar>foo</bar>' IS DOCUMENT;
450 SELECT xml '<abc/>' IS NOT DOCUMENT;
456 SELECT xml 'abc' IS NOT DOCUMENT;
462 SELECT '<>' IS NOT DOCUMENT;
463 ERROR: invalid XML content
464 LINE 1: SELECT '<>' IS NOT DOCUMENT;
466 DETAIL: line 1: StartTag: invalid element name
469 SELECT xmlagg(data) FROM xmltest;
471 --------------------------------------
472 <value>one</value><value>two</value>
475 SELECT xmlagg(data) FROM xmltest WHERE id > 10;
481 SELECT xmlelement(name employees, xmlagg(xmlelement(name name, name))) FROM emp;
483 --------------------------------------------------------------------------------------------------------------------------------
484 <employees><name>sharon</name><name>sam</name><name>bill</name><name>jeff</name><name>cim</name><name>linda</name></employees>
487 -- Check mapping SQL identifier to XML name
488 SELECT xmlpi(name ":::_xml_abc135.%-&_");
490 -------------------------------------------------
491 <?_x003A_::_x005F_xml_abc135._x0025_-_x0026__?>
494 SELECT xmlpi(name "123");
500 PREPARE foo (xml) AS SELECT xmlconcat('<foo/>', $1);
501 SET XML OPTION DOCUMENT;
502 EXECUTE foo ('<bar/>');
509 ERROR: invalid XML document
510 LINE 1: EXECUTE foo ('bad');
512 DETAIL: line 1: Start tag expected, '<' not found
515 SET XML OPTION CONTENT;
516 EXECUTE foo ('<bar/>');
522 EXECUTE foo ('good');
528 -- Test backwards parsing
529 CREATE VIEW xmlview1 AS SELECT xmlcomment('test');
530 CREATE VIEW xmlview2 AS SELECT xmlconcat('hello', 'you');
531 CREATE VIEW xmlview3 AS SELECT xmlelement(name element, xmlattributes (1 as ":one:", 'deuce' as two), 'content&');
532 CREATE VIEW xmlview4 AS SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp;
533 CREATE VIEW xmlview5 AS SELECT xmlparse(content '<abc>x</abc>');
534 CREATE VIEW xmlview6 AS SELECT xmlpi(name foo, 'bar');
535 CREATE VIEW xmlview7 AS SELECT xmlroot(xml '<foo/>', version no value, standalone yes);
536 CREATE VIEW xmlview8 AS SELECT xmlserialize(content 'good' as char(10));
537 CREATE VIEW xmlview9 AS SELECT xmlserialize(content 'good' as text);
538 SELECT table_name, view_definition FROM information_schema.views
539 WHERE table_name LIKE 'xmlview%' ORDER BY 1;
540 table_name | view_definition
541 ------------+-------------------------------------------------------------------------------------------------------------------
542 xmlview1 | SELECT xmlcomment('test'::text) AS xmlcomment;
543 xmlview2 | SELECT XMLCONCAT('hello'::xml, 'you'::xml) AS "xmlconcat";
544 xmlview3 | SELECT XMLELEMENT(NAME element, XMLATTRIBUTES(1 AS ":one:", 'deuce' AS two), 'content&') AS "xmlelement";
545 xmlview4 | SELECT XMLELEMENT(NAME employee, XMLFOREST(emp.name AS name, emp.age AS age, emp.salary AS pay)) AS "xmlelement"+
547 xmlview5 | SELECT XMLPARSE(CONTENT '<abc>x</abc>'::text STRIP WHITESPACE) AS "xmlparse";
548 xmlview6 | SELECT XMLPI(NAME foo, 'bar'::text) AS "xmlpi";
549 xmlview7 | SELECT XMLROOT('<foo/>'::xml, VERSION NO VALUE, STANDALONE YES) AS "xmlroot";
550 xmlview8 | SELECT (XMLSERIALIZE(CONTENT 'good'::xml AS character(10)))::character(10) AS "xmlserialize";
551 xmlview9 | SELECT XMLSERIALIZE(CONTENT 'good'::xml AS text) AS "xmlserialize";
554 -- Text XPath expressions evaluation
555 SELECT xpath('/value', data) FROM xmltest;
557 ----------------------
562 SELECT xpath(NULL, NULL) IS NULL FROM xmltest;
569 SELECT xpath('', '<!-- error -->');
570 ERROR: empty XPath expression
571 CONTEXT: SQL function "xpath" statement 1
572 SELECT xpath('//text()', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>');
578 SELECT xpath('//loc:piece/@id', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
584 SELECT xpath('//loc:piece', '<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
586 ------------------------------------------------------------------------------------------------------------------------------------------------
587 {"<local:piece xmlns:local=\"http://127.0.0.1\" id=\"1\">number one</local:piece>","<local:piece xmlns:local=\"http://127.0.0.1\" id=\"2\"/>"}
590 SELECT xpath('//loc:piece', '<local:data xmlns:local="http://127.0.0.1" xmlns="http://127.0.0.2"><local:piece id="1"><internal>number one</internal><internal2/></local:piece><local:piece id="2" /></local:data>', ARRAY[ARRAY['loc', 'http://127.0.0.1']]);
592 --------------------------------------------------------------------------------------
593 {"<local:piece xmlns:local=\"http://127.0.0.1\" xmlns=\"http://127.0.0.2\" id=\"1\">+
594 <internal>number one</internal> +
596 </local:piece>","<local:piece xmlns:local=\"http://127.0.0.1\" id=\"2\"/>"}
599 SELECT xpath('//b', '<a>one <b>two</b> three <b>etc</b></a>');
601 -------------------------
602 {<b>two</b>,<b>etc</b>}
605 SELECT xpath('//text()', '<root><</root>');
611 SELECT xpath('//@value', '<root value="<"/>');
617 SELECT xpath('''<<invalid>>''', '<root/>');
619 ---------------------------
620 {<<invalid>>}
623 SELECT xpath('count(//*)', '<root><sub/><sub/></root>');
629 SELECT xpath('count(//*)=0', '<root><sub/><sub/></root>');
635 SELECT xpath('count(//*)=3', '<root><sub/><sub/></root>');
641 SELECT xpath('name(/*)', '<root><sub/><sub/></root>');
647 SELECT xpath('/nosuchtag', '<root/>');
653 -- Test xmlexists and xpath_exists
654 SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>');
660 SELECT xmlexists('//town[text() = ''Cwmbran'']' PASSING BY REF '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>');
666 SELECT xmlexists('count(/nosuchtag)' PASSING BY REF '<root/>');
672 SELECT xpath_exists('//town[text() = ''Toronto'']','<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'::xml);
678 SELECT xpath_exists('//town[text() = ''Cwmbran'']','<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'::xml);
684 SELECT xpath_exists('count(/nosuchtag)', '<root/>'::xml);
690 INSERT INTO xmltest VALUES (4, '<menu><beers><name>Budvar</name><cost>free</cost><name>Carling</name><cost>lots</cost></beers></menu>'::xml);
691 INSERT INTO xmltest VALUES (5, '<menu><beers><name>Molson</name><cost>free</cost><name>Carling</name><cost>lots</cost></beers></menu>'::xml);
692 INSERT INTO xmltest VALUES (6, '<myns:menu xmlns:myns="http://myns.com"><myns:beers><myns:name>Budvar</myns:name><myns:cost>free</myns:cost><myns:name>Carling</myns:name><myns:cost>lots</myns:cost></myns:beers></myns:menu>'::xml);
693 INSERT INTO xmltest VALUES (7, '<myns:menu xmlns:myns="http://myns.com"><myns:beers><myns:name>Molson</myns:name><myns:cost>free</myns:cost><myns:name>Carling</myns:name><myns:cost>lots</myns:cost></myns:beers></myns:menu>'::xml);
694 SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING data);
700 SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING BY REF data BY REF);
706 SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers' PASSING BY REF data);
712 SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers/name[text() = ''Molson'']' PASSING BY REF data);
718 SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beer',data);
724 SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers',data);
730 SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers/name[text() = ''Molson'']',data);
736 SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beer',data,ARRAY[ARRAY['myns','http://myns.com']]);
742 SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers',data,ARRAY[ARRAY['myns','http://myns.com']]);
748 SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers/myns:name[text() = ''Molson'']',data,ARRAY[ARRAY['myns','http://myns.com']]);
754 CREATE TABLE query ( expr TEXT );
755 INSERT INTO query VALUES ('/menu/beers/cost[text() = ''lots'']');
756 SELECT COUNT(id) FROM xmltest, query WHERE xmlexists(expr PASSING BY REF data);
762 -- Test xml_is_well_formed and variants
763 SELECT xml_is_well_formed_document('<foo>bar</foo>');
764 xml_is_well_formed_document
765 -----------------------------
769 SELECT xml_is_well_formed_document('abc');
770 xml_is_well_formed_document
771 -----------------------------
775 SELECT xml_is_well_formed_content('<foo>bar</foo>');
776 xml_is_well_formed_content
777 ----------------------------
781 SELECT xml_is_well_formed_content('abc');
782 xml_is_well_formed_content
783 ----------------------------
787 SET xmloption TO DOCUMENT;
788 SELECT xml_is_well_formed('abc');
794 SELECT xml_is_well_formed('<>');
800 SELECT xml_is_well_formed('<abc/>');
806 SELECT xml_is_well_formed('<foo>bar</foo>');
812 SELECT xml_is_well_formed('<foo>bar</foo');
818 SELECT xml_is_well_formed('<foo><bar>baz</foo>');
824 SELECT xml_is_well_formed('<local:data xmlns:local="http://127.0.0.1"><local:piece id="1">number one</local:piece><local:piece id="2" /></local:data>');
830 SELECT xml_is_well_formed('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>');
836 SELECT xml_is_well_formed('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>');
842 SELECT xml_is_well_formed('<invalidentity>&</abc>');
848 SELECT xml_is_well_formed('<undefinedentity>&idontexist;</abc>');
854 SELECT xml_is_well_formed('<invalidns xmlns=''<''/>');
860 SELECT xml_is_well_formed('<relativens xmlns=''relative''/>');
866 SELECT xml_is_well_formed('<twoerrors>&idontexist;</unbalanced>');
872 SET xmloption TO CONTENT;
873 SELECT xml_is_well_formed('abc');
879 -- Since xpath() deals with namespaces, it's a bit stricter about
880 -- what's well-formed and what's not. If we don't obey these rules
881 -- (i.e. ignore namespace-related errors from libxml), xpath()
882 -- fails in subtle ways. The following would for example produce
884 -- <invalidns xmlns='<'/>
885 -- which is invalid because '<' may not appear un-escaped in
887 -- Since different libxml versions emit slightly different
888 -- error messages, we suppress the DETAIL in this test.
890 SELECT xpath('/*', '<invalidns xmlns=''<''/>');
891 ERROR: could not parse XML document
892 \set VERBOSITY default
893 -- Again, the XML isn't well-formed for namespace purposes
894 SELECT xpath('/*', '<nosuchprefix:tag/>');
895 ERROR: could not parse XML document
896 DETAIL: line 1: Namespace prefix nosuchprefix on tag is not defined
899 CONTEXT: SQL function "xpath" statement 1
900 -- XPath deprecates relative namespaces, but they're not supposed to
901 -- throw an error, only a warning.
902 SELECT xpath('/*', '<relativens xmlns=''relative''/>');
903 WARNING: line 1: xmlns: URI relative is not absolute
904 <relativens xmlns='relative'/>
907 --------------------------------------
908 {"<relativens xmlns=\"relative\"/>"}
911 -- External entity references should not leak filesystem information.
912 SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/passwd">]><foo>&c;</foo>');
914 -----------------------------------------------------------------
915 <!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/passwd">]><foo>&c;</foo>
918 SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/no.such.file">]><foo>&c;</foo>');
920 -----------------------------------------------------------------------
921 <!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/no.such.file">]><foo>&c;</foo>
924 -- This might or might not load the requested DTD, but it mustn't throw error.
925 SELECT XMLPARSE(DOCUMENT '<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter>');
927 ------------------------------------------------------------------------------------------------------------------------------------------------------
928 <!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN" "http://www.oasis-open.org/docbook/xml/4.1.2/docbookx.dtd"><chapter> </chapter>
932 CREATE TABLE xmldata(data xml);
933 INSERT INTO xmldata VALUES('<ROWS>
935 <COUNTRY_ID>AU</COUNTRY_ID>
936 <COUNTRY_NAME>Australia</COUNTRY_NAME>
937 <REGION_ID>3</REGION_ID>
940 <COUNTRY_ID>CN</COUNTRY_ID>
941 <COUNTRY_NAME>China</COUNTRY_NAME>
942 <REGION_ID>3</REGION_ID>
945 <COUNTRY_ID>HK</COUNTRY_ID>
946 <COUNTRY_NAME>HongKong</COUNTRY_NAME>
947 <REGION_ID>3</REGION_ID>
950 <COUNTRY_ID>IN</COUNTRY_ID>
951 <COUNTRY_NAME>India</COUNTRY_NAME>
952 <REGION_ID>3</REGION_ID>
955 <COUNTRY_ID>JP</COUNTRY_ID>
956 <COUNTRY_NAME>Japan</COUNTRY_NAME>
957 <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
960 <COUNTRY_ID>SG</COUNTRY_ID>
961 <COUNTRY_NAME>Singapore</COUNTRY_NAME>
962 <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE>
965 -- XMLTABLE with columns
967 FROM (SELECT data FROM xmldata) x,
968 LATERAL XMLTABLE('/ROWS/ROW'
970 COLUMNS id int PATH '@id',
972 country_name text PATH 'COUNTRY_NAME' NOT NULL,
973 country_id text PATH 'COUNTRY_ID',
974 region_id int PATH 'REGION_ID',
975 size float PATH 'SIZE',
976 unit text PATH 'SIZE/@unit',
977 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
978 id | _id | country_name | country_id | region_id | size | unit | premier_name
979 ----+-----+--------------+------------+-----------+------+------+---------------
980 1 | 1 | Australia | AU | 3 | | | not specified
981 2 | 2 | China | CN | 3 | | | not specified
982 3 | 3 | HongKong | HK | 3 | | | not specified
983 4 | 4 | India | IN | 3 | | | not specified
984 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
985 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
988 CREATE VIEW xmltableview1 AS SELECT xmltable.*
989 FROM (SELECT data FROM xmldata) x,
990 LATERAL XMLTABLE('/ROWS/ROW'
992 COLUMNS id int PATH '@id',
994 country_name text PATH 'COUNTRY_NAME' NOT NULL,
995 country_id text PATH 'COUNTRY_ID',
996 region_id int PATH 'REGION_ID',
997 size float PATH 'SIZE',
998 unit text PATH 'SIZE/@unit',
999 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
1000 SELECT * FROM xmltableview1;
1001 id | _id | country_name | country_id | region_id | size | unit | premier_name
1002 ----+-----+--------------+------------+-----------+------+------+---------------
1003 1 | 1 | Australia | AU | 3 | | | not specified
1004 2 | 2 | China | CN | 3 | | | not specified
1005 3 | 3 | HongKong | HK | 3 | | | not specified
1006 4 | 4 | India | IN | 3 | | | not specified
1007 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
1008 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
1012 CREATE OR REPLACE VIEW public.xmltableview1 AS
1013 SELECT "xmltable".id,
1015 "xmltable".country_name,
1016 "xmltable".country_id,
1017 "xmltable".region_id,
1020 "xmltable".premier_name
1021 FROM ( SELECT xmldata.data
1023 LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
1024 EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1;
1026 -----------------------------------------
1028 -> Seq Scan on xmldata
1029 -> Table Function Scan on "xmltable"
1032 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1;
1034 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1036 Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
1037 -> Seq Scan on public.xmldata
1038 Output: xmldata.data
1039 -> Table Function Scan on "xmltable"
1040 Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
1041 Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
1044 -- XMLNAMESPACES tests
1045 SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
1047 PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
1048 COLUMNS a int PATH 'zz:a');
1054 CREATE VIEW xmltableview2 AS SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz),
1056 PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
1057 COLUMNS a int PATH 'zz:a');
1058 SELECT * FROM xmltableview2;
1064 SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'),
1066 PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>'
1067 COLUMNS a int PATH 'a');
1068 ERROR: DEFAULT namespace is not supported
1069 -- used in prepare statements
1072 FROM (SELECT data FROM xmldata) x,
1073 LATERAL XMLTABLE('/ROWS/ROW'
1075 COLUMNS id int PATH '@id',
1077 country_name text PATH 'COUNTRY_NAME' NOT NULL,
1078 country_id text PATH 'COUNTRY_ID',
1079 region_id int PATH 'REGION_ID',
1080 size float PATH 'SIZE',
1081 unit text PATH 'SIZE/@unit',
1082 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
1084 id | _id | country_name | country_id | region_id | size | unit | premier_name
1085 ----+-----+--------------+------------+-----------+------+------+---------------
1086 1 | 1 | Australia | AU | 3 | | | not specified
1087 2 | 2 | China | CN | 3 | | | not specified
1088 3 | 3 | HongKong | HK | 3 | | | not specified
1089 4 | 4 | India | IN | 3 | | | not specified
1090 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
1091 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
1094 SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int);
1095 COUNTRY_NAME | REGION_ID
1096 --------------+-----------
1101 SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY, "COUNTRY_NAME" text, "REGION_ID" int);
1102 id | COUNTRY_NAME | REGION_ID
1103 ----+--------------+-----------
1108 SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int);
1109 id | COUNTRY_NAME | REGION_ID
1110 ----+--------------+-----------
1115 SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id');
1122 SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY);
1129 SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH '.');
1130 id | COUNTRY_NAME | REGION_ID | rawdata
1131 ----+--------------+-----------+------------------------------------------------------------------
1132 4 | India | 3 | <ROW id="4"> +
1133 | | | <COUNTRY_ID>IN</COUNTRY_ID> +
1134 | | | <COUNTRY_NAME>India</COUNTRY_NAME> +
1135 | | | <REGION_ID>3</REGION_ID> +
1137 5 | Japan | 3 | <ROW id="5"> +
1138 | | | <COUNTRY_ID>JP</COUNTRY_ID> +
1139 | | | <COUNTRY_NAME>Japan</COUNTRY_NAME> +
1140 | | | <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>+
1144 SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id', "COUNTRY_NAME" text, "REGION_ID" int, rawdata xml PATH './*');
1145 id | COUNTRY_NAME | REGION_ID | rawdata
1146 ----+--------------+-----------+-----------------------------------------------------------------------------------------------------------------------------
1147 4 | India | 3 | <COUNTRY_ID>IN</COUNTRY_ID><COUNTRY_NAME>India</COUNTRY_NAME><REGION_ID>3</REGION_ID>
1148 5 | Japan | 3 | <COUNTRY_ID>JP</COUNTRY_ID><COUNTRY_NAME>Japan</COUNTRY_NAME><REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>
1151 SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?> <!--z--> bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text);
1157 SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?> <!--z--> bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text PATH 'element/text()'); -- should fail
1158 ERROR: more than one value returned by column XPath expression
1160 select * from xmltable('r' passing '<d><r><c><![CDATA[<hello> &"<>!<a>foo</a>]]></c></r><r><c>2</c></r></d>' columns c text);
1162 -------------------------
1163 <hello> &"<>!<a>foo</a>
1167 -- XML builtin entities
1168 SELECT * FROM xmltable('/x/a' PASSING '<x><a><ent>'</ent></a><a><ent>"</ent></a><a><ent>&</ent></a><a><ent><</ent></a><a><ent>></ent></a></x>' COLUMNS ent text);
1178 SELECT * FROM xmltable('/x/a' PASSING '<x><a><ent>'</ent></a><a><ent>"</ent></a><a><ent>&</ent></a><a><ent><</ent></a><a><ent>></ent></a></x>' COLUMNS ent xml);
1188 EXPLAIN (VERBOSE, COSTS OFF)
1190 FROM (SELECT data FROM xmldata) x,
1191 LATERAL XMLTABLE('/ROWS/ROW'
1193 COLUMNS id int PATH '@id',
1195 country_name text PATH 'COUNTRY_NAME' NOT NULL,
1196 country_id text PATH 'COUNTRY_ID',
1197 region_id int PATH 'REGION_ID',
1198 size float PATH 'SIZE',
1199 unit text PATH 'SIZE/@unit',
1200 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
1202 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1204 Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
1205 -> Seq Scan on public.xmldata
1206 Output: xmldata.data
1207 -> Table Function Scan on "xmltable"
1208 Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
1209 Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
1213 SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
1214 COUNTRY_NAME | REGION_ID
1215 --------------+-----------
1219 EXPLAIN (VERBOSE, COSTS OFF)
1220 SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int) WHERE "COUNTRY_NAME" = 'Japan';
1222 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1224 Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
1225 -> Seq Scan on public.xmldata
1226 Output: xmldata.data
1227 -> Table Function Scan on "xmltable"
1228 Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID"
1229 Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer)
1230 Filter: ("xmltable"."COUNTRY_NAME" = 'Japan'::text)
1233 -- should to work with more data
1234 INSERT INTO xmldata VALUES('<ROWS>
1236 <COUNTRY_ID>CZ</COUNTRY_ID>
1237 <COUNTRY_NAME>Czech Republic</COUNTRY_NAME>
1238 <REGION_ID>2</REGION_ID><PREMIER_NAME>Milos Zeman</PREMIER_NAME>
1241 <COUNTRY_ID>DE</COUNTRY_ID>
1242 <COUNTRY_NAME>Germany</COUNTRY_NAME>
1243 <REGION_ID>2</REGION_ID>
1246 <COUNTRY_ID>FR</COUNTRY_ID>
1247 <COUNTRY_NAME>France</COUNTRY_NAME>
1248 <REGION_ID>2</REGION_ID>
1251 INSERT INTO xmldata VALUES('<ROWS>
1253 <COUNTRY_ID>EG</COUNTRY_ID>
1254 <COUNTRY_NAME>Egypt</COUNTRY_NAME>
1255 <REGION_ID>1</REGION_ID>
1258 <COUNTRY_ID>SD</COUNTRY_ID>
1259 <COUNTRY_NAME>Sudan</COUNTRY_NAME>
1260 <REGION_ID>1</REGION_ID>
1264 FROM (SELECT data FROM xmldata) x,
1265 LATERAL XMLTABLE('/ROWS/ROW'
1267 COLUMNS id int PATH '@id',
1269 country_name text PATH 'COUNTRY_NAME' NOT NULL,
1270 country_id text PATH 'COUNTRY_ID',
1271 region_id int PATH 'REGION_ID',
1272 size float PATH 'SIZE',
1273 unit text PATH 'SIZE/@unit',
1274 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
1275 id | _id | country_name | country_id | region_id | size | unit | premier_name
1276 ----+-----+----------------+------------+-----------+------+------+---------------
1277 1 | 1 | Australia | AU | 3 | | | not specified
1278 2 | 2 | China | CN | 3 | | | not specified
1279 3 | 3 | HongKong | HK | 3 | | | not specified
1280 4 | 4 | India | IN | 3 | | | not specified
1281 5 | 5 | Japan | JP | 3 | | | Sinzo Abe
1282 6 | 6 | Singapore | SG | 3 | 791 | km | not specified
1283 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman
1284 11 | 2 | Germany | DE | 2 | | | not specified
1285 12 | 3 | France | FR | 2 | | | not specified
1286 20 | 1 | Egypt | EG | 1 | | | not specified
1287 21 | 2 | Sudan | SD | 1 | | | not specified
1291 FROM (SELECT data FROM xmldata) x,
1292 LATERAL XMLTABLE('/ROWS/ROW'
1294 COLUMNS id int PATH '@id',
1296 country_name text PATH 'COUNTRY_NAME' NOT NULL,
1297 country_id text PATH 'COUNTRY_ID',
1298 region_id int PATH 'REGION_ID',
1299 size float PATH 'SIZE',
1300 unit text PATH 'SIZE/@unit',
1301 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
1302 WHERE region_id = 2;
1303 id | _id | country_name | country_id | region_id | size | unit | premier_name
1304 ----+-----+----------------+------------+-----------+------+------+---------------
1305 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman
1306 11 | 2 | Germany | DE | 2 | | | not specified
1307 12 | 3 | France | FR | 2 | | | not specified
1310 EXPLAIN (VERBOSE, COSTS OFF)
1312 FROM (SELECT data FROM xmldata) x,
1313 LATERAL XMLTABLE('/ROWS/ROW'
1315 COLUMNS id int PATH '@id',
1317 country_name text PATH 'COUNTRY_NAME' NOT NULL,
1318 country_id text PATH 'COUNTRY_ID',
1319 region_id int PATH 'REGION_ID',
1320 size float PATH 'SIZE',
1321 unit text PATH 'SIZE/@unit',
1322 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified')
1323 WHERE region_id = 2;
1325 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1327 Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
1328 -> Seq Scan on public.xmldata
1329 Output: xmldata.data
1330 -> Table Function Scan on "xmltable"
1331 Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name
1332 Table Function Call: XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME'::text) NOT NULL, country_id text PATH ('COUNTRY_ID'::text), region_id integer PATH ('REGION_ID'::text), size double precision PATH ('SIZE'::text), unit text PATH ('SIZE/@unit'::text), premier_name text DEFAULT ('not specified'::text) PATH ('PREMIER_NAME'::text))
1333 Filter: ("xmltable".region_id = 2)
1336 -- should fail, NULL value
1338 FROM (SELECT data FROM xmldata) x,
1339 LATERAL XMLTABLE('/ROWS/ROW'
1341 COLUMNS id int PATH '@id',
1343 country_name text PATH 'COUNTRY_NAME' NOT NULL,
1344 country_id text PATH 'COUNTRY_ID',
1345 region_id int PATH 'REGION_ID',
1346 size float PATH 'SIZE' NOT NULL,
1347 unit text PATH 'SIZE/@unit',
1348 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
1349 ERROR: null is not allowed in column "size"
1350 -- if all is ok, then result is empty
1351 -- one line xml test
1353 x AS (SELECT proname, proowner, procost::numeric, pronargs,
1354 array_to_string(proargnames,',') as proargnames,
1355 case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes
1356 FROM pg_proc WHERE proname = 'f_leak'),
1357 y AS (SELECT xmlelement(name proc,
1358 xmlforest(proname, proowner,
1360 proargnames, proargtypes)) as proc
1362 z AS (SELECT xmltable.*
1364 LATERAL xmltable('/proc' PASSING proc
1365 COLUMNS proname name,
1372 EXCEPT SELECT * FROM x;
1373 proname | proowner | procost | pronargs | proargnames | proargtypes
1374 ---------+----------+---------+----------+-------------+-------------
1377 -- multi line xml test, result should be empty too
1379 x AS (SELECT proname, proowner, procost::numeric, pronargs,
1380 array_to_string(proargnames,',') as proargnames,
1381 case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes
1383 y AS (SELECT xmlelement(name data,
1384 xmlagg(xmlelement(name proc,
1385 xmlforest(proname, proowner, procost,
1386 pronargs, proargnames, proargtypes)))) as doc
1388 z AS (SELECT xmltable.*
1390 LATERAL xmltable('/data/proc' PASSING doc
1391 COLUMNS proname name,
1398 EXCEPT SELECT * FROM x;
1399 proname | proowner | procost | pronargs | proargnames | proargtypes
1400 ---------+----------+---------+----------+-------------+-------------
1403 CREATE TABLE xmltest2(x xml, _path text);
1404 INSERT INTO xmltest2 VALUES('<d><r><ac>1</ac></r></d>', 'A');
1405 INSERT INTO xmltest2 VALUES('<d><r><bc>2</bc></r></d>', 'B');
1406 INSERT INTO xmltest2 VALUES('<d><r><cc>3</cc></r></d>', 'C');
1407 INSERT INTO xmltest2 VALUES('<d><r><dc>2</dc></r></d>', 'D');
1408 SELECT xmltable.* FROM xmltest2, LATERAL xmltable('/d/r' PASSING x COLUMNS a int PATH '' || lower(_path) || 'c');
1417 SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH '.');
1426 SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54);