| blob | ccc5460663019bcfe280802567c73f3214b8d561 |
1 CREATE SCHEMA testxmlschema;
2 CREATE TABLE testxmlschema.test1 (a int, b text);
3 INSERT INTO testxmlschema.test1 VALUES (1, 'one'), (2, 'two'), (-1, null);
4 CREATE DOMAIN testxmldomain AS varchar;
5 CREATE TABLE testxmlschema.test2 (z int, y varchar(500), x char(6),
6 w numeric(9,2), v smallint, u bigint, t real,
7 s time, stz timetz, r timestamp, rtz timestamptz, q date,
8 p xml, o testxmldomain, n bool, m bytea, aaa text);
9 ALTER TABLE testxmlschema.test2 DROP COLUMN aaa;
10 INSERT INTO testxmlschema.test2 VALUES (55, 'abc', 'def',
11 98.6, 2, 999, 0,
12 '21:07', '21:11 +05', '2009-06-08 21:07:30', '2009-06-08 21:07:30 -07', '2009-06-08',
13 NULL, 'ABC', true, 'XYZ');
14 SELECT table_to_xml('testxmlschema.test1', false, false, '');
15 table_to_xml
16 ---------------------------------------------------------------
17 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
18 +
19 <row> +
20 <a>1</a> +
21 <b>one</b> +
22 </row> +
23 +
24 <row> +
25 <a>2</a> +
26 <b>two</b> +
27 </row> +
28 +
29 <row> +
30 <a>-1</a> +
31 </row> +
32 +
33 </test1> +
35 (1 row)
37 SELECT table_to_xml('testxmlschema.test1', true, false, 'foo');
38 table_to_xml
39 ---------------------------------------------------------------------------
40 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo">+
41 +
42 <row> +
43 <a>1</a> +
44 <b>one</b> +
45 </row> +
46 +
47 <row> +
48 <a>2</a> +
49 <b>two</b> +
50 </row> +
51 +
52 <row> +
53 <a>-1</a> +
54 <b xsi:nil="true"/> +
55 </row> +
56 +
57 </test1> +
59 (1 row)
61 SELECT table_to_xml('testxmlschema.test1', false, true, '');
62 table_to_xml
63 ---------------------------------------------------------------
64 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
65 <a>1</a> +
66 <b>one</b> +
67 </test1> +
68 +
69 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
70 <a>2</a> +
71 <b>two</b> +
72 </test1> +
73 +
74 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
75 <a>-1</a> +
76 </test1> +
77 +
79 (1 row)
81 SELECT table_to_xml('testxmlschema.test1', true, true, '');
82 table_to_xml
83 ---------------------------------------------------------------
84 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
85 <a>1</a> +
86 <b>one</b> +
87 </test1> +
88 +
89 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
90 <a>2</a> +
91 <b>two</b> +
92 </test1> +
93 +
94 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
95 <a>-1</a> +
96 <b xsi:nil="true"/> +
97 </test1> +
98 +
100 (1 row)
102 SELECT table_to_xml('testxmlschema.test2', false, false, '');
103 table_to_xml
104 ---------------------------------------------------------------
105 <test2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
106 +
107 <row> +
108 <z>55</z> +
109 <y>abc</y> +
110 <x>def </x> +
111 <w>98.60</w> +
112 <v>2</v> +
113 <u>999</u> +
114 <t>0</t> +
115 <s>21:07:00</s> +
116 <stz>21:11:00+05</stz> +
117 <r>2009-06-08T21:07:30</r> +
118 <rtz>2009-06-08T21:07:30-07:00</rtz> +
119 <q>2009-06-08</q> +
120 <o>ABC</o> +
121 <n>true</n> +
122 <m>WFla</m> +
123 </row> +
124 +
125 </test2> +
127 (1 row)
129 SELECT table_to_xmlschema('testxmlschema.test1', false, false, '');
130 table_to_xmlschema
131 -----------------------------------------------------------------------------------------------------------------
132 <xsd:schema +
133 xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +
134 +
135 <xsd:simpleType name="INTEGER"> +
136 <xsd:restriction base="xsd:int"> +
137 <xsd:maxInclusive value="2147483647"/> +
138 <xsd:minInclusive value="-2147483648"/> +
139 </xsd:restriction> +
140 </xsd:simpleType> +
141 +
142 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
143 <xsd:restriction base="xsd:string"> +
144 </xsd:restriction> +
145 </xsd:simpleType> +
146 +
147 <xsd:complexType name="RowType.regression.testxmlschema.test1"> +
148 <xsd:sequence> +
149 <xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element> +
150 <xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element> +
151 </xsd:sequence> +
152 </xsd:complexType> +
153 +
154 <xsd:complexType name="TableType.regression.testxmlschema.test1"> +
155 <xsd:sequence> +
156 <xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>+
157 </xsd:sequence> +
158 </xsd:complexType> +
159 +
160 <xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/> +
161 +
162 </xsd:schema>
163 (1 row)
165 SELECT table_to_xmlschema('testxmlschema.test1', true, false, '');
166 table_to_xmlschema
167 -----------------------------------------------------------------------------------------------------------------
168 <xsd:schema +
169 xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +
170 +
171 <xsd:simpleType name="INTEGER"> +
172 <xsd:restriction base="xsd:int"> +
173 <xsd:maxInclusive value="2147483647"/> +
174 <xsd:minInclusive value="-2147483648"/> +
175 </xsd:restriction> +
176 </xsd:simpleType> +
177 +
178 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
179 <xsd:restriction base="xsd:string"> +
180 </xsd:restriction> +
181 </xsd:simpleType> +
182 +
183 <xsd:complexType name="RowType.regression.testxmlschema.test1"> +
184 <xsd:sequence> +
185 <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> +
186 <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element> +
187 </xsd:sequence> +
188 </xsd:complexType> +
189 +
190 <xsd:complexType name="TableType.regression.testxmlschema.test1"> +
191 <xsd:sequence> +
192 <xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>+
193 </xsd:sequence> +
194 </xsd:complexType> +
195 +
196 <xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/> +
197 +
198 </xsd:schema>
199 (1 row)
201 SELECT table_to_xmlschema('testxmlschema.test1', false, true, 'foo');
202 table_to_xmlschema
203 ----------------------------------------------------------------------------------------------
204 <xsd:schema +
205 xmlns:xsd="http://www.w3.org/2001/XMLSchema" +
206 targetNamespace="foo" +
207 elementFormDefault="qualified"> +
208 +
209 <xsd:simpleType name="INTEGER"> +
210 <xsd:restriction base="xsd:int"> +
211 <xsd:maxInclusive value="2147483647"/> +
212 <xsd:minInclusive value="-2147483648"/> +
213 </xsd:restriction> +
214 </xsd:simpleType> +
215 +
216 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
217 <xsd:restriction base="xsd:string"> +
218 </xsd:restriction> +
219 </xsd:simpleType> +
220 +
221 <xsd:complexType name="RowType.regression.testxmlschema.test1"> +
222 <xsd:sequence> +
223 <xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element> +
224 <xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element>+
225 </xsd:sequence> +
226 </xsd:complexType> +
227 +
228 <xsd:element name="test1" type="RowType.regression.testxmlschema.test1"/> +
229 +
230 </xsd:schema>
231 (1 row)
233 SELECT table_to_xmlschema('testxmlschema.test1', true, true, '');
234 table_to_xmlschema
235 ------------------------------------------------------------------------------------------------
236 <xsd:schema +
237 xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +
238 +
239 <xsd:simpleType name="INTEGER"> +
240 <xsd:restriction base="xsd:int"> +
241 <xsd:maxInclusive value="2147483647"/> +
242 <xsd:minInclusive value="-2147483648"/> +
243 </xsd:restriction> +
244 </xsd:simpleType> +
245 +
246 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
247 <xsd:restriction base="xsd:string"> +
248 </xsd:restriction> +
249 </xsd:simpleType> +
250 +
251 <xsd:complexType name="RowType.regression.testxmlschema.test1"> +
252 <xsd:sequence> +
253 <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> +
254 <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>+
255 </xsd:sequence> +
256 </xsd:complexType> +
257 +
258 <xsd:element name="test1" type="RowType.regression.testxmlschema.test1"/> +
259 +
260 </xsd:schema>
261 (1 row)
263 SELECT table_to_xmlschema('testxmlschema.test2', false, false, '');
264 table_to_xmlschema
265 ----------------------------------------------------------------------------------------------------------------------------
266 <xsd:schema +
267 xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +
268 +
269 <xsd:simpleType name="INTEGER"> +
270 <xsd:restriction base="xsd:int"> +
271 <xsd:maxInclusive value="2147483647"/> +
272 <xsd:minInclusive value="-2147483648"/> +
273 </xsd:restriction> +
274 </xsd:simpleType> +
275 +
276 <xsd:simpleType name="VARCHAR"> +
277 <xsd:restriction base="xsd:string"> +
278 </xsd:restriction> +
279 </xsd:simpleType> +
280 +
281 <xsd:simpleType name="CHAR"> +
282 <xsd:restriction base="xsd:string"> +
283 </xsd:restriction> +
284 </xsd:simpleType> +
285 +
286 <xsd:simpleType name="NUMERIC"> +
287 </xsd:simpleType> +
288 +
289 <xsd:simpleType name="SMALLINT"> +
290 <xsd:restriction base="xsd:short"> +
291 <xsd:maxInclusive value="32767"/> +
292 <xsd:minInclusive value="-32768"/> +
293 </xsd:restriction> +
294 </xsd:simpleType> +
295 +
296 <xsd:simpleType name="BIGINT"> +
297 <xsd:restriction base="xsd:long"> +
298 <xsd:maxInclusive value="9223372036854775807"/> +
299 <xsd:minInclusive value="-9223372036854775808"/> +
300 </xsd:restriction> +
301 </xsd:simpleType> +
302 +
303 <xsd:simpleType name="REAL"> +
304 <xsd:restriction base="xsd:float"></xsd:restriction> +
305 </xsd:simpleType> +
306 +
307 <xsd:simpleType name="TIME"> +
308 <xsd:restriction base="xsd:time"> +
309 <xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +
310 </xsd:restriction> +
311 </xsd:simpleType> +
312 +
313 <xsd:simpleType name="TIME_WTZ"> +
314 <xsd:restriction base="xsd:time"> +
315 <xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?(\+|-)\p{Nd}{2}:\p{Nd}{2}"/> +
316 </xsd:restriction> +
317 </xsd:simpleType> +
318 +
319 <xsd:simpleType name="TIMESTAMP"> +
320 <xsd:restriction base="xsd:dateTime"> +
321 <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +
322 </xsd:restriction> +
323 </xsd:simpleType> +
324 +
325 <xsd:simpleType name="TIMESTAMP_WTZ"> +
326 <xsd:restriction base="xsd:dateTime"> +
327 <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?(\+|-)\p{Nd}{2}:\p{Nd}{2}"/>+
328 </xsd:restriction> +
329 </xsd:simpleType> +
330 +
331 <xsd:simpleType name="DATE"> +
332 <xsd:restriction base="xsd:date"> +
333 <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/> +
334 </xsd:restriction> +
335 </xsd:simpleType> +
336 +
337 <xsd:complexType mixed="true"> +
338 <xsd:sequence> +
339 <xsd:any name="element" minOccurs="0" maxOccurs="unbounded" processContents="skip"/> +
340 </xsd:sequence> +
341 </xsd:complexType> +
342 +
343 <xsd:simpleType name="Domain.regression.public.testxmldomain"> +
344 <xsd:restriction base="VARCHAR"/> +
345 </xsd:simpleType> +
346 +
347 <xsd:simpleType name="BOOLEAN"> +
348 <xsd:restriction base="xsd:boolean"></xsd:restriction> +
349 </xsd:simpleType> +
350 +
351 <xsd:simpleType name="UDT.regression.pg_catalog.bytea"> +
352 <xsd:restriction base="xsd:base64Binary"> +
353 </xsd:restriction> +
354 </xsd:simpleType> +
355 +
356 <xsd:complexType name="RowType.regression.testxmlschema.test2"> +
357 <xsd:sequence> +
358 <xsd:element name="z" type="INTEGER" minOccurs="0"></xsd:element> +
359 <xsd:element name="y" type="VARCHAR" minOccurs="0"></xsd:element> +
360 <xsd:element name="x" type="CHAR" minOccurs="0"></xsd:element> +
361 <xsd:element name="w" type="NUMERIC" minOccurs="0"></xsd:element> +
362 <xsd:element name="v" type="SMALLINT" minOccurs="0"></xsd:element> +
363 <xsd:element name="u" type="BIGINT" minOccurs="0"></xsd:element> +
364 <xsd:element name="t" type="REAL" minOccurs="0"></xsd:element> +
365 <xsd:element name="s" type="TIME" minOccurs="0"></xsd:element> +
366 <xsd:element name="stz" type="TIME_WTZ" minOccurs="0"></xsd:element> +
367 <xsd:element name="r" type="TIMESTAMP" minOccurs="0"></xsd:element> +
368 <xsd:element name="rtz" type="TIMESTAMP_WTZ" minOccurs="0"></xsd:element> +
369 <xsd:element name="q" type="DATE" minOccurs="0"></xsd:element> +
370 <xsd:element name="p" type="XML" minOccurs="0"></xsd:element> +
371 <xsd:element name="o" type="Domain.regression.public.testxmldomain" minOccurs="0"></xsd:element> +
372 <xsd:element name="n" type="BOOLEAN" minOccurs="0"></xsd:element> +
373 <xsd:element name="m" type="UDT.regression.pg_catalog.bytea" minOccurs="0"></xsd:element> +
374 </xsd:sequence> +
375 </xsd:complexType> +
376 +
377 <xsd:complexType name="TableType.regression.testxmlschema.test2"> +
378 <xsd:sequence> +
379 <xsd:element name="row" type="RowType.regression.testxmlschema.test2" minOccurs="0" maxOccurs="unbounded"/> +
380 </xsd:sequence> +
381 </xsd:complexType> +
382 +
383 <xsd:element name="test2" type="TableType.regression.testxmlschema.test2"/> +
384 +
385 </xsd:schema>
386 (1 row)
388 SELECT table_to_xml_and_xmlschema('testxmlschema.test1', false, false, '');
389 table_to_xml_and_xmlschema
390 -----------------------------------------------------------------------------------------------------------------
391 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="#"> +
392 +
393 <xsd:schema +
394 xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +
395 +
396 <xsd:simpleType name="INTEGER"> +
397 <xsd:restriction base="xsd:int"> +
398 <xsd:maxInclusive value="2147483647"/> +
399 <xsd:minInclusive value="-2147483648"/> +
400 </xsd:restriction> +
401 </xsd:simpleType> +
402 +
403 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
404 <xsd:restriction base="xsd:string"> +
405 </xsd:restriction> +
406 </xsd:simpleType> +
407 +
408 <xsd:complexType name="RowType.regression.testxmlschema.test1"> +
409 <xsd:sequence> +
410 <xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element> +
411 <xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element> +
412 </xsd:sequence> +
413 </xsd:complexType> +
414 +
415 <xsd:complexType name="TableType.regression.testxmlschema.test1"> +
416 <xsd:sequence> +
417 <xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>+
418 </xsd:sequence> +
419 </xsd:complexType> +
420 +
421 <xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/> +
422 +
423 </xsd:schema> +
424 +
425 <row> +
426 <a>1</a> +
427 <b>one</b> +
428 </row> +
429 +
430 <row> +
431 <a>2</a> +
432 <b>two</b> +
433 </row> +
434 +
435 <row> +
436 <a>-1</a> +
437 </row> +
438 +
439 </test1> +
441 (1 row)
443 SELECT table_to_xml_and_xmlschema('testxmlschema.test1', true, false, '');
444 table_to_xml_and_xmlschema
445 -----------------------------------------------------------------------------------------------------------------
446 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="#"> +
447 +
448 <xsd:schema +
449 xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +
450 +
451 <xsd:simpleType name="INTEGER"> +
452 <xsd:restriction base="xsd:int"> +
453 <xsd:maxInclusive value="2147483647"/> +
454 <xsd:minInclusive value="-2147483648"/> +
455 </xsd:restriction> +
456 </xsd:simpleType> +
457 +
458 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
459 <xsd:restriction base="xsd:string"> +
460 </xsd:restriction> +
461 </xsd:simpleType> +
462 +
463 <xsd:complexType name="RowType.regression.testxmlschema.test1"> +
464 <xsd:sequence> +
465 <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> +
466 <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element> +
467 </xsd:sequence> +
468 </xsd:complexType> +
469 +
470 <xsd:complexType name="TableType.regression.testxmlschema.test1"> +
471 <xsd:sequence> +
472 <xsd:element name="row" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/>+
473 </xsd:sequence> +
474 </xsd:complexType> +
475 +
476 <xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/> +
477 +
478 </xsd:schema> +
479 +
480 <row> +
481 <a>1</a> +
482 <b>one</b> +
483 </row> +
484 +
485 <row> +
486 <a>2</a> +
487 <b>two</b> +
488 </row> +
489 +
490 <row> +
491 <a>-1</a> +
492 <b xsi:nil="true"/> +
493 </row> +
494 +
495 </test1> +
497 (1 row)
499 SELECT table_to_xml_and_xmlschema('testxmlschema.test1', false, true, '');
500 table_to_xml_and_xmlschema
501 ----------------------------------------------------------------------------------------------
502 <xsd:schema +
503 xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +
504 +
505 <xsd:simpleType name="INTEGER"> +
506 <xsd:restriction base="xsd:int"> +
507 <xsd:maxInclusive value="2147483647"/> +
508 <xsd:minInclusive value="-2147483648"/> +
509 </xsd:restriction> +
510 </xsd:simpleType> +
511 +
512 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
513 <xsd:restriction base="xsd:string"> +
514 </xsd:restriction> +
515 </xsd:simpleType> +
516 +
517 <xsd:complexType name="RowType.regression.testxmlschema.test1"> +
518 <xsd:sequence> +
519 <xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element> +
520 <xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element>+
521 </xsd:sequence> +
522 </xsd:complexType> +
523 +
524 <xsd:element name="test1" type="RowType.regression.testxmlschema.test1"/> +
525 +
526 </xsd:schema> +
527 +
528 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +
529 <a>1</a> +
530 <b>one</b> +
531 </test1> +
532 +
533 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +
534 <a>2</a> +
535 <b>two</b> +
536 </test1> +
537 +
538 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +
539 <a>-1</a> +
540 </test1> +
541 +
543 (1 row)
545 SELECT table_to_xml_and_xmlschema('testxmlschema.test1', true, true, 'foo');
546 table_to_xml_and_xmlschema
547 ------------------------------------------------------------------------------------------------
548 <xsd:schema +
549 xmlns:xsd="http://www.w3.org/2001/XMLSchema" +
550 targetNamespace="foo" +
551 elementFormDefault="qualified"> +
552 +
553 <xsd:simpleType name="INTEGER"> +
554 <xsd:restriction base="xsd:int"> +
555 <xsd:maxInclusive value="2147483647"/> +
556 <xsd:minInclusive value="-2147483648"/> +
557 </xsd:restriction> +
558 </xsd:simpleType> +
559 +
560 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
561 <xsd:restriction base="xsd:string"> +
562 </xsd:restriction> +
563 </xsd:simpleType> +
564 +
565 <xsd:complexType name="RowType.regression.testxmlschema.test1"> +
566 <xsd:sequence> +
567 <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> +
568 <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>+
569 </xsd:sequence> +
570 </xsd:complexType> +
571 +
572 <xsd:element name="test1" type="RowType.regression.testxmlschema.test1"/> +
573 +
574 </xsd:schema> +
575 +
576 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo"> +
577 <a>1</a> +
578 <b>one</b> +
579 </test1> +
580 +
581 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo"> +
582 <a>2</a> +
583 <b>two</b> +
584 </test1> +
585 +
586 <test1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo"> +
587 <a>-1</a> +
588 <b xsi:nil="true"/> +
589 </test1> +
590 +
592 (1 row)
594 SELECT query_to_xml('SELECT * FROM testxmlschema.test1', false, false, '');
595 query_to_xml
596 ---------------------------------------------------------------
597 <table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
598 +
599 <row> +
600 <a>1</a> +
601 <b>one</b> +
602 </row> +
603 +
604 <row> +
605 <a>2</a> +
606 <b>two</b> +
607 </row> +
608 +
609 <row> +
610 <a>-1</a> +
611 </row> +
612 +
613 </table> +
615 (1 row)
617 SELECT query_to_xmlschema('SELECT * FROM testxmlschema.test1', false, false, '');
618 query_to_xmlschema
619 ----------------------------------------------------------------------------------------------
620 <xsd:schema +
621 xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +
622 +
623 <xsd:simpleType name="INTEGER"> +
624 <xsd:restriction base="xsd:int"> +
625 <xsd:maxInclusive value="2147483647"/> +
626 <xsd:minInclusive value="-2147483648"/> +
627 </xsd:restriction> +
628 </xsd:simpleType> +
629 +
630 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
631 <xsd:restriction base="xsd:string"> +
632 </xsd:restriction> +
633 </xsd:simpleType> +
634 +
635 <xsd:complexType name="RowType"> +
636 <xsd:sequence> +
637 <xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element> +
638 <xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element>+
639 </xsd:sequence> +
640 </xsd:complexType> +
641 +
642 <xsd:complexType name="TableType"> +
643 <xsd:sequence> +
644 <xsd:element name="row" type="RowType" minOccurs="0" maxOccurs="unbounded"/> +
645 </xsd:sequence> +
646 </xsd:complexType> +
647 +
648 <xsd:element name="table" type="TableType"/> +
649 +
650 </xsd:schema>
651 (1 row)
653 SELECT query_to_xml_and_xmlschema('SELECT * FROM testxmlschema.test1', true, true, '');
654 query_to_xml_and_xmlschema
655 ------------------------------------------------------------------------------------------------
656 <xsd:schema +
657 xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +
658 +
659 <xsd:simpleType name="INTEGER"> +
660 <xsd:restriction base="xsd:int"> +
661 <xsd:maxInclusive value="2147483647"/> +
662 <xsd:minInclusive value="-2147483648"/> +
663 </xsd:restriction> +
664 </xsd:simpleType> +
665 +
666 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
667 <xsd:restriction base="xsd:string"> +
668 </xsd:restriction> +
669 </xsd:simpleType> +
670 +
671 <xsd:complexType name="RowType"> +
672 <xsd:sequence> +
673 <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> +
674 <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>+
675 </xsd:sequence> +
676 </xsd:complexType> +
677 +
678 <xsd:element name="row" type="RowType"/> +
679 +
680 </xsd:schema> +
681 +
682 <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +
683 <a>1</a> +
684 <b>one</b> +
685 </row> +
686 +
687 <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +
688 <a>2</a> +
689 <b>two</b> +
690 </row> +
691 +
692 <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +
693 <a>-1</a> +
694 <b xsi:nil="true"/> +
695 </row> +
696 +
698 (1 row)
700 DECLARE xc CURSOR WITH HOLD FOR SELECT * FROM testxmlschema.test1 ORDER BY 1, 2;
701 SELECT cursor_to_xml('xc'::refcursor, 5, false, true, '');
702 cursor_to_xml
703 -------------------------------------------------------------
704 <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
705 <a>-1</a> +
706 </row> +
707 +
708 <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
709 <a>1</a> +
710 <b>one</b> +
711 </row> +
712 +
713 <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
714 <a>2</a> +
715 <b>two</b> +
716 </row> +
717 +
719 (1 row)
721 SELECT cursor_to_xmlschema('xc'::refcursor, false, true, '');
722 cursor_to_xmlschema
723 ----------------------------------------------------------------------------------------------
724 <xsd:schema +
725 xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +
726 +
727 <xsd:simpleType name="INTEGER"> +
728 <xsd:restriction base="xsd:int"> +
729 <xsd:maxInclusive value="2147483647"/> +
730 <xsd:minInclusive value="-2147483648"/> +
731 </xsd:restriction> +
732 </xsd:simpleType> +
733 +
734 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
735 <xsd:restriction base="xsd:string"> +
736 </xsd:restriction> +
737 </xsd:simpleType> +
738 +
739 <xsd:complexType name="RowType"> +
740 <xsd:sequence> +
741 <xsd:element name="a" type="INTEGER" minOccurs="0"></xsd:element> +
742 <xsd:element name="b" type="UDT.regression.pg_catalog.text" minOccurs="0"></xsd:element>+
743 </xsd:sequence> +
744 </xsd:complexType> +
745 +
746 <xsd:element name="row" type="RowType"/> +
747 +
748 </xsd:schema>
749 (1 row)
751 MOVE BACKWARD ALL IN xc;
752 SELECT cursor_to_xml('xc'::refcursor, 5, true, false, '');
753 cursor_to_xml
754 ---------------------------------------------------------------
755 <table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
756 +
757 <row> +
758 <a>-1</a> +
759 <b xsi:nil="true"/> +
760 </row> +
761 +
762 <row> +
763 <a>1</a> +
764 <b>one</b> +
765 </row> +
766 +
767 <row> +
768 <a>2</a> +
769 <b>two</b> +
770 </row> +
771 +
772 </table> +
774 (1 row)
776 SELECT cursor_to_xmlschema('xc'::refcursor, true, false, '');
777 cursor_to_xmlschema
778 ------------------------------------------------------------------------------------------------
779 <xsd:schema +
780 xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +
781 +
782 <xsd:simpleType name="INTEGER"> +
783 <xsd:restriction base="xsd:int"> +
784 <xsd:maxInclusive value="2147483647"/> +
785 <xsd:minInclusive value="-2147483648"/> +
786 </xsd:restriction> +
787 </xsd:simpleType> +
788 +
789 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
790 <xsd:restriction base="xsd:string"> +
791 </xsd:restriction> +
792 </xsd:simpleType> +
793 +
794 <xsd:complexType name="RowType"> +
795 <xsd:sequence> +
796 <xsd:element name="a" type="INTEGER" nillable="true"></xsd:element> +
797 <xsd:element name="b" type="UDT.regression.pg_catalog.text" nillable="true"></xsd:element>+
798 </xsd:sequence> +
799 </xsd:complexType> +
800 +
801 <xsd:complexType name="TableType"> +
802 <xsd:sequence> +
803 <xsd:element name="row" type="RowType" minOccurs="0" maxOccurs="unbounded"/> +
804 </xsd:sequence> +
805 </xsd:complexType> +
806 +
807 <xsd:element name="table" type="TableType"/> +
808 +
809 </xsd:schema>
810 (1 row)
812 SELECT schema_to_xml('testxmlschema', false, true, '');
813 schema_to_xml
814 -----------------------------------------------------------------------
815 <testxmlschema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
816 +
817 <test1> +
818 <a>1</a> +
819 <b>one</b> +
820 </test1> +
821 +
822 <test1> +
823 <a>2</a> +
824 <b>two</b> +
825 </test1> +
826 +
827 <test1> +
828 <a>-1</a> +
829 </test1> +
830 +
831 +
832 <test2> +
833 <z>55</z> +
834 <y>abc</y> +
835 <x>def </x> +
836 <w>98.60</w> +
837 <v>2</v> +
838 <u>999</u> +
839 <t>0</t> +
840 <s>21:07:00</s> +
841 <stz>21:11:00+05</stz> +
842 <r>2009-06-08T21:07:30</r> +
843 <rtz>2009-06-08T21:07:30-07:00</rtz> +
844 <q>2009-06-08</q> +
845 <o>ABC</o> +
846 <n>true</n> +
847 <m>WFla</m> +
848 </test2> +
849 +
850 +
851 </testxmlschema> +
853 (1 row)
855 SELECT schema_to_xml('testxmlschema', true, false, '');
856 schema_to_xml
857 -----------------------------------------------------------------------
858 <testxmlschema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
859 +
860 <test1> +
861 +
862 <row> +
863 <a>1</a> +
864 <b>one</b> +
865 </row> +
866 +
867 <row> +
868 <a>2</a> +
869 <b>two</b> +
870 </row> +
871 +
872 <row> +
873 <a>-1</a> +
874 <b xsi:nil="true"/> +
875 </row> +
876 +
877 </test1> +
878 +
879 <test2> +
880 +
881 <row> +
882 <z>55</z> +
883 <y>abc</y> +
884 <x>def </x> +
885 <w>98.60</w> +
886 <v>2</v> +
887 <u>999</u> +
888 <t>0</t> +
889 <s>21:07:00</s> +
890 <stz>21:11:00+05</stz> +
891 <r>2009-06-08T21:07:30</r> +
892 <rtz>2009-06-08T21:07:30-07:00</rtz> +
893 <q>2009-06-08</q> +
894 <p xsi:nil="true"/> +
895 <o>ABC</o> +
896 <n>true</n> +
897 <m>WFla</m> +
898 </row> +
899 +
900 </test2> +
901 +
902 </testxmlschema> +
904 (1 row)
906 SELECT schema_to_xmlschema('testxmlschema', false, true, '');
907 schema_to_xmlschema
908 ----------------------------------------------------------------------------------------------------------------------------
909 <xsd:schema +
910 xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +
911 +
912 <xsd:simpleType name="INTEGER"> +
913 <xsd:restriction base="xsd:int"> +
914 <xsd:maxInclusive value="2147483647"/> +
915 <xsd:minInclusive value="-2147483648"/> +
916 </xsd:restriction> +
917 </xsd:simpleType> +
918 +
919 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
920 <xsd:restriction base="xsd:string"> +
921 </xsd:restriction> +
922 </xsd:simpleType> +
923 +
924 <xsd:simpleType name="VARCHAR"> +
925 <xsd:restriction base="xsd:string"> +
926 </xsd:restriction> +
927 </xsd:simpleType> +
928 +
929 <xsd:simpleType name="CHAR"> +
930 <xsd:restriction base="xsd:string"> +
931 </xsd:restriction> +
932 </xsd:simpleType> +
933 +
934 <xsd:simpleType name="NUMERIC"> +
935 </xsd:simpleType> +
936 +
937 <xsd:simpleType name="SMALLINT"> +
938 <xsd:restriction base="xsd:short"> +
939 <xsd:maxInclusive value="32767"/> +
940 <xsd:minInclusive value="-32768"/> +
941 </xsd:restriction> +
942 </xsd:simpleType> +
943 +
944 <xsd:simpleType name="BIGINT"> +
945 <xsd:restriction base="xsd:long"> +
946 <xsd:maxInclusive value="9223372036854775807"/> +
947 <xsd:minInclusive value="-9223372036854775808"/> +
948 </xsd:restriction> +
949 </xsd:simpleType> +
950 +
951 <xsd:simpleType name="REAL"> +
952 <xsd:restriction base="xsd:float"></xsd:restriction> +
953 </xsd:simpleType> +
954 +
955 <xsd:simpleType name="TIME"> +
956 <xsd:restriction base="xsd:time"> +
957 <xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +
958 </xsd:restriction> +
959 </xsd:simpleType> +
960 +
961 <xsd:simpleType name="TIME_WTZ"> +
962 <xsd:restriction base="xsd:time"> +
963 <xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?(\+|-)\p{Nd}{2}:\p{Nd}{2}"/> +
964 </xsd:restriction> +
965 </xsd:simpleType> +
966 +
967 <xsd:simpleType name="TIMESTAMP"> +
968 <xsd:restriction base="xsd:dateTime"> +
969 <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +
970 </xsd:restriction> +
971 </xsd:simpleType> +
972 +
973 <xsd:simpleType name="TIMESTAMP_WTZ"> +
974 <xsd:restriction base="xsd:dateTime"> +
975 <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?(\+|-)\p{Nd}{2}:\p{Nd}{2}"/>+
976 </xsd:restriction> +
977 </xsd:simpleType> +
978 +
979 <xsd:simpleType name="DATE"> +
980 <xsd:restriction base="xsd:date"> +
981 <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/> +
982 </xsd:restriction> +
983 </xsd:simpleType> +
984 +
985 <xsd:complexType mixed="true"> +
986 <xsd:sequence> +
987 <xsd:any name="element" minOccurs="0" maxOccurs="unbounded" processContents="skip"/> +
988 </xsd:sequence> +
989 </xsd:complexType> +
990 +
991 <xsd:simpleType name="Domain.regression.public.testxmldomain"> +
992 <xsd:restriction base="VARCHAR"/> +
993 </xsd:simpleType> +
994 +
995 <xsd:simpleType name="BOOLEAN"> +
996 <xsd:restriction base="xsd:boolean"></xsd:restriction> +
997 </xsd:simpleType> +
998 +
999 <xsd:simpleType name="UDT.regression.pg_catalog.bytea"> +
1000 <xsd:restriction base="xsd:base64Binary"> +
1001 </xsd:restriction> +
1002 </xsd:simpleType> +
1003 +
1004 <xsd:complexType name="SchemaType.regression.testxmlschema"> +
1005 <xsd:sequence> +
1006 <xsd:element name="test1" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/> +
1007 <xsd:element name="test2" type="RowType.regression.testxmlschema.test2" minOccurs="0" maxOccurs="unbounded"/> +
1008 </xsd:sequence> +
1009 </xsd:complexType> +
1010 +
1011 <xsd:element name="testxmlschema" type="SchemaType.regression.testxmlschema"/> +
1012 +
1013 </xsd:schema>
1014 (1 row)
1016 SELECT schema_to_xmlschema('testxmlschema', true, false, '');
1017 schema_to_xmlschema
1018 ----------------------------------------------------------------------------------------------------------------------------
1019 <xsd:schema +
1020 xmlns:xsd="http://www.w3.org/2001/XMLSchema"> +
1021 +
1022 <xsd:simpleType name="INTEGER"> +
1023 <xsd:restriction base="xsd:int"> +
1024 <xsd:maxInclusive value="2147483647"/> +
1025 <xsd:minInclusive value="-2147483648"/> +
1026 </xsd:restriction> +
1027 </xsd:simpleType> +
1028 +
1029 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
1030 <xsd:restriction base="xsd:string"> +
1031 </xsd:restriction> +
1032 </xsd:simpleType> +
1033 +
1034 <xsd:simpleType name="VARCHAR"> +
1035 <xsd:restriction base="xsd:string"> +
1036 </xsd:restriction> +
1037 </xsd:simpleType> +
1038 +
1039 <xsd:simpleType name="CHAR"> +
1040 <xsd:restriction base="xsd:string"> +
1041 </xsd:restriction> +
1042 </xsd:simpleType> +
1043 +
1044 <xsd:simpleType name="NUMERIC"> +
1045 </xsd:simpleType> +
1046 +
1047 <xsd:simpleType name="SMALLINT"> +
1048 <xsd:restriction base="xsd:short"> +
1049 <xsd:maxInclusive value="32767"/> +
1050 <xsd:minInclusive value="-32768"/> +
1051 </xsd:restriction> +
1052 </xsd:simpleType> +
1053 +
1054 <xsd:simpleType name="BIGINT"> +
1055 <xsd:restriction base="xsd:long"> +
1056 <xsd:maxInclusive value="9223372036854775807"/> +
1057 <xsd:minInclusive value="-9223372036854775808"/> +
1058 </xsd:restriction> +
1059 </xsd:simpleType> +
1060 +
1061 <xsd:simpleType name="REAL"> +
1062 <xsd:restriction base="xsd:float"></xsd:restriction> +
1063 </xsd:simpleType> +
1064 +
1065 <xsd:simpleType name="TIME"> +
1066 <xsd:restriction base="xsd:time"> +
1067 <xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +
1068 </xsd:restriction> +
1069 </xsd:simpleType> +
1070 +
1071 <xsd:simpleType name="TIME_WTZ"> +
1072 <xsd:restriction base="xsd:time"> +
1073 <xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?(\+|-)\p{Nd}{2}:\p{Nd}{2}"/> +
1074 </xsd:restriction> +
1075 </xsd:simpleType> +
1076 +
1077 <xsd:simpleType name="TIMESTAMP"> +
1078 <xsd:restriction base="xsd:dateTime"> +
1079 <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +
1080 </xsd:restriction> +
1081 </xsd:simpleType> +
1082 +
1083 <xsd:simpleType name="TIMESTAMP_WTZ"> +
1084 <xsd:restriction base="xsd:dateTime"> +
1085 <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?(\+|-)\p{Nd}{2}:\p{Nd}{2}"/>+
1086 </xsd:restriction> +
1087 </xsd:simpleType> +
1088 +
1089 <xsd:simpleType name="DATE"> +
1090 <xsd:restriction base="xsd:date"> +
1091 <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/> +
1092 </xsd:restriction> +
1093 </xsd:simpleType> +
1094 +
1095 <xsd:complexType mixed="true"> +
1096 <xsd:sequence> +
1097 <xsd:any name="element" minOccurs="0" maxOccurs="unbounded" processContents="skip"/> +
1098 </xsd:sequence> +
1099 </xsd:complexType> +
1100 +
1101 <xsd:simpleType name="Domain.regression.public.testxmldomain"> +
1102 <xsd:restriction base="VARCHAR"/> +
1103 </xsd:simpleType> +
1104 +
1105 <xsd:simpleType name="BOOLEAN"> +
1106 <xsd:restriction base="xsd:boolean"></xsd:restriction> +
1107 </xsd:simpleType> +
1108 +
1109 <xsd:simpleType name="UDT.regression.pg_catalog.bytea"> +
1110 <xsd:restriction base="xsd:base64Binary"> +
1111 </xsd:restriction> +
1112 </xsd:simpleType> +
1113 +
1114 <xsd:complexType name="SchemaType.regression.testxmlschema"> +
1115 <xsd:all> +
1116 <xsd:element name="test1" type="TableType.regression.testxmlschema.test1"/> +
1117 <xsd:element name="test2" type="TableType.regression.testxmlschema.test2"/> +
1118 </xsd:all> +
1119 </xsd:complexType> +
1120 +
1121 <xsd:element name="testxmlschema" type="SchemaType.regression.testxmlschema"/> +
1122 +
1123 </xsd:schema>
1124 (1 row)
1126 SELECT schema_to_xml_and_xmlschema('testxmlschema', true, true, 'foo');
1127 schema_to_xml_and_xmlschema
1128 ----------------------------------------------------------------------------------------------------------------------------
1129 <testxmlschema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="foo" xsi:schemaLocation="foo #"> +
1130 +
1131 <xsd:schema +
1132 xmlns:xsd="http://www.w3.org/2001/XMLSchema" +
1133 targetNamespace="foo" +
1134 elementFormDefault="qualified"> +
1135 +
1136 <xsd:simpleType name="INTEGER"> +
1137 <xsd:restriction base="xsd:int"> +
1138 <xsd:maxInclusive value="2147483647"/> +
1139 <xsd:minInclusive value="-2147483648"/> +
1140 </xsd:restriction> +
1141 </xsd:simpleType> +
1142 +
1143 <xsd:simpleType name="UDT.regression.pg_catalog.text"> +
1144 <xsd:restriction base="xsd:string"> +
1145 </xsd:restriction> +
1146 </xsd:simpleType> +
1147 +
1148 <xsd:simpleType name="VARCHAR"> +
1149 <xsd:restriction base="xsd:string"> +
1150 </xsd:restriction> +
1151 </xsd:simpleType> +
1152 +
1153 <xsd:simpleType name="CHAR"> +
1154 <xsd:restriction base="xsd:string"> +
1155 </xsd:restriction> +
1156 </xsd:simpleType> +
1157 +
1158 <xsd:simpleType name="NUMERIC"> +
1159 </xsd:simpleType> +
1160 +
1161 <xsd:simpleType name="SMALLINT"> +
1162 <xsd:restriction base="xsd:short"> +
1163 <xsd:maxInclusive value="32767"/> +
1164 <xsd:minInclusive value="-32768"/> +
1165 </xsd:restriction> +
1166 </xsd:simpleType> +
1167 +
1168 <xsd:simpleType name="BIGINT"> +
1169 <xsd:restriction base="xsd:long"> +
1170 <xsd:maxInclusive value="9223372036854775807"/> +
1171 <xsd:minInclusive value="-9223372036854775808"/> +
1172 </xsd:restriction> +
1173 </xsd:simpleType> +
1174 +
1175 <xsd:simpleType name="REAL"> +
1176 <xsd:restriction base="xsd:float"></xsd:restriction> +
1177 </xsd:simpleType> +
1178 +
1179 <xsd:simpleType name="TIME"> +
1180 <xsd:restriction base="xsd:time"> +
1181 <xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +
1182 </xsd:restriction> +
1183 </xsd:simpleType> +
1184 +
1185 <xsd:simpleType name="TIME_WTZ"> +
1186 <xsd:restriction base="xsd:time"> +
1187 <xsd:pattern value="\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?(\+|-)\p{Nd}{2}:\p{Nd}{2}"/> +
1188 </xsd:restriction> +
1189 </xsd:simpleType> +
1190 +
1191 <xsd:simpleType name="TIMESTAMP"> +
1192 <xsd:restriction base="xsd:dateTime"> +
1193 <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?"/> +
1194 </xsd:restriction> +
1195 </xsd:simpleType> +
1196 +
1197 <xsd:simpleType name="TIMESTAMP_WTZ"> +
1198 <xsd:restriction base="xsd:dateTime"> +
1199 <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}T\p{Nd}{2}:\p{Nd}{2}:\p{Nd}{2}(.\p{Nd}+)?(\+|-)\p{Nd}{2}:\p{Nd}{2}"/>+
1200 </xsd:restriction> +
1201 </xsd:simpleType> +
1202 +
1203 <xsd:simpleType name="DATE"> +
1204 <xsd:restriction base="xsd:date"> +
1205 <xsd:pattern value="\p{Nd}{4}-\p{Nd}{2}-\p{Nd}{2}"/> +
1206 </xsd:restriction> +
1207 </xsd:simpleType> +
1208 +
1209 <xsd:complexType mixed="true"> +
1210 <xsd:sequence> +
1211 <xsd:any name="element" minOccurs="0" maxOccurs="unbounded" processContents="skip"/> +
1212 </xsd:sequence> +
1213 </xsd:complexType> +
1214 +
1215 <xsd:simpleType name="Domain.regression.public.testxmldomain"> +
1216 <xsd:restriction base="VARCHAR"/> +
1217 </xsd:simpleType> +
1218 +
1219 <xsd:simpleType name="BOOLEAN"> +
1220 <xsd:restriction base="xsd:boolean"></xsd:restriction> +
1221 </xsd:simpleType> +
1222 +
1223 <xsd:simpleType name="UDT.regression.pg_catalog.bytea"> +
1224 <xsd:restriction base="xsd:base64Binary"> +
1225 </xsd:restriction> +
1226 </xsd:simpleType> +
1227 +
1228 <xsd:complexType name="SchemaType.regression.testxmlschema"> +
1229 <xsd:sequence> +
1230 <xsd:element name="test1" type="RowType.regression.testxmlschema.test1" minOccurs="0" maxOccurs="unbounded"/> +
1231 <xsd:element name="test2" type="RowType.regression.testxmlschema.test2" minOccurs="0" maxOccurs="unbounded"/> +
1232 </xsd:sequence> +
1233 </xsd:complexType> +
1234 +
1235 <xsd:element name="testxmlschema" type="SchemaType.regression.testxmlschema"/> +
1236 +
1237 </xsd:schema> +
1238 +
1239 <test1> +
1240 <a>1</a> +
1241 <b>one</b> +
1242 </test1> +
1243 +
1244 <test1> +
1245 <a>2</a> +
1246 <b>two</b> +
1247 </test1> +
1248 +
1249 <test1> +
1250 <a>-1</a> +
1251 <b xsi:nil="true"/> +
1252 </test1> +
1253 +
1254 +
1255 <test2> +
1256 <z>55</z> +
1257 <y>abc</y> +
1258 <x>def </x> +
1259 <w>98.60</w> +
1260 <v>2</v> +
1261 <u>999</u> +
1262 <t>0</t> +
1263 <s>21:07:00</s> +
1264 <stz>21:11:00+05</stz> +
1265 <r>2009-06-08T21:07:30</r> +
1266 <rtz>2009-06-08T21:07:30-07:00</rtz> +
1267 <q>2009-06-08</q> +
1268 <p xsi:nil="true"/> +
1269 <o>ABC</o> +
1270 <n>true</n> +
1271 <m>WFla</m> +
1272 </test2> +
1273 +
1274 +
1275 </testxmlschema> +
1277 (1 row)
1279 -- test that domains are transformed like their base types
1280 CREATE DOMAIN testboolxmldomain AS bool;
1281 CREATE DOMAIN testdatexmldomain AS date;
1282 CREATE TABLE testxmlschema.test3
1283 AS SELECT true c1,
1284 true::testboolxmldomain c2,
1285 '2013-02-21'::date c3,
1286 '2013-02-21'::testdatexmldomain c4;
1287 SELECT xmlforest(c1, c2, c3, c4) FROM testxmlschema.test3;
1288 xmlforest
1289 ------------------------------------------------------------------
1290 <c1>true</c1><c2>true</c2><c3>2013-02-21</c3><c4>2013-02-21</c4>
1291 (1 row)
1293 SELECT table_to_xml('testxmlschema.test3', true, true, '');
1294 table_to_xml
1295 ---------------------------------------------------------------
1296 <test3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
1297 <c1>true</c1> +
1298 <c2>true</c2> +
1299 <c3>2013-02-21</c3> +
1300 <c4>2013-02-21</c4> +
1301 </test3> +
1302 +
1304 (1 row)