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: Entity: line 1: parser error : Couldn't find end of Start Tag wrong line 1
14 SELECT * FROM xmltest;
16 ----+--------------------
17 1 | <value>one</value>
18 2 | <value>two</value>
21 SELECT xmlcomment('test');
27 SELECT xmlcomment('-test');
33 SELECT xmlcomment('test-');
34 ERROR: invalid XML comment
35 SELECT xmlcomment('--test');
36 ERROR: invalid XML comment
37 SELECT xmlcomment('te st');
43 SELECT xmlconcat(xmlcomment('hello'),
44 xmlelement(NAME qux, 'foo'),
47 ----------------------------------------
48 <!--hello--><qux>foo</qux><!--world-->
51 SELECT xmlconcat('hello', 'you');
57 SELECT xmlconcat(1, 2);
58 ERROR: argument of XMLCONCAT must be type xml, not type integer
59 LINE 1: SELECT xmlconcat(1, 2);
61 SELECT xmlconcat('bad', '<syntax');
62 ERROR: invalid XML content
63 LINE 1: SELECT xmlconcat('bad', '<syntax');
65 DETAIL: Entity: line 1: parser error : Couldn't find end of Start Tag syntax line 1
68 SELECT xmlconcat('<foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
74 SELECT xmlconcat('<?xml version="1.1"?><foo/>', NULL, '<?xml version="1.1" standalone="no"?><bar/>');
76 -----------------------------------
77 <?xml version="1.1"?><foo/><bar/>
80 SELECT xmlelement(name element,
81 xmlattributes (1 as one, 'deuce' as two),
84 ------------------------------------------------
85 <element one="1" two="deuce">content</element>
88 SELECT xmlelement(name element,
89 xmlattributes ('unnamed and wrong'));
90 ERROR: unnamed XML attribute value must be a column reference
91 LINE 2: xmlattributes ('unnamed and wrong'));
93 SELECT xmlelement(name element, xmlelement(name nested, 'stuff'));
95 -------------------------------------------
96 <element><nested>stuff</nested></element>
99 SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp;
101 ----------------------------------------------------------------------
102 <employee><name>sharon</name><age>25</age><pay>1000</pay></employee>
103 <employee><name>sam</name><age>30</age><pay>2000</pay></employee>
104 <employee><name>bill</name><age>20</age><pay>1000</pay></employee>
105 <employee><name>jeff</name><age>23</age><pay>600</pay></employee>
106 <employee><name>cim</name><age>30</age><pay>400</pay></employee>
107 <employee><name>linda</name><age>19</age><pay>100</pay></employee>
110 SELECT xmlelement(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a));
111 ERROR: XML attribute name "a" appears more than once
112 LINE 1: ...ment(name duplicate, xmlattributes(1 as a, 2 as b, 3 as a));
114 SELECT xmlelement(name num, 37);
120 SELECT xmlelement(name foo, text 'bar');
126 SELECT xmlelement(name foo, xml 'bar');
132 SELECT xmlelement(name foo, text 'b<a/>r');
134 -------------------------
135 <foo>b<a/>r</foo>
138 SELECT xmlelement(name foo, xml 'b<a/>r');
144 SELECT xmlelement(name foo, array[1, 2, 3]);
146 -------------------------------------------------------------------------
147 <foo><element>1</element><element>2</element><element>3</element></foo>
150 SET xmlbinary TO base64;
151 SELECT xmlelement(name foo, bytea 'bar');
157 SET xmlbinary TO hex;
158 SELECT xmlelement(name foo, bytea 'bar');
164 SELECT xmlparse(content 'abc');
170 SELECT xmlparse(content '<abc>x</abc>');
176 SELECT xmlparse(document 'abc');
177 ERROR: invalid XML document
178 DETAIL: Entity: line 1: parser error : Start tag expected, '<' not found
181 SELECT xmlparse(document '<abc>x</abc>');
187 SELECT xmlpi(name foo);
193 SELECT xmlpi(name xml);
194 ERROR: invalid XML processing instruction
195 DETAIL: XML processing instruction target name cannot be "xml".
196 SELECT xmlpi(name xmlstuff);
202 SELECT xmlpi(name foo, 'bar');
208 SELECT xmlpi(name foo, 'in?>valid');
209 ERROR: invalid XML processing instruction
210 DETAIL: XML processing instruction cannot contain "?>".
211 SELECT xmlpi(name foo, null);
217 SELECT xmlpi(name xml, null);
218 ERROR: invalid XML processing instruction
219 DETAIL: XML processing instruction target name cannot be "xml".
220 SELECT xmlpi(name xmlstuff, null);
226 SELECT xmlpi(name "xml-stylesheet", 'href="mystyle.css" type="text/css"');
228 -------------------------------------------------------
229 <?xml-stylesheet href="mystyle.css" type="text/css"?>
232 SELECT xmlpi(name foo, ' bar');
238 SELECT xmlroot(xml '<foo/>', version no value, standalone no value);
244 SELECT xmlroot(xml '<foo/>', version '2.0');
246 -----------------------------
247 <?xml version="2.0"?><foo/>
250 SELECT xmlroot(xml '<foo/>', version no value, standalone yes);
252 ----------------------------------------------
253 <?xml version="1.0" standalone="yes"?><foo/>
256 SELECT xmlroot(xml '<?xml version="1.1"?><foo/>', version no value, standalone yes);
258 ----------------------------------------------
259 <?xml version="1.0" standalone="yes"?><foo/>
262 SELECT xmlroot(xmlroot(xml '<foo/>', version '1.0'), version '1.1', standalone no);
264 ---------------------------------------------
265 <?xml version="1.1" standalone="no"?><foo/>
268 SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no);
270 ---------------------------------------------
271 <?xml version="1.0" standalone="no"?><foo/>
274 SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value, standalone no value);
280 SELECT xmlroot('<?xml version="1.1" standalone="yes"?><foo/>', version no value);
282 ----------------------------------------------
283 <?xml version="1.0" standalone="yes"?><foo/>
302 ------------------------------------------------------------------------------------------
303 <?xml version="1.0" standalone="yes"?><gazonk name="val" num="2"><qux>foo</qux></gazonk>
306 SELECT xmlserialize(content data as character varying(20)) FROM xmltest;
313 SELECT xmlserialize(content 'good' as char(10));
319 SELECT xmlserialize(document 'bad' as text);
320 ERROR: not an XML document
321 SELECT xml '<foo>bar</foo>' IS DOCUMENT;
327 SELECT xml '<foo>bar</foo><bar>foo</bar>' IS DOCUMENT;
333 SELECT xml '<abc/>' IS NOT DOCUMENT;
339 SELECT xml 'abc' IS NOT DOCUMENT;
345 SELECT '<>' IS NOT DOCUMENT;
346 ERROR: invalid XML content
347 LINE 1: SELECT '<>' IS NOT DOCUMENT;
349 DETAIL: Entity: line 1: parser error : StartTag: invalid element name
352 SELECT xmlagg(data) FROM xmltest;
354 --------------------------------------
355 <value>one</value><value>two</value>
358 SELECT xmlagg(data) FROM xmltest WHERE id > 10;
364 SELECT xmlelement(name employees, xmlagg(xmlelement(name name, name))) FROM emp;
366 --------------------------------------------------------------------------------------------------------------------------------
367 <employees><name>sharon</name><name>sam</name><name>bill</name><name>jeff</name><name>cim</name><name>linda</name></employees>
370 -- Check mapping SQL identifier to XML name
371 SELECT xmlpi(name ":::_xml_abc135.%-&_");
373 -------------------------------------------------
374 <?_x003A_::_x005F_xml_abc135._x0025_-_x0026__?>
377 SELECT xmlpi(name "123");
383 PREPARE foo (xml) AS SELECT xmlconcat('<foo/>', $1);
384 SET XML OPTION DOCUMENT;
385 EXECUTE foo ('<bar/>');
392 ERROR: invalid XML document
393 LINE 1: EXECUTE foo ('bad');
395 DETAIL: Entity: line 1: parser error : Start tag expected, '<' not found
398 SET XML OPTION CONTENT;
399 EXECUTE foo ('<bar/>');
405 EXECUTE foo ('good');
411 -- Test backwards parsing
412 CREATE VIEW xmlview1 AS SELECT xmlcomment('test');
413 CREATE VIEW xmlview2 AS SELECT xmlconcat('hello', 'you');
414 CREATE VIEW xmlview3 AS SELECT xmlelement(name element, xmlattributes (1 as ":one:", 'deuce' as two), 'content&');
415 CREATE VIEW xmlview4 AS SELECT xmlelement(name employee, xmlforest(name, age, salary as pay)) FROM emp;
416 CREATE VIEW xmlview5 AS SELECT xmlparse(content '<abc>x</abc>');
417 CREATE VIEW xmlview6 AS SELECT xmlpi(name foo, 'bar');
418 CREATE VIEW xmlview7 AS SELECT xmlroot(xml '<foo/>', version no value, standalone yes);
419 CREATE VIEW xmlview8 AS SELECT xmlserialize(content 'good' as char(10));
420 CREATE VIEW xmlview9 AS SELECT xmlserialize(content 'good' as text);
421 SELECT table_name, view_definition FROM information_schema.views
422 WHERE table_name LIKE 'xmlview%' ORDER BY 1;
423 table_name | view_definition
424 ------------+----------------------------------------------------------------------------------------------------------------------------
425 xmlview1 | SELECT xmlcomment('test'::text) AS xmlcomment;
426 xmlview2 | SELECT XMLCONCAT('hello'::xml, 'you'::xml) AS "xmlconcat";
427 xmlview3 | SELECT XMLELEMENT(NAME element, XMLATTRIBUTES(1 AS ":one:", 'deuce' AS two), 'content&') AS "xmlelement";
428 xmlview4 | SELECT XMLELEMENT(NAME employee, XMLFOREST(emp.name AS name, emp.age AS age, emp.salary AS pay)) AS "xmlelement" FROM emp;
429 xmlview5 | SELECT XMLPARSE(CONTENT '<abc>x</abc>'::text STRIP WHITESPACE) AS "xmlparse";
430 xmlview6 | SELECT XMLPI(NAME foo, 'bar'::text) AS "xmlpi";
431 xmlview7 | SELECT XMLROOT('<foo/>'::xml, VERSION NO VALUE, STANDALONE YES) AS "xmlroot";
432 xmlview8 | SELECT (XMLSERIALIZE(CONTENT 'good'::xml AS character(10)))::character(10) AS "xmlserialize";
433 xmlview9 | SELECT XMLSERIALIZE(CONTENT 'good'::xml AS text) AS "xmlserialize";
436 -- Text XPath expressions evaluation
437 SELECT xpath('/value', data) FROM xmltest;
439 ----------------------
444 SELECT xpath(NULL, NULL) IS NULL FROM xmltest;
451 SELECT xpath('', '<!-- error -->');
452 ERROR: empty XPath expression
453 CONTEXT: SQL function "xpath" statement 1
454 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>');
460 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']]);
466 SELECT xpath('//b', '<a>one <b>two</b> three <b>etc</b></a>');
468 -------------------------
469 {<b>two</b>,<b>etc</b>}