1CREATE TABLE xmltest ( 2 id int, 3 data xml 4); 5INSERT INTO xmltest VALUES (1, '<value>one</value>'); 6INSERT INTO xmltest VALUES (2, '<value>two</value>'); 7INSERT INTO xmltest VALUES (3, '<wrong'); 8ERROR: invalid XML content 9LINE 1: INSERT INTO xmltest VALUES (3, '<wrong'); 10 ^ 11DETAIL: line 1: Couldn't find end of Start Tag wrong line 1 12SELECT * FROM xmltest; 13 id | data 14----+-------------------- 15 1 | <value>one</value> 16 2 | <value>two</value> 17(2 rows) 18 19SELECT xmlcomment('test'); 20 xmlcomment 21------------- 22 <!--test--> 23(1 row) 24 25SELECT xmlcomment('-test'); 26 xmlcomment 27-------------- 28 <!---test--> 29(1 row) 30 31SELECT xmlcomment('test-'); 32ERROR: invalid XML comment 33SELECT xmlcomment('--test'); 34ERROR: invalid XML comment 35SELECT xmlcomment('te st'); 36 xmlcomment 37-------------- 38 <!--te st--> 39(1 row) 40 41SELECT xmlconcat(xmlcomment('hello'), 42 xmlelement(NAME qux, 'foo'), 43 xmlcomment('world')); 44 xmlconcat 45---------------------------------------- 46 <!--hello--><qux>foo</qux><!--world--> 47(1 row) 48 49SELECT xmlconcat('hello', 'you'); 50 xmlconcat 51----------- 52 helloyou 53(1 row) 54 55SELECT xmlconcat(1, 2); 56ERROR: argument of XMLCONCAT must be type xml, not type integer 57LINE 1: SELECT xmlconcat(1, 2); 58 ^ 59SELECT xmlconcat('bad', '<syntax'); 60ERROR: invalid XML content 61LINE 1: SELECT xmlconcat('bad', '<syntax'); 62 ^ 63DETAIL: line 1: Couldn't find end of Start Tag syntax line 1 64SELECT xmlconcat('<foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>'); 65 xmlconcat 66-------------- 67 <foo/><bar/> 68(1 row) 69 70SELECT xmlconcat('<?xml version="1.1"?><foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>'); 71 xmlconcat 72----------------------------------- 73 <?xml version="1.1"?><foo/><bar/> 74(1 row) 75 76SELECT xmlconcat(NULL); 77 xmlconcat 78----------- 79 80(1 row) 81 82SELECT xmlconcat(NULL, NULL); 83 xmlconcat 84----------- 85 86(1 row) 87 88SELECT xmlelement(name element, 89 xmlattributes (1 as one, 'deuce' as two), 90 'content'); 91 xmlelement 92------------------------------------------------ 93 <element one="1" two="deuce">content</element> 94(1 row) 95 96SELECT xmlelement(name element, 97 xmlattributes ('unnamed and wrong')); 98ERROR: unnamed XML attribute value must be a column reference 99LINE 2: xmlattributes ('unnamed and wrong')); 100 ^ 101SELECT xmlelement(name element, xmlelement(name nested, 'stuff')); 102 xmlelement 103------------------------------------------- 104 <element><nested>stuff</nested></element> 105(1 row) 106 107SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp; 108 xmlelement 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> 116(6 rows) 117 118SELECT xmlelement(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a)); 119ERROR: XML attribute name "a" appears more than once 120LINE 1: ...ment(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a)); 121 ^ 122SELECT xmlelement(name num, 37); 123 xmlelement 124--------------- 125 <num>37</num> 126(1 row) 127 128SELECT xmlelement(name foo, text 'bar'); 129 xmlelement 130---------------- 131 <foo>bar</foo> 132(1 row) 133 134SELECT xmlelement(name foo, xml 'bar'); 135 xmlelement 136---------------- 137 <foo>bar</foo> 138(1 row) 139 140SELECT xmlelement(name foo, text 'b<a/>r'); 141 xmlelement 142------------------------- 143 <foo>b<a/>r</foo> 144(1 row) 145 146SELECT xmlelement(name foo, xml 'b<a/>r'); 147 xmlelement 148------------------- 149 <foo>b<a/>r</foo> 150(1 row) 151 152SELECT xmlelement(name foo, array[1, 2, 3]); 153 xmlelement 154------------------------------------------------------------------------- 155 <foo><element>1</element><element>2</element><element>3</element></foo> 156(1 row) 157 158SET xmlbinary TO base64; 159SELECT xmlelement(name foo, bytea 'bar'); 160 xmlelement 161----------------- 162 <foo>YmFy</foo> 163(1 row) 164 165SET xmlbinary TO hex; 166SELECT xmlelement(name foo, bytea 'bar'); 167 xmlelement 168------------------- 169 <foo>626172</foo> 170(1 row) 171 172SELECT xmlelement(name foo, xmlattributes(true as bar)); 173 xmlelement 174------------------- 175 <foo bar="true"/> 176(1 row) 177 178SELECT xmlelement(name foo, xmlattributes('2009-04-09 00:24:37'::timestamp as bar)); 179 xmlelement 180---------------------------------- 181 <foo bar="2009-04-09T00:24:37"/> 182(1 row) 183 184SELECT xmlelement(name foo, xmlattributes('infinity'::timestamp as bar)); 185ERROR: timestamp out of range 186DETAIL: XML does not support infinite timestamp values. 187SELECT xmlelement(name foo, xmlattributes('<>&"''' as funny, xml 'b<a/>r' as funnier)); 188 xmlelement 189------------------------------------------------------------ 190 <foo funny="<>&"'" funnier="b<a/>r"/> 191(1 row) 192 193SELECT xmlparse(content ''); 194 xmlparse 195---------- 196 197(1 row) 198 199SELECT xmlparse(content ' '); 200 xmlparse 201---------- 202 203(1 row) 204 205SELECT xmlparse(content 'abc'); 206 xmlparse 207---------- 208 abc 209(1 row) 210 211SELECT xmlparse(content '<abc>x</abc>'); 212 xmlparse 213-------------- 214 <abc>x</abc> 215(1 row) 216 217SELECT xmlparse(content '<invalidentity>&</invalidentity>'); 218ERROR: invalid XML content 219DETAIL: line 1: xmlParseEntityRef: no name 220<invalidentity>&</invalidentity> 221 ^ 222line 1: chunk is not well balanced 223SELECT xmlparse(content '<undefinedentity>&idontexist;</undefinedentity>'); 224ERROR: invalid XML content 225DETAIL: line 1: Entity 'idontexist' not defined 226<undefinedentity>&idontexist;</undefinedentity> 227 ^ 228line 1: chunk is not well balanced 229SELECT xmlparse(content '<invalidns xmlns=''<''/>'); 230 xmlparse 231--------------------------- 232 <invalidns xmlns='<'/> 233(1 row) 234 235SELECT xmlparse(content '<relativens xmlns=''relative''/>'); 236 xmlparse 237-------------------------------- 238 <relativens xmlns='relative'/> 239(1 row) 240 241SELECT xmlparse(content '<twoerrors>&idontexist;</unbalanced>'); 242ERROR: invalid XML content 243DETAIL: line 1: Entity 'idontexist' not defined 244<twoerrors>&idontexist;</unbalanced> 245 ^ 246line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced 247line 1: chunk is not well balanced 248SELECT xmlparse(content '<nosuchprefix:tag/>'); 249 xmlparse 250--------------------- 251 <nosuchprefix:tag/> 252(1 row) 253 254SELECT xmlparse(document ' '); 255ERROR: invalid XML document 256DETAIL: line 1: Start tag expected, '<' not found 257SELECT xmlparse(document 'abc'); 258ERROR: invalid XML document 259DETAIL: line 1: Start tag expected, '<' not found 260abc 261^ 262SELECT xmlparse(document '<abc>x</abc>'); 263 xmlparse 264-------------- 265 <abc>x</abc> 266(1 row) 267 268SELECT xmlparse(document '<invalidentity>&</abc>'); 269ERROR: invalid XML document 270DETAIL: line 1: xmlParseEntityRef: no name 271<invalidentity>&</abc> 272 ^ 273line 1: Opening and ending tag mismatch: invalidentity line 1 and abc 274SELECT xmlparse(document '<undefinedentity>&idontexist;</abc>'); 275ERROR: invalid XML document 276DETAIL: line 1: Entity 'idontexist' not defined 277<undefinedentity>&idontexist;</abc> 278 ^ 279line 1: Opening and ending tag mismatch: undefinedentity line 1 and abc 280SELECT xmlparse(document '<invalidns xmlns=''<''/>'); 281 xmlparse 282--------------------------- 283 <invalidns xmlns='<'/> 284(1 row) 285 286SELECT xmlparse(document '<relativens xmlns=''relative''/>'); 287 xmlparse 288-------------------------------- 289 <relativens xmlns='relative'/> 290(1 row) 291 292SELECT xmlparse(document '<twoerrors>&idontexist;</unbalanced>'); 293ERROR: invalid XML document 294DETAIL: line 1: Entity 'idontexist' not defined 295<twoerrors>&idontexist;</unbalanced> 296 ^ 297line 1: Opening and ending tag mismatch: twoerrors line 1 and unbalanced 298SELECT xmlparse(document '<nosuchprefix:tag/>'); 299 xmlparse 300--------------------- 301 <nosuchprefix:tag/> 302(1 row) 303 304SELECT xmlpi(name foo); 305 xmlpi 306--------- 307 <?foo?> 308(1 row) 309 310SELECT xmlpi(name xml); 311ERROR: invalid XML processing instruction 312DETAIL: XML processing instruction target name cannot be "xml". 313SELECT xmlpi(name xmlstuff); 314 xmlpi 315-------------- 316 <?xmlstuff?> 317(1 row) 318 319SELECT xmlpi(name foo, 'bar'); 320 xmlpi 321------------- 322 <?foo bar?> 323(1 row) 324 325SELECT xmlpi(name foo, 'in?>valid'); 326ERROR: invalid XML processing instruction 327DETAIL: XML processing instruction cannot contain "?>". 328SELECT xmlpi(name foo, null); 329 xmlpi 330------- 331 332(1 row) 333 334SELECT xmlpi(name xml, null); 335ERROR: invalid XML processing instruction 336DETAIL: XML processing instruction target name cannot be "xml". 337SELECT xmlpi(name xmlstuff, null); 338 xmlpi 339------- 340 341(1 row) 342 343SELECT xmlpi(name "xml-stylesheet", 'href="mystyle.css" type="text/css"'); 344 xmlpi 345------------------------------------------------------- 346 <?xml-stylesheet href="mystyle.css" type="text/css"?> 347(1 row) 348 349SELECT xmlpi(name foo, ' bar'); 350 xmlpi 351------------- 352 <?foo bar?> 353(1 row) 354 355SELECT xmlroot(xml '<foo/>', version no value, standalone no value); 356 xmlroot 357--------- 358 <foo/> 359(1 row) 360 361SELECT xmlroot(xml '<foo/>', version '2.0'); 362 xmlroot 363----------------------------- 364 <?xml version="2.0"?><foo/> 365(1 row) 366 367SELECT xmlroot(xml '<foo/>', version no value, standalone yes); 368 xmlroot 369---------------------------------------------- 370 <?xml version="1.0" standalone="yes"?><foo/> 371(1 row) 372 373SELECT xmlroot(xml '<?xml version="1.1"?><foo/>', version no value, standalone yes); 374 xmlroot 375---------------------------------------------- 376 <?xml version="1.0" standalone="yes"?><foo/> 377(1 row) 378 379SELECT xmlroot(xmlroot(xml '<foo/>', version '1.0'), version '1.1', standalone no); 380 xmlroot 381--------------------------------------------- 382 <?xml version="1.1" standalone="no"?><foo/> 383(1 row) 384 385SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no); 386 xmlroot 387--------------------------------------------- 388 <?xml version="1.0" standalone="no"?><foo/> 389(1 row) 390 391SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no value); 392 xmlroot 393--------- 394 <foo/> 395(1 row) 396 397SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value); 398 xmlroot 399---------------------------------------------- 400 <?xml version="1.0" standalone="yes"?><foo/> 401(1 row) 402 403SELECT xmlroot ( 404 xmlelement ( 405 name gazonk, 406 xmlattributes ( 407 'val' AS name, 408 1 + 1 AS num 409 ), 410 xmlelement ( 411 NAME qux, 412 'foo' 413 ) 414 ), 415 version '1.0', 416 standalone yes 417); 418 xmlroot 419------------------------------------------------------------------------------------------ 420 <?xml version="1.0" standalone="yes"?><gazonk name="val" num="2"><qux>foo</qux></gazonk> 421(1 row) 422 423SELECT xmlserialize(content data as character varying(20)) FROM xmltest; 424 xmlserialize 425-------------------- 426 <value>one</value> 427 <value>two</value> 428(2 rows) 429 430SELECT xmlserialize(content 'good' as char(10)); 431 xmlserialize 432-------------- 433 good 434(1 row) 435 436SELECT xmlserialize(document 'bad' as text); 437ERROR: not an XML document 438SELECT xml '<foo>bar</foo>' IS DOCUMENT; 439 ?column? 440---------- 441 t 442(1 row) 443 444SELECT xml '<foo>bar</foo><bar>foo</bar>' IS DOCUMENT; 445 ?column? 446---------- 447 f 448(1 row) 449 450SELECT xml '<abc/>' IS NOT DOCUMENT; 451 ?column? 452---------- 453 f 454(1 row) 455 456SELECT xml 'abc' IS NOT DOCUMENT; 457 ?column? 458---------- 459 t 460(1 row) 461 462SELECT '<>' IS NOT DOCUMENT; 463ERROR: invalid XML content 464LINE 1: SELECT '<>' IS NOT DOCUMENT; 465 ^ 466DETAIL: line 1: StartTag: invalid element name 467<> 468 ^ 469SELECT xmlagg(data) FROM xmltest; 470 xmlagg 471-------------------------------------- 472 <value>one</value><value>two</value> 473(1 row) 474 475SELECT xmlagg(data) FROM xmltest WHERE id > 10; 476 xmlagg 477-------- 478 479(1 row) 480 481SELECT xmlelement(name employees, xmlagg(xmlelement(name name, name))) FROM emp; 482 xmlelement 483-------------------------------------------------------------------------------------------------------------------------------- 484 <employees><name>sharon</name><name>sam</name><name>bill</name><name>jeff</name><name>cim</name><name>linda</name></employees> 485(1 row) 486 487-- Check mapping SQL identifier to XML name 488SELECT xmlpi(name ":::_xml_abc135.%-&_"); 489 xmlpi 490------------------------------------------------- 491 <?_x003A_::_x005F_xml_abc135._x0025_-_x0026__?> 492(1 row) 493 494SELECT xmlpi(name "123"); 495 xmlpi 496--------------- 497 <?_x0031_23?> 498(1 row) 499 500PREPARE foo (xml) AS SELECT xmlconcat('<foo/>', $1); 501SET XML OPTION DOCUMENT; 502EXECUTE foo ('<bar/>'); 503 xmlconcat 504-------------- 505 <foo/><bar/> 506(1 row) 507 508EXECUTE foo ('bad'); 509ERROR: invalid XML document 510LINE 1: EXECUTE foo ('bad'); 511 ^ 512DETAIL: line 1: Start tag expected, '<' not found 513bad 514^ 515SELECT xml '<!DOCTYPE a><a/><b/>'; 516ERROR: invalid XML document 517LINE 1: SELECT xml '<!DOCTYPE a><a/><b/>'; 518 ^ 519DETAIL: line 1: Extra content at the end of the document 520<!DOCTYPE a><a/><b/> 521 ^ 522SET XML OPTION CONTENT; 523EXECUTE foo ('<bar/>'); 524 xmlconcat 525-------------- 526 <foo/><bar/> 527(1 row) 528 529EXECUTE foo ('good'); 530 xmlconcat 531------------ 532 <foo/>good 533(1 row) 534 535SELECT xml '<!-- in SQL:2006+ a doc is content too--> <?y z?> <!DOCTYPE a><a/>'; 536 xml 537-------------------------------------------------------------------- 538 <!-- in SQL:2006+ a doc is content too--> <?y z?> <!DOCTYPE a><a/> 539(1 row) 540 541SELECT xml '<?xml version="1.0"?> <!-- hi--> <!DOCTYPE a><a/>'; 542 xml 543------------------------------ 544 <!-- hi--> <!DOCTYPE a><a/> 545(1 row) 546 547SELECT xml '<!DOCTYPE a><a/>'; 548 xml 549------------------ 550 <!DOCTYPE a><a/> 551(1 row) 552 553SELECT xml '<!-- hi--> oops <!DOCTYPE a><a/>'; 554ERROR: invalid XML content 555LINE 1: SELECT xml '<!-- hi--> oops <!DOCTYPE a><a/>'; 556 ^ 557DETAIL: line 1: StartTag: invalid element name 558<!-- hi--> oops <!DOCTYPE a><a/> 559 ^ 560SELECT xml '<!-- hi--> <oops/> <!DOCTYPE a><a/>'; 561ERROR: invalid XML content 562LINE 1: SELECT xml '<!-- hi--> <oops/> <!DOCTYPE a><a/>'; 563 ^ 564DETAIL: line 1: StartTag: invalid element name 565<!-- hi--> <oops/> <!DOCTYPE a><a/> 566 ^ 567SELECT xml '<!DOCTYPE a><a/><b/>'; 568ERROR: invalid XML content 569LINE 1: SELECT xml '<!DOCTYPE a><a/><b/>'; 570 ^ 571DETAIL: line 1: Extra content at the end of the document 572<!DOCTYPE a><a/><b/> 573 ^ 574-- Test backwards parsing 575CREATE VIEW xmlview1 AS SELECT xmlcomment('test'); 576CREATE VIEW xmlview2 AS SELECT xmlconcat('hello', 'you'); 577CREATE VIEW xmlview3 AS SELECT xmlelement(name element, xmlattributes (1 as ":one:", 'deuce' as two), 'content&'); 578CREATE VIEW xmlview4 AS SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp; 579CREATE VIEW xmlview5 AS SELECT xmlparse(content '<abc>x</abc>'); 580CREATE VIEW xmlview6 AS SELECT xmlpi(name foo, 'bar'); 581CREATE VIEW xmlview7 AS SELECT xmlroot(xml '<foo/>', version no value, standalone yes); 582CREATE VIEW xmlview8 AS SELECT xmlserialize(content 'good' as char(10)); 583CREATE VIEW xmlview9 AS SELECT xmlserialize(content 'good' as text); 584SELECT table_name, view_definition FROM information_schema.views 585 WHERE table_name LIKE 'xmlview%' ORDER BY 1; 586 table_name | view_definition 587------------+------------------------------------------------------------------------------------------------------------------- 588 xmlview1 | SELECT xmlcomment('test'::text) AS xmlcomment; 589 xmlview2 | SELECT XMLCONCAT('hello'::xml, 'you'::xml) AS "xmlconcat"; 590 xmlview3 | SELECT XMLELEMENT(NAME element, XMLATTRIBUTES(1 AS ":one:", 'deuce' AS two), 'content&') AS "xmlelement"; 591 xmlview4 | SELECT XMLELEMENT(NAME employee, XMLFOREST(emp.name AS name, emp.age AS age, emp.salary AS pay)) AS "xmlelement"+ 592 | FROM emp; 593 xmlview5 | SELECT XMLPARSE(CONTENT '<abc>x</abc>'::text STRIP WHITESPACE) AS "xmlparse"; 594 xmlview6 | SELECT XMLPI(NAME foo, 'bar'::text) AS "xmlpi"; 595 xmlview7 | SELECT XMLROOT('<foo/>'::xml, VERSION NO VALUE, STANDALONE YES) AS "xmlroot"; 596 xmlview8 | SELECT (XMLSERIALIZE(CONTENT 'good'::xml AS character(10)))::character(10) AS "xmlserialize"; 597 xmlview9 | SELECT XMLSERIALIZE(CONTENT 'good'::xml AS text) AS "xmlserialize"; 598(9 rows) 599 600-- Text XPath expressions evaluation 601SELECT xpath('/value', data) FROM xmltest; 602 xpath 603---------------------- 604 {<value>one</value>} 605 {<value>two</value>} 606(2 rows) 607 608SELECT xpath(NULL, NULL) IS NULL FROM xmltest; 609 ?column? 610---------- 611 t 612 t 613(2 rows) 614 615SELECT xpath('', '<!-- error -->'); 616ERROR: empty XPath expression 617CONTEXT: SQL function "xpath" statement 1 618SELECT 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>'); 619 xpath 620---------------- 621 {"number one"} 622(1 row) 623 624SELECT 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']]); 625 xpath 626------- 627 {1,2} 628(1 row) 629 630SELECT 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']]); 631 xpath 632------------------------------------------------------------------------------------------------------------------------------------------------ 633 {"<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\"/>"} 634(1 row) 635 636SELECT 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']]); 637 xpath 638-------------------------------------------------------------------------------------- 639 {"<local:piece xmlns:local=\"http://127.0.0.1\" xmlns=\"http://127.0.0.2\" id=\"1\">+ 640 <internal>number one</internal> + 641 <internal2/> + 642 </local:piece>","<local:piece xmlns:local=\"http://127.0.0.1\" id=\"2\"/>"} 643(1 row) 644 645SELECT xpath('//b', '<a>one <b>two</b> three <b>etc</b></a>'); 646 xpath 647------------------------- 648 {<b>two</b>,<b>etc</b>} 649(1 row) 650 651SELECT xpath('//text()', '<root><</root>'); 652 xpath 653-------- 654 {<} 655(1 row) 656 657SELECT xpath('//@value', '<root value="<"/>'); 658 xpath 659-------- 660 {<} 661(1 row) 662 663SELECT xpath('''<<invalid>>''', '<root/>'); 664 xpath 665--------------------------- 666 {<<invalid>>} 667(1 row) 668 669SELECT xpath('count(//*)', '<root><sub/><sub/></root>'); 670 xpath 671------- 672 {3} 673(1 row) 674 675SELECT xpath('count(//*)=0', '<root><sub/><sub/></root>'); 676 xpath 677--------- 678 {false} 679(1 row) 680 681SELECT xpath('count(//*)=3', '<root><sub/><sub/></root>'); 682 xpath 683-------- 684 {true} 685(1 row) 686 687SELECT xpath('name(/*)', '<root><sub/><sub/></root>'); 688 xpath 689-------- 690 {root} 691(1 row) 692 693SELECT xpath('/nosuchtag', '<root/>'); 694 xpath 695------- 696 {} 697(1 row) 698 699-- Round-trip non-ASCII data through xpath(). 700DO $$ 701DECLARE 702 xml_declaration text := '<?xml version="1.0" encoding="ISO-8859-1"?>'; 703 degree_symbol text; 704 res xml[]; 705BEGIN 706 -- Per the documentation, except when the server encoding is UTF8, xpath() 707 -- may not work on non-ASCII data. The untranslatable_character and 708 -- undefined_function traps below, currently dead code, will become relevant 709 -- if we remove this limitation. 710 IF current_setting('server_encoding') <> 'UTF8' THEN 711 RAISE LOG 'skip: encoding % unsupported for xpath', 712 current_setting('server_encoding'); 713 RETURN; 714 END IF; 715 716 degree_symbol := convert_from('\xc2b0', 'UTF8'); 717 res := xpath('text()', (xml_declaration || 718 '<x>' || degree_symbol || '</x>')::xml); 719 IF degree_symbol <> res[1]::text THEN 720 RAISE 'expected % (%), got % (%)', 721 degree_symbol, convert_to(degree_symbol, 'UTF8'), 722 res[1], convert_to(res[1]::text, 'UTF8'); 723 END IF; 724EXCEPTION 725 -- character with byte sequence 0xc2 0xb0 in encoding "UTF8" has no equivalent in encoding "LATIN8" 726 WHEN untranslatable_character 727 -- default conversion function for encoding "UTF8" to "MULE_INTERNAL" does not exist 728 OR undefined_function 729 -- unsupported XML feature 730 OR feature_not_supported THEN 731 RAISE LOG 'skip: %', SQLERRM; 732END 733$$; 734-- Test xmlexists and xpath_exists 735SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY REF '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'); 736 xmlexists 737----------- 738 f 739(1 row) 740 741SELECT xmlexists('//town[text() = ''Cwmbran'']' PASSING BY REF '<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'); 742 xmlexists 743----------- 744 t 745(1 row) 746 747SELECT xmlexists('count(/nosuchtag)' PASSING BY REF '<root/>'); 748 xmlexists 749----------- 750 t 751(1 row) 752 753SELECT xpath_exists('//town[text() = ''Toronto'']','<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'::xml); 754 xpath_exists 755-------------- 756 f 757(1 row) 758 759SELECT xpath_exists('//town[text() = ''Cwmbran'']','<towns><town>Bidford-on-Avon</town><town>Cwmbran</town><town>Bristol</town></towns>'::xml); 760 xpath_exists 761-------------- 762 t 763(1 row) 764 765SELECT xpath_exists('count(/nosuchtag)', '<root/>'::xml); 766 xpath_exists 767-------------- 768 t 769(1 row) 770 771INSERT INTO xmltest VALUES (4, '<menu><beers><name>Budvar</name><cost>free</cost><name>Carling</name><cost>lots</cost></beers></menu>'::xml); 772INSERT INTO xmltest VALUES (5, '<menu><beers><name>Molson</name><cost>free</cost><name>Carling</name><cost>lots</cost></beers></menu>'::xml); 773INSERT 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); 774INSERT 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); 775SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING data); 776 count 777------- 778 0 779(1 row) 780 781SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beer' PASSING BY REF data BY REF); 782 count 783------- 784 0 785(1 row) 786 787SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers' PASSING BY REF data); 788 count 789------- 790 2 791(1 row) 792 793SELECT COUNT(id) FROM xmltest WHERE xmlexists('/menu/beers/name[text() = ''Molson'']' PASSING BY REF data); 794 count 795------- 796 1 797(1 row) 798 799SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beer',data); 800 count 801------- 802 0 803(1 row) 804 805SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers',data); 806 count 807------- 808 2 809(1 row) 810 811SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/menu/beers/name[text() = ''Molson'']',data); 812 count 813------- 814 1 815(1 row) 816 817SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beer',data,ARRAY[ARRAY['myns','http://myns.com']]); 818 count 819------- 820 0 821(1 row) 822 823SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers',data,ARRAY[ARRAY['myns','http://myns.com']]); 824 count 825------- 826 2 827(1 row) 828 829SELECT COUNT(id) FROM xmltest WHERE xpath_exists('/myns:menu/myns:beers/myns:name[text() = ''Molson'']',data,ARRAY[ARRAY['myns','http://myns.com']]); 830 count 831------- 832 1 833(1 row) 834 835CREATE TABLE query ( expr TEXT ); 836INSERT INTO query VALUES ('/menu/beers/cost[text() = ''lots'']'); 837SELECT COUNT(id) FROM xmltest, query WHERE xmlexists(expr PASSING BY REF data); 838 count 839------- 840 2 841(1 row) 842 843-- Test xml_is_well_formed and variants 844SELECT xml_is_well_formed_document('<foo>bar</foo>'); 845 xml_is_well_formed_document 846----------------------------- 847 t 848(1 row) 849 850SELECT xml_is_well_formed_document('abc'); 851 xml_is_well_formed_document 852----------------------------- 853 f 854(1 row) 855 856SELECT xml_is_well_formed_content('<foo>bar</foo>'); 857 xml_is_well_formed_content 858---------------------------- 859 t 860(1 row) 861 862SELECT xml_is_well_formed_content('abc'); 863 xml_is_well_formed_content 864---------------------------- 865 t 866(1 row) 867 868SET xmloption TO DOCUMENT; 869SELECT xml_is_well_formed('abc'); 870 xml_is_well_formed 871-------------------- 872 f 873(1 row) 874 875SELECT xml_is_well_formed('<>'); 876 xml_is_well_formed 877-------------------- 878 f 879(1 row) 880 881SELECT xml_is_well_formed('<abc/>'); 882 xml_is_well_formed 883-------------------- 884 t 885(1 row) 886 887SELECT xml_is_well_formed('<foo>bar</foo>'); 888 xml_is_well_formed 889-------------------- 890 t 891(1 row) 892 893SELECT xml_is_well_formed('<foo>bar</foo'); 894 xml_is_well_formed 895-------------------- 896 f 897(1 row) 898 899SELECT xml_is_well_formed('<foo><bar>baz</foo>'); 900 xml_is_well_formed 901-------------------- 902 f 903(1 row) 904 905SELECT 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>'); 906 xml_is_well_formed 907-------------------- 908 t 909(1 row) 910 911SELECT xml_is_well_formed('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>'); 912 xml_is_well_formed 913-------------------- 914 f 915(1 row) 916 917SELECT xml_is_well_formed('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>'); 918 xml_is_well_formed 919-------------------- 920 t 921(1 row) 922 923SELECT xml_is_well_formed('<invalidentity>&</abc>'); 924 xml_is_well_formed 925-------------------- 926 f 927(1 row) 928 929SELECT xml_is_well_formed('<undefinedentity>&idontexist;</abc>'); 930 xml_is_well_formed 931-------------------- 932 f 933(1 row) 934 935SELECT xml_is_well_formed('<invalidns xmlns=''<''/>'); 936 xml_is_well_formed 937-------------------- 938 t 939(1 row) 940 941SELECT xml_is_well_formed('<relativens xmlns=''relative''/>'); 942 xml_is_well_formed 943-------------------- 944 t 945(1 row) 946 947SELECT xml_is_well_formed('<twoerrors>&idontexist;</unbalanced>'); 948 xml_is_well_formed 949-------------------- 950 f 951(1 row) 952 953SET xmloption TO CONTENT; 954SELECT xml_is_well_formed('abc'); 955 xml_is_well_formed 956-------------------- 957 t 958(1 row) 959 960-- Since xpath() deals with namespaces, it's a bit stricter about 961-- what's well-formed and what's not. If we don't obey these rules 962-- (i.e. ignore namespace-related errors from libxml), xpath() 963-- fails in subtle ways. The following would for example produce 964-- the xml value 965-- <invalidns xmlns='<'/> 966-- which is invalid because '<' may not appear un-escaped in 967-- attribute values. 968-- Since different libxml versions emit slightly different 969-- error messages, we suppress the DETAIL in this test. 970\set VERBOSITY terse 971SELECT xpath('/*', '<invalidns xmlns=''<''/>'); 972ERROR: could not parse XML document 973\set VERBOSITY default 974-- Again, the XML isn't well-formed for namespace purposes 975SELECT xpath('/*', '<nosuchprefix:tag/>'); 976ERROR: could not parse XML document 977DETAIL: line 1: Namespace prefix nosuchprefix on tag is not defined 978<nosuchprefix:tag/> 979 ^ 980CONTEXT: SQL function "xpath" statement 1 981-- XPath deprecates relative namespaces, but they're not supposed to 982-- throw an error, only a warning. 983SELECT xpath('/*', '<relativens xmlns=''relative''/>'); 984WARNING: line 1: xmlns: URI relative is not absolute 985<relativens xmlns='relative'/> 986 ^ 987 xpath 988-------------------------------------- 989 {"<relativens xmlns=\"relative\"/>"} 990(1 row) 991 992-- External entity references should not leak filesystem information. 993SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/passwd">]><foo>&c;</foo>'); 994 xmlparse 995----------------------------------------------------------------- 996 <!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/passwd">]><foo>&c;</foo> 997(1 row) 998 999SELECT XMLPARSE(DOCUMENT '<!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/no.such.file">]><foo>&c;</foo>'); 1000 xmlparse 1001----------------------------------------------------------------------- 1002 <!DOCTYPE foo [<!ENTITY c SYSTEM "/etc/no.such.file">]><foo>&c;</foo> 1003(1 row) 1004 1005-- This might or might not load the requested DTD, but it mustn't throw error. 1006SELECT 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>'); 1007 xmlparse 1008------------------------------------------------------------------------------------------------------------------------------------------------------ 1009 <!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> 1010(1 row) 1011 1012-- XMLPATH tests 1013CREATE TABLE xmldata(data xml); 1014INSERT INTO xmldata VALUES('<ROWS> 1015<ROW id="1"> 1016 <COUNTRY_ID>AU</COUNTRY_ID> 1017 <COUNTRY_NAME>Australia</COUNTRY_NAME> 1018 <REGION_ID>3</REGION_ID> 1019</ROW> 1020<ROW id="2"> 1021 <COUNTRY_ID>CN</COUNTRY_ID> 1022 <COUNTRY_NAME>China</COUNTRY_NAME> 1023 <REGION_ID>3</REGION_ID> 1024</ROW> 1025<ROW id="3"> 1026 <COUNTRY_ID>HK</COUNTRY_ID> 1027 <COUNTRY_NAME>HongKong</COUNTRY_NAME> 1028 <REGION_ID>3</REGION_ID> 1029</ROW> 1030<ROW id="4"> 1031 <COUNTRY_ID>IN</COUNTRY_ID> 1032 <COUNTRY_NAME>India</COUNTRY_NAME> 1033 <REGION_ID>3</REGION_ID> 1034</ROW> 1035<ROW id="5"> 1036 <COUNTRY_ID>JP</COUNTRY_ID> 1037 <COUNTRY_NAME>Japan</COUNTRY_NAME> 1038 <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME> 1039</ROW> 1040<ROW id="6"> 1041 <COUNTRY_ID>SG</COUNTRY_ID> 1042 <COUNTRY_NAME>Singapore</COUNTRY_NAME> 1043 <REGION_ID>3</REGION_ID><SIZE unit="km">791</SIZE> 1044</ROW> 1045</ROWS>'); 1046-- XMLTABLE with columns 1047SELECT xmltable.* 1048 FROM (SELECT data FROM xmldata) x, 1049 LATERAL XMLTABLE('/ROWS/ROW' 1050 PASSING data 1051 COLUMNS id int PATH '@id', 1052 _id FOR ORDINALITY, 1053 country_name text PATH 'COUNTRY_NAME/text()' NOT NULL, 1054 country_id text PATH 'COUNTRY_ID', 1055 region_id int PATH 'REGION_ID', 1056 size float PATH 'SIZE', 1057 unit text PATH 'SIZE/@unit', 1058 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); 1059 id | _id | country_name | country_id | region_id | size | unit | premier_name 1060----+-----+--------------+------------+-----------+------+------+--------------- 1061 1 | 1 | Australia | AU | 3 | | | not specified 1062 2 | 2 | China | CN | 3 | | | not specified 1063 3 | 3 | HongKong | HK | 3 | | | not specified 1064 4 | 4 | India | IN | 3 | | | not specified 1065 5 | 5 | Japan | JP | 3 | | | Sinzo Abe 1066 6 | 6 | Singapore | SG | 3 | 791 | km | not specified 1067(6 rows) 1068 1069CREATE VIEW xmltableview1 AS SELECT xmltable.* 1070 FROM (SELECT data FROM xmldata) x, 1071 LATERAL XMLTABLE('/ROWS/ROW' 1072 PASSING data 1073 COLUMNS id int PATH '@id', 1074 _id FOR ORDINALITY, 1075 country_name text PATH 'COUNTRY_NAME/text()' NOT NULL, 1076 country_id text PATH 'COUNTRY_ID', 1077 region_id int PATH 'REGION_ID', 1078 size float PATH 'SIZE', 1079 unit text PATH 'SIZE/@unit', 1080 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); 1081SELECT * FROM xmltableview1; 1082 id | _id | country_name | country_id | region_id | size | unit | premier_name 1083----+-----+--------------+------------+-----------+------+------+--------------- 1084 1 | 1 | Australia | AU | 3 | | | not specified 1085 2 | 2 | China | CN | 3 | | | not specified 1086 3 | 3 | HongKong | HK | 3 | | | not specified 1087 4 | 4 | India | IN | 3 | | | not specified 1088 5 | 5 | Japan | JP | 3 | | | Sinzo Abe 1089 6 | 6 | Singapore | SG | 3 | 791 | km | not specified 1090(6 rows) 1091 1092\sv xmltableview1 1093CREATE OR REPLACE VIEW public.xmltableview1 AS 1094 SELECT "xmltable".id, 1095 "xmltable"._id, 1096 "xmltable".country_name, 1097 "xmltable".country_id, 1098 "xmltable".region_id, 1099 "xmltable".size, 1100 "xmltable".unit, 1101 "xmltable".premier_name 1102 FROM ( SELECT xmldata.data 1103 FROM xmldata) x, 1104 LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (x.data) COLUMNS id integer PATH ('@id'::text), _id FOR ORDINALITY, country_name text PATH ('COUNTRY_NAME/text()'::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)) 1105EXPLAIN (COSTS OFF) SELECT * FROM xmltableview1; 1106 QUERY PLAN 1107----------------------------------------- 1108 Nested Loop 1109 -> Seq Scan on xmldata 1110 -> Table Function Scan on "xmltable" 1111(3 rows) 1112 1113EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1; 1114 QUERY PLAN 1115------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1116 Nested Loop 1117 Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name 1118 -> Seq Scan on public.xmldata 1119 Output: xmldata.data 1120 -> Table Function Scan on "xmltable" 1121 Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name 1122 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()'::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)) 1123(7 rows) 1124 1125-- XMLNAMESPACES tests 1126SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz), 1127 '/zz:rows/zz:row' 1128 PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>' 1129 COLUMNS a int PATH 'zz:a'); 1130 a 1131---- 1132 10 1133(1 row) 1134 1135CREATE VIEW xmltableview2 AS SELECT * FROM XMLTABLE(XMLNAMESPACES('http://x.y' AS zz), 1136 '/zz:rows/zz:row' 1137 PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>' 1138 COLUMNS a int PATH 'zz:a'); 1139SELECT * FROM xmltableview2; 1140 a 1141---- 1142 10 1143(1 row) 1144 1145SELECT * FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://x.y'), 1146 '/rows/row' 1147 PASSING '<rows xmlns="http://x.y"><row><a>10</a></row></rows>' 1148 COLUMNS a int PATH 'a'); 1149ERROR: DEFAULT namespace is not supported 1150SELECT * FROM XMLTABLE('/' 1151 PASSING '<foo/>' 1152 COLUMNS a text PATH 'foo/namespace::node()'); 1153ERROR: XMLTABLE cannot cast a namespace node to a non-XML result type 1154-- used in prepare statements 1155PREPARE pp AS 1156SELECT xmltable.* 1157 FROM (SELECT data FROM xmldata) x, 1158 LATERAL XMLTABLE('/ROWS/ROW' 1159 PASSING data 1160 COLUMNS id int PATH '@id', 1161 _id FOR ORDINALITY, 1162 country_name text PATH 'COUNTRY_NAME' NOT NULL, 1163 country_id text PATH 'COUNTRY_ID', 1164 region_id int PATH 'REGION_ID', 1165 size float PATH 'SIZE', 1166 unit text PATH 'SIZE/@unit', 1167 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); 1168EXECUTE pp; 1169 id | _id | country_name | country_id | region_id | size | unit | premier_name 1170----+-----+--------------+------------+-----------+------+------+--------------- 1171 1 | 1 | Australia | AU | 3 | | | not specified 1172 2 | 2 | China | CN | 3 | | | not specified 1173 3 | 3 | HongKong | HK | 3 | | | not specified 1174 4 | 4 | India | IN | 3 | | | not specified 1175 5 | 5 | Japan | JP | 3 | | | Sinzo Abe 1176 6 | 6 | Singapore | SG | 3 | 791 | km | not specified 1177(6 rows) 1178 1179SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS "COUNTRY_NAME" text, "REGION_ID" int); 1180 COUNTRY_NAME | REGION_ID 1181--------------+----------- 1182 India | 3 1183 Japan | 3 1184(2 rows) 1185 1186SELECT 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); 1187 id | COUNTRY_NAME | REGION_ID 1188----+--------------+----------- 1189 1 | India | 3 1190 2 | Japan | 3 1191(2 rows) 1192 1193SELECT 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); 1194 id | COUNTRY_NAME | REGION_ID 1195----+--------------+----------- 1196 4 | India | 3 1197 5 | Japan | 3 1198(2 rows) 1199 1200SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id int PATH '@id'); 1201 id 1202---- 1203 4 1204 5 1205(2 rows) 1206 1207SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]' PASSING data COLUMNS id FOR ORDINALITY); 1208 id 1209---- 1210 1 1211 2 1212(2 rows) 1213 1214SELECT 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 '.'); 1215 id | COUNTRY_NAME | REGION_ID | rawdata 1216----+--------------+-----------+------------------------------------------------------------------ 1217 4 | India | 3 | <ROW id="4"> + 1218 | | | <COUNTRY_ID>IN</COUNTRY_ID> + 1219 | | | <COUNTRY_NAME>India</COUNTRY_NAME> + 1220 | | | <REGION_ID>3</REGION_ID> + 1221 | | | </ROW> 1222 5 | Japan | 3 | <ROW id="5"> + 1223 | | | <COUNTRY_ID>JP</COUNTRY_ID> + 1224 | | | <COUNTRY_NAME>Japan</COUNTRY_NAME> + 1225 | | | <REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME>+ 1226 | | | </ROW> 1227(2 rows) 1228 1229SELECT 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 './*'); 1230 id | COUNTRY_NAME | REGION_ID | rawdata 1231----+--------------+-----------+----------------------------------------------------------------------------------------------------------------------------- 1232 4 | India | 3 | <COUNTRY_ID>IN</COUNTRY_ID><COUNTRY_NAME>India</COUNTRY_NAME><REGION_ID>3</REGION_ID> 1233 5 | Japan | 3 | <COUNTRY_ID>JP</COUNTRY_ID><COUNTRY_NAME>Japan</COUNTRY_NAME><REGION_ID>3</REGION_ID><PREMIER_NAME>Sinzo Abe</PREMIER_NAME> 1234(2 rows) 1235 1236SELECT * FROM xmltable('/root' passing '<root><element>a1a<!-- aaaa -->a2a<?aaaaa?> <!--z--> bbbb<x>xxx</x>cccc</element></root>' COLUMNS element text); 1237 element 1238------------------- 1239 a1aa2a bbbbcccc 1240(1 row) 1241 1242SELECT * 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 1243ERROR: more than one value returned by column XPath expression 1244-- CDATA test 1245select * from xmltable('r' passing '<d><r><c><![CDATA[<hello> &"<>!<a>foo</a>]]></c></r><r><c>2</c></r></d>' columns c text); 1246 c 1247------------------------- 1248 <hello> &"<>!<a>foo</a> 1249 2 1250(2 rows) 1251 1252-- XML builtin entities 1253SELECT * 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); 1254 ent 1255----- 1256 ' 1257 " 1258 & 1259 < 1260 > 1261(5 rows) 1262 1263SELECT * 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); 1264 ent 1265------------------ 1266 <ent>'</ent> 1267 <ent>"</ent> 1268 <ent>&</ent> 1269 <ent><</ent> 1270 <ent>></ent> 1271(5 rows) 1272 1273EXPLAIN (VERBOSE, COSTS OFF) 1274SELECT xmltable.* 1275 FROM (SELECT data FROM xmldata) x, 1276 LATERAL XMLTABLE('/ROWS/ROW' 1277 PASSING data 1278 COLUMNS id int PATH '@id', 1279 _id FOR ORDINALITY, 1280 country_name text PATH 'COUNTRY_NAME' NOT NULL, 1281 country_id text PATH 'COUNTRY_ID', 1282 region_id int PATH 'REGION_ID', 1283 size float PATH 'SIZE', 1284 unit text PATH 'SIZE/@unit', 1285 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); 1286 QUERY PLAN 1287----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1288 Nested Loop 1289 Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name 1290 -> Seq Scan on public.xmldata 1291 Output: xmldata.data 1292 -> Table Function Scan on "xmltable" 1293 Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name 1294 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)) 1295(7 rows) 1296 1297-- test qual 1298SELECT 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'; 1299 COUNTRY_NAME | REGION_ID 1300--------------+----------- 1301 Japan | 3 1302(1 row) 1303 1304EXPLAIN (VERBOSE, COSTS OFF) 1305SELECT 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'; 1306 QUERY PLAN 1307---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1308 Nested Loop 1309 Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID" 1310 -> Seq Scan on public.xmldata 1311 Output: xmldata.data 1312 -> Table Function Scan on "xmltable" 1313 Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID" 1314 Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer) 1315 Filter: ("xmltable"."COUNTRY_NAME" = 'Japan'::text) 1316(8 rows) 1317 1318-- should to work with more data 1319INSERT INTO xmldata VALUES('<ROWS> 1320<ROW id="10"> 1321 <COUNTRY_ID>CZ</COUNTRY_ID> 1322 <COUNTRY_NAME>Czech Republic</COUNTRY_NAME> 1323 <REGION_ID>2</REGION_ID><PREMIER_NAME>Milos Zeman</PREMIER_NAME> 1324</ROW> 1325<ROW id="11"> 1326 <COUNTRY_ID>DE</COUNTRY_ID> 1327 <COUNTRY_NAME>Germany</COUNTRY_NAME> 1328 <REGION_ID>2</REGION_ID> 1329</ROW> 1330<ROW id="12"> 1331 <COUNTRY_ID>FR</COUNTRY_ID> 1332 <COUNTRY_NAME>France</COUNTRY_NAME> 1333 <REGION_ID>2</REGION_ID> 1334</ROW> 1335</ROWS>'); 1336INSERT INTO xmldata VALUES('<ROWS> 1337<ROW id="20"> 1338 <COUNTRY_ID>EG</COUNTRY_ID> 1339 <COUNTRY_NAME>Egypt</COUNTRY_NAME> 1340 <REGION_ID>1</REGION_ID> 1341</ROW> 1342<ROW id="21"> 1343 <COUNTRY_ID>SD</COUNTRY_ID> 1344 <COUNTRY_NAME>Sudan</COUNTRY_NAME> 1345 <REGION_ID>1</REGION_ID> 1346</ROW> 1347</ROWS>'); 1348SELECT xmltable.* 1349 FROM (SELECT data FROM xmldata) x, 1350 LATERAL XMLTABLE('/ROWS/ROW' 1351 PASSING data 1352 COLUMNS id int PATH '@id', 1353 _id FOR ORDINALITY, 1354 country_name text PATH 'COUNTRY_NAME' NOT NULL, 1355 country_id text PATH 'COUNTRY_ID', 1356 region_id int PATH 'REGION_ID', 1357 size float PATH 'SIZE', 1358 unit text PATH 'SIZE/@unit', 1359 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); 1360 id | _id | country_name | country_id | region_id | size | unit | premier_name 1361----+-----+----------------+------------+-----------+------+------+--------------- 1362 1 | 1 | Australia | AU | 3 | | | not specified 1363 2 | 2 | China | CN | 3 | | | not specified 1364 3 | 3 | HongKong | HK | 3 | | | not specified 1365 4 | 4 | India | IN | 3 | | | not specified 1366 5 | 5 | Japan | JP | 3 | | | Sinzo Abe 1367 6 | 6 | Singapore | SG | 3 | 791 | km | not specified 1368 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman 1369 11 | 2 | Germany | DE | 2 | | | not specified 1370 12 | 3 | France | FR | 2 | | | not specified 1371 20 | 1 | Egypt | EG | 1 | | | not specified 1372 21 | 2 | Sudan | SD | 1 | | | not specified 1373(11 rows) 1374 1375SELECT xmltable.* 1376 FROM (SELECT data FROM xmldata) x, 1377 LATERAL XMLTABLE('/ROWS/ROW' 1378 PASSING data 1379 COLUMNS id int PATH '@id', 1380 _id FOR ORDINALITY, 1381 country_name text PATH 'COUNTRY_NAME' NOT NULL, 1382 country_id text PATH 'COUNTRY_ID', 1383 region_id int PATH 'REGION_ID', 1384 size float PATH 'SIZE', 1385 unit text PATH 'SIZE/@unit', 1386 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') 1387 WHERE region_id = 2; 1388 id | _id | country_name | country_id | region_id | size | unit | premier_name 1389----+-----+----------------+------------+-----------+------+------+--------------- 1390 10 | 1 | Czech Republic | CZ | 2 | | | Milos Zeman 1391 11 | 2 | Germany | DE | 2 | | | not specified 1392 12 | 3 | France | FR | 2 | | | not specified 1393(3 rows) 1394 1395EXPLAIN (VERBOSE, COSTS OFF) 1396SELECT xmltable.* 1397 FROM (SELECT data FROM xmldata) x, 1398 LATERAL XMLTABLE('/ROWS/ROW' 1399 PASSING data 1400 COLUMNS id int PATH '@id', 1401 _id FOR ORDINALITY, 1402 country_name text PATH 'COUNTRY_NAME' NOT NULL, 1403 country_id text PATH 'COUNTRY_ID', 1404 region_id int PATH 'REGION_ID', 1405 size float PATH 'SIZE', 1406 unit text PATH 'SIZE/@unit', 1407 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') 1408 WHERE region_id = 2; 1409 QUERY PLAN 1410----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1411 Nested Loop 1412 Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name 1413 -> Seq Scan on public.xmldata 1414 Output: xmldata.data 1415 -> Table Function Scan on "xmltable" 1416 Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name 1417 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)) 1418 Filter: ("xmltable".region_id = 2) 1419(8 rows) 1420 1421-- should fail, NULL value 1422SELECT xmltable.* 1423 FROM (SELECT data FROM xmldata) x, 1424 LATERAL XMLTABLE('/ROWS/ROW' 1425 PASSING data 1426 COLUMNS id int PATH '@id', 1427 _id FOR ORDINALITY, 1428 country_name text PATH 'COUNTRY_NAME' NOT NULL, 1429 country_id text PATH 'COUNTRY_ID', 1430 region_id int PATH 'REGION_ID', 1431 size float PATH 'SIZE' NOT NULL, 1432 unit text PATH 'SIZE/@unit', 1433 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); 1434ERROR: null is not allowed in column "size" 1435-- if all is ok, then result is empty 1436-- one line xml test 1437WITH 1438 x AS (SELECT proname, proowner, procost::numeric, pronargs, 1439 array_to_string(proargnames,',') as proargnames, 1440 case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes 1441 FROM pg_proc WHERE proname = 'f_leak'), 1442 y AS (SELECT xmlelement(name proc, 1443 xmlforest(proname, proowner, 1444 procost, pronargs, 1445 proargnames, proargtypes)) as proc 1446 FROM x), 1447 z AS (SELECT xmltable.* 1448 FROM y, 1449 LATERAL xmltable('/proc' PASSING proc 1450 COLUMNS proname name, 1451 proowner oid, 1452 procost float, 1453 pronargs int, 1454 proargnames text, 1455 proargtypes text)) 1456 SELECT * FROM z 1457 EXCEPT SELECT * FROM x; 1458 proname | proowner | procost | pronargs | proargnames | proargtypes 1459---------+----------+---------+----------+-------------+------------- 1460(0 rows) 1461 1462-- multi line xml test, result should be empty too 1463WITH 1464 x AS (SELECT proname, proowner, procost::numeric, pronargs, 1465 array_to_string(proargnames,',') as proargnames, 1466 case when proargtypes <> '' then array_to_string(proargtypes::oid[],',') end as proargtypes 1467 FROM pg_proc), 1468 y AS (SELECT xmlelement(name data, 1469 xmlagg(xmlelement(name proc, 1470 xmlforest(proname, proowner, procost, 1471 pronargs, proargnames, proargtypes)))) as doc 1472 FROM x), 1473 z AS (SELECT xmltable.* 1474 FROM y, 1475 LATERAL xmltable('/data/proc' PASSING doc 1476 COLUMNS proname name, 1477 proowner oid, 1478 procost float, 1479 pronargs int, 1480 proargnames text, 1481 proargtypes text)) 1482 SELECT * FROM z 1483 EXCEPT SELECT * FROM x; 1484 proname | proowner | procost | pronargs | proargnames | proargtypes 1485---------+----------+---------+----------+-------------+------------- 1486(0 rows) 1487 1488CREATE TABLE xmltest2(x xml, _path text); 1489INSERT INTO xmltest2 VALUES('<d><r><ac>1</ac></r></d>', 'A'); 1490INSERT INTO xmltest2 VALUES('<d><r><bc>2</bc></r></d>', 'B'); 1491INSERT INTO xmltest2 VALUES('<d><r><cc>3</cc></r></d>', 'C'); 1492INSERT INTO xmltest2 VALUES('<d><r><dc>2</dc></r></d>', 'D'); 1493SELECT xmltable.* FROM xmltest2, LATERAL xmltable('/d/r' PASSING x COLUMNS a int PATH '' || lower(_path) || 'c'); 1494 a 1495--- 1496 1 1497 2 1498 3 1499 2 1500(4 rows) 1501 1502SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH '.'); 1503 a 1504--- 1505 1 1506 2 1507 3 1508 2 1509(4 rows) 1510 1511SELECT xmltable.* FROM xmltest2, LATERAL xmltable(('/d/r/' || lower(_path) || 'c') PASSING x COLUMNS a int PATH 'x' DEFAULT ascii(_path) - 54); 1512 a 1513---- 1514 11 1515 12 1516 13 1517 14 1518(4 rows) 1519 1520