xml.out 12.6 KB
Newer Older
1 2 3 4 5 6 7
CREATE TABLE xmltest (
    id int,
    data xml
);
INSERT INTO xmltest VALUES (1, '<value>one</value>');
INSERT INTO xmltest VALUES (2, '<value>two</value>');
INSERT INTO xmltest VALUES (3, '<wrong');
8
ERROR:  invalid XML content
9 10 11
DETAIL:  Entity: line 1: parser error : Couldn't find end of Start Tag wrong line 1
<wrong
      ^
12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
SELECT * FROM xmltest;
 id |        data        
----+--------------------
  1 | <value>one</value>
  2 | <value>two</value>
(2 rows)

SELECT xmlcomment('test');
 xmlcomment  
-------------
 <!--test-->
(1 row)

SELECT xmlcomment('-test');
  xmlcomment  
--------------
 <!---test-->
(1 row)

SELECT xmlcomment('test-');
ERROR:  invalid XML comment
SELECT xmlcomment('--test');
ERROR:  invalid XML comment
SELECT xmlcomment('te st');
  xmlcomment  
--------------
 <!--te st-->
(1 row)

SELECT xmlconcat(xmlcomment('hello'),
                 xmlelement(NAME qux, 'foo'),
                 xmlcomment('world'));
               xmlconcat                
----------------------------------------
 <!--hello--><qux>foo</qux><!--world-->
(1 row)

SELECT xmlconcat('hello', 'you');
 xmlconcat 
-----------
 helloyou
(1 row)

SELECT xmlconcat(1, 2);
56
ERROR:  argument of XMLCONCAT must be type xml, not type integer
57
SELECT xmlconcat('bad', '<syntax');
58
ERROR:  invalid XML content
59 60 61
DETAIL:  Entity: line 1: parser error : Couldn't find end of Start Tag syntax line 1
<syntax
       ^
62
SELECT xmlconcat('<foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
63 64 65 66 67 68
  xmlconcat   
--------------
 <foo/><bar/>
(1 row)

SELECT xmlconcat('<?xml version="1.1"?><foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
69 70 71
             xmlconcat             
-----------------------------------
 <?xml version="1.1"?><foo/><bar/>
72 73
(1 row)

74 75 76 77 78 79 80 81 82 83
SELECT xmlelement(name element,
                  xmlattributes (1 as one, 'deuce' as two),
                  'content');
                   xmlelement                   
------------------------------------------------
 <element one="1" two="deuce">content</element>
(1 row)

SELECT xmlelement(name element,
                  xmlattributes ('unnamed and wrong'));
84
ERROR:  unnamed XML attribute value must be a column reference
85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
SELECT xmlelement(name element, xmlelement(name nested, 'stuff'));
                xmlelement                 
-------------------------------------------
 <element><nested>stuff</nested></element>
(1 row)

SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp;
                              xmlelement                              
----------------------------------------------------------------------
 <employee><name>sharon</name><age>25</age><pay>1000</pay></employee>
 <employee><name>sam</name><age>30</age><pay>2000</pay></employee>
 <employee><name>bill</name><age>20</age><pay>1000</pay></employee>
 <employee><name>jeff</name><age>23</age><pay>600</pay></employee>
 <employee><name>cim</name><age>30</age><pay>400</pay></employee>
 <employee><name>linda</name><age>19</age><pay>100</pay></employee>
(6 rows)

102 103
SELECT xmlelement(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a));
ERROR:  XML attribute name "a" appears more than once
104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139
SELECT xmlelement(name num, 37);
  xmlelement   
---------------
 <num>37</num>
(1 row)

SELECT xmlelement(name foo, text 'bar');
   xmlelement   
----------------
 <foo>bar</foo>
(1 row)

SELECT xmlelement(name foo, xml 'bar');
   xmlelement   
----------------
 <foo>bar</foo>
(1 row)

SELECT xmlelement(name foo, text 'b<a/>r');
       xmlelement        
-------------------------
 <foo>b&lt;a/&gt;r</foo>
(1 row)

SELECT xmlelement(name foo, xml 'b<a/>r');
    xmlelement     
-------------------
 <foo>b<a/>r</foo>
(1 row)

SELECT xmlelement(name foo, array[1, 2, 3]);
                               xmlelement                                
-------------------------------------------------------------------------
 <foo><element>1</element><element>2</element><element>3</element></foo>
(1 row)

140 141 142 143 144 145 146 147 148 149 150 151 152 153
SET xmlbinary TO base64;
SELECT xmlelement(name foo, bytea 'bar');
   xmlelement    
-----------------
 <foo>YmFy</foo>
(1 row)

SET xmlbinary TO hex;
SELECT xmlelement(name foo, bytea 'bar');
    xmlelement     
-------------------
 <foo>626172</foo>
(1 row)

154 155 156 157 158 159 160 161 162 163 164 165 166 167
SELECT xmlparse(content 'abc');
 xmlparse 
----------
 abc
(1 row)

SELECT xmlparse(content '<abc>x</abc>');
   xmlparse   
--------------
 <abc>x</abc>
(1 row)

SELECT xmlparse(document 'abc');
ERROR:  invalid XML document
168 169 170
DETAIL:  Entity: line 1: parser error : Start tag expected, '<' not found
abc
^
171 172 173 174 175 176
SELECT xmlparse(document '<abc>x</abc>');
   xmlparse   
--------------
 <abc>x</abc>
(1 row)

177 178 179 180 181 182
SELECT xmlpi(name foo);
  xmlpi  
---------
 <?foo?>
(1 row)

183
SELECT xmlpi(name xml);
184
ERROR:  invalid XML processing instruction
185 186 187 188 189 190 191
DETAIL:  XML processing instruction target name cannot be "xml".
SELECT xmlpi(name xmlstuff);
    xmlpi     
--------------
 <?xmlstuff?>
(1 row)

192 193 194 195 196 197 198 199 200
SELECT xmlpi(name foo, 'bar');
    xmlpi    
-------------
 <?foo bar?>
(1 row)

SELECT xmlpi(name foo, 'in?>valid');
ERROR:  invalid XML processing instruction
DETAIL:  XML processing instruction cannot contain "?>".
201 202 203 204 205 206
SELECT xmlpi(name foo, null);
 xmlpi 
-------
 
(1 row)

207
SELECT xmlpi(name xml, null);
208
ERROR:  invalid XML processing instruction
209 210 211 212 213 214 215 216 217 218 219 220 221
DETAIL:  XML processing instruction target name cannot be "xml".
SELECT xmlpi(name xmlstuff, null);
 xmlpi 
-------
 
(1 row)

SELECT xmlpi(name "xml-stylesheet", 'href="mystyle.css" type="text/css"');
                         xmlpi                         
-------------------------------------------------------
 <?xml-stylesheet href="mystyle.css" type="text/css"?>
(1 row)

222 223 224 225 226 227
SELECT xmlpi(name foo, '   bar');
    xmlpi    
-------------
 <?foo bar?>
(1 row)

228
SELECT xmlroot(xml '<foo/>', version no value, standalone no value);
229
 xmlroot 
230
---------
231 232 233 234
 <foo/>
(1 row)

SELECT xmlroot(xml '<foo/>', version '2.0');
235 236 237 238 239 240 241 242 243 244 245 246 247 248 249
           xmlroot           
-----------------------------
 <?xml version="2.0"?><foo/>
(1 row)

SELECT xmlroot(xml '<foo/>', version no value, standalone yes);
                   xmlroot                    
----------------------------------------------
 <?xml version="1.0" standalone="yes"?><foo/>
(1 row)

SELECT xmlroot(xml '<?xml version="1.1"?><foo/>', version no value, standalone yes);
                   xmlroot                    
----------------------------------------------
 <?xml version="1.0" standalone="yes"?><foo/>
250 251 252
(1 row)

SELECT xmlroot(xmlroot(xml '<foo/>', version '1.0'), version '1.1', standalone no);
253 254 255 256 257 258 259 260 261 262 263 264 265 266
                   xmlroot                   
---------------------------------------------
 <?xml version="1.1" standalone="no"?><foo/>
(1 row)

SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no);
                   xmlroot                   
---------------------------------------------
 <?xml version="1.0" standalone="no"?><foo/>
(1 row)

SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no value);
 xmlroot 
---------
267
 <foo/>
268 269 270 271 272 273
(1 row)

SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value);
                   xmlroot                    
----------------------------------------------
 <?xml version="1.0" standalone="yes"?><foo/>
274 275
(1 row)

276 277 278 279 280 281 282 283 284 285 286 287 288 289 290
SELECT xmlroot (
  xmlelement (
    name gazonk,
    xmlattributes (
      'val' AS name,
      1 + 1 AS num
    ),
    xmlelement (
      NAME qux,
      'foo'
    )
  ),
  version '1.0',
  standalone yes
);
291 292 293
                                         xmlroot                                          
------------------------------------------------------------------------------------------
 <?xml version="1.0" standalone="yes"?><gazonk name="val" num="2"><qux>foo</qux></gazonk>
294 295
(1 row)

296 297
SELECT xmlserialize(content data as character varying(20)) FROM xmltest;
    xmlserialize    
298 299 300 301 302
--------------------
 <value>one</value>
 <value>two</value>
(2 rows)

303 304 305 306 307 308 309 310
SELECT xmlserialize(content 'good' as char(10));
 xmlserialize 
--------------
 good      
(1 row)

SELECT xmlserialize(document 'bad' as text);
ERROR:  not an XML document
311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336
SELECT xml '<foo>bar</foo>' IS DOCUMENT;
 ?column? 
----------
 t
(1 row)

SELECT xml '<foo>bar</foo><bar>foo</bar>' IS DOCUMENT;
 ?column? 
----------
 f
(1 row)

SELECT xml '<abc/>' IS NOT DOCUMENT;
 ?column? 
----------
 f
(1 row)

SELECT xml 'abc' IS NOT DOCUMENT;
 ?column? 
----------
 t
(1 row)

SELECT '<>' IS NOT DOCUMENT;
ERROR:  invalid XML content
337 338 339
DETAIL:  Entity: line 1: parser error : StartTag: invalid element name
<>
 ^
340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357
SELECT xmlagg(data) FROM xmltest;
                xmlagg                
--------------------------------------
 <value>one</value><value>two</value>
(1 row)

SELECT xmlagg(data) FROM xmltest WHERE id > 10;
 xmlagg 
--------
 
(1 row)

SELECT xmlelement(name employees, xmlagg(xmlelement(name name, name))) FROM emp;
                                                           xmlelement                                                           
--------------------------------------------------------------------------------------------------------------------------------
 <employees><name>sharon</name><name>sam</name><name>bill</name><name>jeff</name><name>cim</name><name>linda</name></employees>
(1 row)

358 359 360 361 362 363 364 365 366 367 368 369 370
-- Check mapping SQL identifier to XML name
SELECT xmlpi(name ":::_xml_abc135.%-&_");
                      xmlpi                      
-------------------------------------------------
 <?_x003A_::_x005F_xml_abc135._x0025_-_x0026__?>
(1 row)

SELECT xmlpi(name "123");
     xmlpi     
---------------
 <?_x0031_23?>
(1 row)

371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396
PREPARE foo (xml) AS SELECT xmlconcat('<foo/>', $1);
SET XML OPTION DOCUMENT;
EXECUTE foo ('<bar/>');
  xmlconcat   
--------------
 <foo/><bar/>
(1 row)

EXECUTE foo ('bad');
ERROR:  invalid XML document
DETAIL:  Entity: line 1: parser error : Start tag expected, '<' not found
bad
^
SET XML OPTION CONTENT;
EXECUTE foo ('<bar/>');
  xmlconcat   
--------------
 <foo/><bar/>
(1 row)

EXECUTE foo ('good');
 xmlconcat  
------------
 <foo/>good
(1 row)

397 398 399 400 401 402 403 404 405 406
-- Test backwards parsing
CREATE VIEW xmlview1 AS SELECT xmlcomment('test');
CREATE VIEW xmlview2 AS SELECT xmlconcat('hello', 'you');
CREATE VIEW xmlview3 AS SELECT xmlelement(name element, xmlattributes (1 as ":one:", 'deuce' as two), 'content&');
CREATE VIEW xmlview4 AS SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp;
CREATE VIEW xmlview5 AS SELECT xmlparse(content '<abc>x</abc>');
CREATE VIEW xmlview6 AS SELECT xmlpi(name foo, 'bar');
CREATE VIEW xmlview7 AS SELECT xmlroot(xml '<foo/>', version no value, standalone yes);
CREATE VIEW xmlview8 AS SELECT xmlserialize(content 'good' as char(10));
CREATE VIEW xmlview9 AS SELECT xmlserialize(content 'good' as text);
407 408
SELECT table_name, view_definition FROM information_schema.views
  WHERE table_name LIKE 'xmlview%' ORDER BY 1;
409 410
 table_name |                                                      view_definition                                                       
------------+----------------------------------------------------------------------------------------------------------------------------
411
 xmlview1   | SELECT xmlcomment('test'::text) AS xmlcomment;
412
 xmlview2   | SELECT XMLCONCAT('hello'::xml, 'you'::xml) AS "xmlconcat";
413
 xmlview3   | SELECT XMLELEMENT(NAME element, XMLATTRIBUTES(1 AS ":one:", 'deuce' AS two), 'content&') AS "xmlelement";
414
 xmlview4   | SELECT XMLELEMENT(NAME employee, XMLFOREST(emp.name AS name, emp.age AS age, emp.salary AS pay)) AS "xmlelement" FROM emp;
415 416
 xmlview5   | SELECT XMLPARSE(CONTENT '<abc>x</abc>'::text STRIP WHITESPACE) AS "xmlparse";
 xmlview6   | SELECT XMLPI(NAME foo, 'bar'::text) AS "xmlpi";
417 418 419
 xmlview7   | SELECT XMLROOT('<foo/>'::xml, VERSION NO VALUE, STANDALONE YES) AS "xmlroot";
 xmlview8   | SELECT (XMLSERIALIZE(CONTENT 'good'::xml AS character(10)))::character(10) AS "xmlserialize";
 xmlview9   | SELECT XMLSERIALIZE(CONTENT 'good'::xml AS text) AS "xmlserialize";
420 421
(9 rows)

422
-- Text XPath expressions evaluation
Peter Eisentraut's avatar
Peter Eisentraut committed
423 424
SELECT xpath('/value', data) FROM xmltest;
        xpath         
425 426 427
----------------------
 {<value>one</value>}
 {<value>two</value>}
428 429
(2 rows)

Peter Eisentraut's avatar
Peter Eisentraut committed
430
SELECT xpath(NULL, NULL) IS NULL FROM xmltest;
431 432 433 434 435 436
 ?column? 
----------
 t
 t
(2 rows)

Peter Eisentraut's avatar
Peter Eisentraut committed
437
SELECT xpath('', '<!-- error -->');
438
ERROR:  empty XPath expression
Peter Eisentraut's avatar
Peter Eisentraut committed
439 440 441
CONTEXT:  SQL function "xpath" statement 1
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>');
     xpath      
442 443 444 445
----------------
 {"number one"}
(1 row)

Peter Eisentraut's avatar
Peter Eisentraut committed
446 447 448
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']]);
 xpath 
-------
449 450 451
 {1,2}
(1 row)

Peter Eisentraut's avatar
Peter Eisentraut committed
452 453
SELECT xpath('//b', '<a>one <b>two</b> three <b>etc</b></a>');
          xpath          
454 455 456 457
-------------------------
 {<b>two</b>,<b>etc</b>}
(1 row)