1
2
3
4
5
6
7
8
9
10
11
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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
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
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
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
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.15. XML Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-uuid.html" title="9.14. UUID Functions" /><link rel="next" href="functions-json.html" title="9.16. JSON Functions and Operators" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.15. XML Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-uuid.html" title="9.14. UUID Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-json.html" title="9.16. JSON Functions and Operators">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-XML"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.15. XML Functions</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="functions-xml.html#FUNCTIONS-PRODUCING-XML">9.15.1. Producing XML Content</a></span></dt><dt><span class="sect2"><a href="functions-xml.html#FUNCTIONS-XML-PREDICATES">9.15.2. XML Predicates</a></span></dt><dt><span class="sect2"><a href="functions-xml.html#FUNCTIONS-XML-PROCESSING">9.15.3. Processing XML</a></span></dt><dt><span class="sect2"><a href="functions-xml.html#FUNCTIONS-XML-MAPPING">9.15.4. Mapping Tables to XML</a></span></dt></dl></div><a id="id-1.5.8.21.2" class="indexterm"></a><p>
The functions and function-like expressions described in this
section operate on values of type <code class="type">xml</code>. See <a class="xref" href="datatype-xml.html" title="8.13. XML Type">Section 8.13</a> for information about the <code class="type">xml</code>
type. The function-like expressions <code class="function">xmlparse</code>
and <code class="function">xmlserialize</code> for converting to and from
type <code class="type">xml</code> are documented there, not in this section.
</p><p>
Use of most of these functions
requires <span class="productname">PostgreSQL</span> to have been built
with <code class="command">configure --with-libxml</code>.
</p><div class="sect2" id="FUNCTIONS-PRODUCING-XML"><div class="titlepage"><div><div><h3 class="title">9.15.1. Producing XML Content</h3></div></div></div><p>
A set of functions and function-like expressions is available for
producing XML content from SQL data. As such, they are
particularly suitable for formatting query results into XML
documents for processing in client applications.
</p><div class="sect3" id="id-1.5.8.21.5.3"><div class="titlepage"><div><div><h4 class="title">9.15.1.1. <code class="literal">xmlcomment</code></h4></div></div></div><a id="id-1.5.8.21.5.3.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlcomment</code> ( <code class="type">text</code> ) → <code class="returnvalue">xml</code>
</pre><p>
The function <code class="function">xmlcomment</code> creates an XML value
containing an XML comment with the specified text as content.
The text cannot contain <span class="quote">“<span class="quote"><code class="literal">--</code></span>”</span> or end with a
<span class="quote">“<span class="quote"><code class="literal">-</code></span>”</span>, otherwise the resulting construct
would not be a valid XML comment.
If the argument is null, the result is null.
</p><p>
Example:
</p><pre class="screen">
SELECT xmlcomment('hello');
xmlcomment
--------------
<!--hello-->
</pre><p>
</p></div><div class="sect3" id="id-1.5.8.21.5.4"><div class="titlepage"><div><div><h4 class="title">9.15.1.2. <code class="literal">xmlconcat</code></h4></div></div></div><a id="id-1.5.8.21.5.4.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlconcat</code> ( <code class="type">xml</code> [<span class="optional">, ...</span>] ) → <code class="returnvalue">xml</code>
</pre><p>
The function <code class="function">xmlconcat</code> concatenates a list
of individual XML values to create a single value containing an
XML content fragment. Null values are omitted; the result is
only null if there are no nonnull arguments.
</p><p>
Example:
</p><pre class="screen">
SELECT xmlconcat('<abc/>', '<bar>foo</bar>');
xmlconcat
----------------------
<abc/><bar>foo</bar>
</pre><p>
</p><p>
XML declarations, if present, are combined as follows. If all
argument values have the same XML version declaration, that
version is used in the result, else no version is used. If all
argument values have the standalone declaration value
<span class="quote">“<span class="quote">yes</span>”</span>, then that value is used in the result. If
all argument values have a standalone declaration value and at
least one is <span class="quote">“<span class="quote">no</span>”</span>, then that is used in the result.
Else the result will have no standalone declaration. If the
result is determined to require a standalone declaration but no
version declaration, a version declaration with version 1.0 will
be used because XML requires an XML declaration to contain a
version declaration. Encoding declarations are ignored and
removed in all cases.
</p><p>
Example:
</p><pre class="screen">
SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');
xmlconcat
-----------------------------------
<?xml version="1.1"?><foo/><bar/>
</pre><p>
</p></div><div class="sect3" id="id-1.5.8.21.5.5"><div class="titlepage"><div><div><h4 class="title">9.15.1.3. <code class="literal">xmlelement</code></h4></div></div></div><a id="id-1.5.8.21.5.5.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlelement</code> ( <code class="literal">NAME</code> <em class="replaceable"><code>name</code></em> [<span class="optional">, <code class="literal">XMLATTRIBUTES</code> ( <em class="replaceable"><code>attvalue</code></em> [<span class="optional"> <code class="literal">AS</code> <em class="replaceable"><code>attname</code></em> </span>] [<span class="optional">, ...</span>] ) </span>] [<span class="optional">, <em class="replaceable"><code>content</code></em> [<span class="optional">, ...</span>]</span>] ) → <code class="returnvalue">xml</code>
</pre><p>
The <code class="function">xmlelement</code> expression produces an XML
element with the given name, attributes, and content.
The <em class="replaceable"><code>name</code></em>
and <em class="replaceable"><code>attname</code></em> items shown in the syntax are
simple identifiers, not values. The <em class="replaceable"><code>attvalue</code></em>
and <em class="replaceable"><code>content</code></em> items are expressions, which can
yield any <span class="productname">PostgreSQL</span> data type. The
argument(s) within <code class="literal">XMLATTRIBUTES</code> generate attributes
of the XML element; the <em class="replaceable"><code>content</code></em> value(s) are
concatenated to form its content.
</p><p>
Examples:
</p><pre class="screen">
SELECT xmlelement(name foo);
xmlelement
------------
<foo/>
SELECT xmlelement(name foo, xmlattributes('xyz' as bar));
xmlelement
------------------
<foo bar="xyz"/>
SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent');
xmlelement
-------------------------------------
<foo bar="2007-01-26">content</foo>
</pre><p>
</p><p>
Element and attribute names that are not valid XML names are
escaped by replacing the offending characters by the sequence
<code class="literal">_x<em class="replaceable"><code>HHHH</code></em>_</code>, where
<em class="replaceable"><code>HHHH</code></em> is the character's Unicode
codepoint in hexadecimal notation. For example:
</p><pre class="screen">
SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b"));
xmlelement
----------------------------------
<foo_x0024_bar a_x0026_b="xyz"/>
</pre><p>
</p><p>
An explicit attribute name need not be specified if the attribute
value is a column reference, in which case the column's name will
be used as the attribute name by default. In other cases, the
attribute must be given an explicit name. So this example is
valid:
</p><pre class="screen">
CREATE TABLE test (a xml, b xml);
SELECT xmlelement(name test, xmlattributes(a, b)) FROM test;
</pre><p>
But these are not:
</p><pre class="screen">
SELECT xmlelement(name test, xmlattributes('constant'), a, b) FROM test;
SELECT xmlelement(name test, xmlattributes(func(a, b))) FROM test;
</pre><p>
</p><p>
Element content, if specified, will be formatted according to
its data type. If the content is itself of type <code class="type">xml</code>,
complex XML documents can be constructed. For example:
</p><pre class="screen">
SELECT xmlelement(name foo, xmlattributes('xyz' as bar),
xmlelement(name abc),
xmlcomment('test'),
xmlelement(name xyz));
xmlelement
----------------------------------------------
<foo bar="xyz"><abc/><!--test--><xyz/></foo>
</pre><p>
Content of other types will be formatted into valid XML character
data. This means in particular that the characters <, >,
and & will be converted to entities. Binary data (data type
<code class="type">bytea</code>) will be represented in base64 or hex
encoding, depending on the setting of the configuration parameter
<a class="xref" href="runtime-config-client.html#GUC-XMLBINARY">xmlbinary</a>. The particular behavior for
individual data types is expected to evolve in order to align the
PostgreSQL mappings with those specified in SQL:2006 and later,
as discussed in <a class="xref" href="xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-CASTS" title="D.3.1.3. Mappings between SQL and XML Data Types and Values">Section D.3.1.3</a>.
</p></div><div class="sect3" id="id-1.5.8.21.5.6"><div class="titlepage"><div><div><h4 class="title">9.15.1.4. <code class="literal">xmlforest</code></h4></div></div></div><a id="id-1.5.8.21.5.6.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlforest</code> ( <em class="replaceable"><code>content</code></em> [<span class="optional"> <code class="literal">AS</code> <em class="replaceable"><code>name</code></em> </span>] [<span class="optional">, ...</span>] ) → <code class="returnvalue">xml</code>
</pre><p>
The <code class="function">xmlforest</code> expression produces an XML
forest (sequence) of elements using the given names and content.
As for <code class="function">xmlelement</code>,
each <em class="replaceable"><code>name</code></em> must be a simple identifier, while
the <em class="replaceable"><code>content</code></em> expressions can have any data
type.
</p><p>
Examples:
</p><pre class="screen">
SELECT xmlforest('abc' AS foo, 123 AS bar);
xmlforest
------------------------------
<foo>abc</foo><bar>123</bar>
SELECT xmlforest(table_name, column_name)
FROM information_schema.columns
WHERE table_schema = 'pg_catalog';
xmlforest
-----------------------------------------------------------------------
<table_name>pg_authid</table_name><column_name>rolname</column_name>
<table_name>pg_authid</table_name><column_name>rolsuper</column_name>
...
</pre><p>
As seen in the second example, the element name can be omitted if
the content value is a column reference, in which case the column
name is used by default. Otherwise, a name must be specified.
</p><p>
Element names that are not valid XML names are escaped as shown
for <code class="function">xmlelement</code> above. Similarly, content
data is escaped to make valid XML content, unless it is already
of type <code class="type">xml</code>.
</p><p>
Note that XML forests are not valid XML documents if they consist
of more than one element, so it might be useful to wrap
<code class="function">xmlforest</code> expressions in
<code class="function">xmlelement</code>.
</p></div><div class="sect3" id="id-1.5.8.21.5.7"><div class="titlepage"><div><div><h4 class="title">9.15.1.5. <code class="literal">xmlpi</code></h4></div></div></div><a id="id-1.5.8.21.5.7.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlpi</code> ( <code class="literal">NAME</code> <em class="replaceable"><code>name</code></em> [<span class="optional">, <em class="replaceable"><code>content</code></em> </span>] ) → <code class="returnvalue">xml</code>
</pre><p>
The <code class="function">xmlpi</code> expression creates an XML
processing instruction.
As for <code class="function">xmlelement</code>,
the <em class="replaceable"><code>name</code></em> must be a simple identifier, while
the <em class="replaceable"><code>content</code></em> expression can have any data type.
The <em class="replaceable"><code>content</code></em>, if present, must not contain the
character sequence <code class="literal">?></code>.
</p><p>
Example:
</p><pre class="screen">
SELECT xmlpi(name php, 'echo "hello world";');
xmlpi
-----------------------------
<?php echo "hello world";?>
</pre><p>
</p></div><div class="sect3" id="id-1.5.8.21.5.8"><div class="titlepage"><div><div><h4 class="title">9.15.1.6. <code class="literal">xmlroot</code></h4></div></div></div><a id="id-1.5.8.21.5.8.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlroot</code> ( <code class="type">xml</code>, <code class="literal">VERSION</code> {<code class="type">text</code>|<code class="literal">NO VALUE</code>} [<span class="optional">, <code class="literal">STANDALONE</code> {<code class="literal">YES</code>|<code class="literal">NO</code>|<code class="literal">NO VALUE</code>} </span>] ) → <code class="returnvalue">xml</code>
</pre><p>
The <code class="function">xmlroot</code> expression alters the properties
of the root node of an XML value. If a version is specified,
it replaces the value in the root node's version declaration; if a
standalone setting is specified, it replaces the value in the
root node's standalone declaration.
</p><p>
</p><pre class="screen">
SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>abc</content>'),
version '1.0', standalone yes);
xmlroot
----------------------------------------
<?xml version="1.0" standalone="yes"?>
<content>abc</content>
</pre><p>
</p></div><div class="sect3" id="FUNCTIONS-XML-XMLAGG"><div class="titlepage"><div><div><h4 class="title">9.15.1.7. <code class="literal">xmlagg</code></h4></div></div></div><a id="id-1.5.8.21.5.9.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xmlagg</code> ( <code class="type">xml</code> ) → <code class="returnvalue">xml</code>
</pre><p>
The function <code class="function">xmlagg</code> is, unlike the other
functions described here, an aggregate function. It concatenates the
input values to the aggregate function call,
much like <code class="function">xmlconcat</code> does, except that concatenation
occurs across rows rather than across expressions in a single row.
See <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a> for additional information
about aggregate functions.
</p><p>
Example:
</p><pre class="screen">
CREATE TABLE test (y int, x xml);
INSERT INTO test VALUES (1, '<foo>abc</foo>');
INSERT INTO test VALUES (2, '<bar/>');
SELECT xmlagg(x) FROM test;
xmlagg
----------------------
<foo>abc</foo><bar/>
</pre><p>
</p><p>
To determine the order of the concatenation, an <code class="literal">ORDER BY</code>
clause may be added to the aggregate call as described in
<a class="xref" href="sql-expressions.html#SYNTAX-AGGREGATES" title="4.2.7. Aggregate Expressions">Section 4.2.7</a>. For example:
</p><pre class="screen">
SELECT xmlagg(x ORDER BY y DESC) FROM test;
xmlagg
----------------------
<bar/><foo>abc</foo>
</pre><p>
</p><p>
The following non-standard approach used to be recommended
in previous versions, and may still be useful in specific
cases:
</p><pre class="screen">
SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
xmlagg
----------------------
<bar/><foo>abc</foo>
</pre><p>
</p></div></div><div class="sect2" id="FUNCTIONS-XML-PREDICATES"><div class="titlepage"><div><div><h3 class="title">9.15.2. XML Predicates</h3></div></div></div><p>
The expressions described in this section check properties
of <code class="type">xml</code> values.
</p><div class="sect3" id="id-1.5.8.21.6.3"><div class="titlepage"><div><div><h4 class="title">9.15.2.1. <code class="literal">IS DOCUMENT</code></h4></div></div></div><a id="id-1.5.8.21.6.3.2" class="indexterm"></a><pre class="synopsis">
<code class="type">xml</code> <code class="literal">IS DOCUMENT</code> → <code class="returnvalue">boolean</code>
</pre><p>
The expression <code class="literal">IS DOCUMENT</code> returns true if the
argument XML value is a proper XML document, false if it is not
(that is, it is a content fragment), or null if the argument is
null. See <a class="xref" href="datatype-xml.html" title="8.13. XML Type">Section 8.13</a> about the difference
between documents and content fragments.
</p></div><div class="sect3" id="id-1.5.8.21.6.4"><div class="titlepage"><div><div><h4 class="title">9.15.2.2. <code class="literal">IS NOT DOCUMENT</code></h4></div></div></div><a id="id-1.5.8.21.6.4.2" class="indexterm"></a><pre class="synopsis">
<code class="type">xml</code> <code class="literal">IS NOT DOCUMENT</code> → <code class="returnvalue">boolean</code>
</pre><p>
The expression <code class="literal">IS NOT DOCUMENT</code> returns false if the
argument XML value is a proper XML document, true if it is not (that is,
it is a content fragment), or null if the argument is null.
</p></div><div class="sect3" id="XML-EXISTS"><div class="titlepage"><div><div><h4 class="title">9.15.2.3. <code class="literal">XMLEXISTS</code></h4></div></div></div><a id="id-1.5.8.21.6.5.2" class="indexterm"></a><pre class="synopsis">
<code class="function">XMLEXISTS</code> ( <code class="type">text</code> <code class="literal">PASSING</code> [<span class="optional"><code class="literal">BY</code> {<code class="literal">REF</code>|<code class="literal">VALUE</code>}</span>] <code class="type">xml</code> [<span class="optional"><code class="literal">BY</code> {<code class="literal">REF</code>|<code class="literal">VALUE</code>}</span>] ) → <code class="returnvalue">boolean</code>
</pre><p>
The function <code class="function">xmlexists</code> evaluates an XPath 1.0
expression (the first argument), with the passed XML value as its context
item. The function returns false if the result of that evaluation
yields an empty node-set, true if it yields any other value. The
function returns null if any argument is null. A nonnull value
passed as the context item must be an XML document, not a content
fragment or any non-XML value.
</p><p>
Example:
</p><pre class="screen">
SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY VALUE '<towns><town>Toronto</town><town>Ottawa</town></towns>');
xmlexists
------------
t
(1 row)
</pre><p>
</p><p>
The <code class="literal">BY REF</code> and <code class="literal">BY VALUE</code> clauses
are accepted in <span class="productname">PostgreSQL</span>, but are ignored,
as discussed in <a class="xref" href="xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-POSTGRESQL" title="D.3.2. Incidental Limits of the Implementation">Section D.3.2</a>.
</p><p>
In the SQL standard, the <code class="function">xmlexists</code> function
evaluates an expression in the XML Query language,
but <span class="productname">PostgreSQL</span> allows only an XPath 1.0
expression, as discussed in
<a class="xref" href="xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-XPATH1" title="D.3.1. Queries Are Restricted to XPath 1.0">Section D.3.1</a>.
</p></div><div class="sect3" id="XML-IS-WELL-FORMED"><div class="titlepage"><div><div><h4 class="title">9.15.2.4. <code class="literal">xml_is_well_formed</code></h4></div></div></div><a id="id-1.5.8.21.6.6.2" class="indexterm"></a><a id="id-1.5.8.21.6.6.3" class="indexterm"></a><a id="id-1.5.8.21.6.6.4" class="indexterm"></a><pre class="synopsis">
<code class="function">xml_is_well_formed</code> ( <code class="type">text</code> ) → <code class="returnvalue">boolean</code>
<code class="function">xml_is_well_formed_document</code> ( <code class="type">text</code> ) → <code class="returnvalue">boolean</code>
<code class="function">xml_is_well_formed_content</code> ( <code class="type">text</code> ) → <code class="returnvalue">boolean</code>
</pre><p>
These functions check whether a <code class="type">text</code> string represents
well-formed XML, returning a Boolean result.
<code class="function">xml_is_well_formed_document</code> checks for a well-formed
document, while <code class="function">xml_is_well_formed_content</code> checks
for well-formed content. <code class="function">xml_is_well_formed</code> does
the former if the <a class="xref" href="runtime-config-client.html#GUC-XMLOPTION">xmloption</a> configuration
parameter is set to <code class="literal">DOCUMENT</code>, or the latter if it is set to
<code class="literal">CONTENT</code>. This means that
<code class="function">xml_is_well_formed</code> is useful for seeing whether
a simple cast to type <code class="type">xml</code> will succeed, whereas the other two
functions are useful for seeing whether the corresponding variants of
<code class="function">XMLPARSE</code> will succeed.
</p><p>
Examples:
</p><pre class="screen">
SET xmloption TO DOCUMENT;
SELECT xml_is_well_formed('<>');
xml_is_well_formed
--------------------
f
(1 row)
SELECT xml_is_well_formed('<abc/>');
xml_is_well_formed
--------------------
t
(1 row)
SET xmloption TO CONTENT;
SELECT xml_is_well_formed('abc');
xml_is_well_formed
--------------------
t
(1 row)
SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>');
xml_is_well_formed_document
-----------------------------
t
(1 row)
SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>');
xml_is_well_formed_document
-----------------------------
f
(1 row)
</pre><p>
The last example shows that the checks include whether
namespaces are correctly matched.
</p></div></div><div class="sect2" id="FUNCTIONS-XML-PROCESSING"><div class="titlepage"><div><div><h3 class="title">9.15.3. Processing XML</h3></div></div></div><p>
To process values of data type <code class="type">xml</code>, PostgreSQL offers
the functions <code class="function">xpath</code> and
<code class="function">xpath_exists</code>, which evaluate XPath 1.0
expressions, and the <code class="function">XMLTABLE</code>
table function.
</p><div class="sect3" id="FUNCTIONS-XML-PROCESSING-XPATH"><div class="titlepage"><div><div><h4 class="title">9.15.3.1. <code class="literal">xpath</code></h4></div></div></div><a id="id-1.5.8.21.7.3.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xpath</code> ( <em class="parameter"><code>xpath</code></em> <code class="type">text</code>, <em class="parameter"><code>xml</code></em> <code class="type">xml</code> [<span class="optional">, <em class="parameter"><code>nsarray</code></em> <code class="type">text[]</code> </span>] ) → <code class="returnvalue">xml[]</code>
</pre><p>
The function <code class="function">xpath</code> evaluates the XPath 1.0
expression <em class="parameter"><code>xpath</code></em> (given as text)
against the XML value
<em class="parameter"><code>xml</code></em>. It returns an array of XML values
corresponding to the node-set produced by the XPath expression.
If the XPath expression returns a scalar value rather than a node-set,
a single-element array is returned.
</p><p>
The second argument must be a well formed XML document. In particular,
it must have a single root node element.
</p><p>
The optional third argument of the function is an array of namespace
mappings. This array should be a two-dimensional <code class="type">text</code> array with
the length of the second axis being equal to 2 (i.e., it should be an
array of arrays, each of which consists of exactly 2 elements).
The first element of each array entry is the namespace name (alias), the
second the namespace URI. It is not required that aliases provided in
this array be the same as those being used in the XML document itself (in
other words, both in the XML document and in the <code class="function">xpath</code>
function context, aliases are <span class="emphasis"><em>local</em></span>).
</p><p>
Example:
</p><pre class="screen">
SELECT xpath('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);
xpath
--------
{test}
(1 row)
</pre><p>
</p><p>
To deal with default (anonymous) namespaces, do something like this:
</p><pre class="screen">
SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a>',
ARRAY[ARRAY['mydefns', 'http://example.com']]);
xpath
--------
{test}
(1 row)
</pre><p>
</p></div><div class="sect3" id="FUNCTIONS-XML-PROCESSING-XPATH-EXISTS"><div class="titlepage"><div><div><h4 class="title">9.15.3.2. <code class="literal">xpath_exists</code></h4></div></div></div><a id="id-1.5.8.21.7.4.2" class="indexterm"></a><pre class="synopsis">
<code class="function">xpath_exists</code> ( <em class="parameter"><code>xpath</code></em> <code class="type">text</code>, <em class="parameter"><code>xml</code></em> <code class="type">xml</code> [<span class="optional">, <em class="parameter"><code>nsarray</code></em> <code class="type">text[]</code> </span>] ) → <code class="returnvalue">boolean</code>
</pre><p>
The function <code class="function">xpath_exists</code> is a specialized form
of the <code class="function">xpath</code> function. Instead of returning the
individual XML values that satisfy the XPath 1.0 expression, this function
returns a Boolean indicating whether the query was satisfied or not
(specifically, whether it produced any value other than an empty node-set).
This function is equivalent to the <code class="literal">XMLEXISTS</code> predicate,
except that it also offers support for a namespace mapping argument.
</p><p>
Example:
</p><pre class="screen">
SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',
ARRAY[ARRAY['my', 'http://example.com']]);
xpath_exists
--------------
t
(1 row)
</pre><p>
</p></div><div class="sect3" id="FUNCTIONS-XML-PROCESSING-XMLTABLE"><div class="titlepage"><div><div><h4 class="title">9.15.3.3. <code class="literal">xmltable</code></h4></div></div></div><a id="id-1.5.8.21.7.5.2" class="indexterm"></a><a id="id-1.5.8.21.7.5.3" class="indexterm"></a><pre class="synopsis">
<code class="function">XMLTABLE</code> (
[<span class="optional"> <code class="literal">XMLNAMESPACES</code> ( <em class="replaceable"><code>namespace_uri</code></em> <code class="literal">AS</code> <em class="replaceable"><code>namespace_name</code></em> [<span class="optional">, ...</span>] ), </span>]
<em class="replaceable"><code>row_expression</code></em> <code class="literal">PASSING</code> [<span class="optional"><code class="literal">BY</code> {<code class="literal">REF</code>|<code class="literal">VALUE</code>}</span>] <em class="replaceable"><code>document_expression</code></em> [<span class="optional"><code class="literal">BY</code> {<code class="literal">REF</code>|<code class="literal">VALUE</code>}</span>]
<code class="literal">COLUMNS</code> <em class="replaceable"><code>name</code></em> { <em class="replaceable"><code>type</code></em> [<span class="optional"><code class="literal">PATH</code> <em class="replaceable"><code>column_expression</code></em></span>] [<span class="optional"><code class="literal">DEFAULT</code> <em class="replaceable"><code>default_expression</code></em></span>] [<span class="optional"><code class="literal">NOT NULL</code> | <code class="literal">NULL</code></span>]
| <code class="literal">FOR ORDINALITY</code> }
[<span class="optional">, ...</span>]
) → <code class="returnvalue">setof record</code>
</pre><p>
The <code class="function">xmltable</code> expression produces a table based
on an XML value, an XPath filter to extract rows, and a
set of column definitions.
Although it syntactically resembles a function, it can only appear
as a table in a query's <code class="literal">FROM</code> clause.
</p><p>
The optional <code class="literal">XMLNAMESPACES</code> clause gives a
comma-separated list of namespace definitions, where
each <em class="replaceable"><code>namespace_uri</code></em> is a <code class="type">text</code>
expression and each <em class="replaceable"><code>namespace_name</code></em> is a simple
identifier. It specifies the XML namespaces used in the document and
their aliases. A default namespace specification is not currently
supported.
</p><p>
The required <em class="replaceable"><code>row_expression</code></em> argument is an
XPath 1.0 expression (given as <code class="type">text</code>) that is evaluated,
passing the XML value <em class="replaceable"><code>document_expression</code></em> as
its context item, to obtain a set of XML nodes. These nodes are what
<code class="function">xmltable</code> transforms into output rows. No rows
will be produced if the <em class="replaceable"><code>document_expression</code></em>
is null, nor if the <em class="replaceable"><code>row_expression</code></em> produces
an empty node-set or any value other than a node-set.
</p><p>
<em class="replaceable"><code>document_expression</code></em> provides the context
item for the <em class="replaceable"><code>row_expression</code></em>. It must be a
well-formed XML document; fragments/forests are not accepted.
The <code class="literal">BY REF</code> and <code class="literal">BY VALUE</code> clauses
are accepted but ignored, as discussed in
<a class="xref" href="xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-POSTGRESQL" title="D.3.2. Incidental Limits of the Implementation">Section D.3.2</a>.
</p><p>
In the SQL standard, the <code class="function">xmltable</code> function
evaluates expressions in the XML Query language,
but <span class="productname">PostgreSQL</span> allows only XPath 1.0
expressions, as discussed in
<a class="xref" href="xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-XPATH1" title="D.3.1. Queries Are Restricted to XPath 1.0">Section D.3.1</a>.
</p><p>
The required <code class="literal">COLUMNS</code> clause specifies the
column(s) that will be produced in the output table.
See the syntax summary above for the format.
A name is required for each column, as is a data type
(unless <code class="literal">FOR ORDINALITY</code> is specified, in which case
type <code class="type">integer</code> is implicit). The path, default and
nullability clauses are optional.
</p><p>
A column marked <code class="literal">FOR ORDINALITY</code> will be populated
with row numbers, starting with 1, in the order of nodes retrieved from
the <em class="replaceable"><code>row_expression</code></em>'s result node-set.
At most one column may be marked <code class="literal">FOR ORDINALITY</code>.
</p><div class="note"><h3 class="title">Note</h3><p>
XPath 1.0 does not specify an order for nodes in a node-set, so code
that relies on a particular order of the results will be
implementation-dependent. Details can be found in
<a class="xref" href="xml-limits-conformance.html#XML-XPATH-1-SPECIFICS" title="D.3.1.2. Restriction of XPath to 1.0">Section D.3.1.2</a>.
</p></div><p>
The <em class="replaceable"><code>column_expression</code></em> for a column is an
XPath 1.0 expression that is evaluated for each row, with the current
node from the <em class="replaceable"><code>row_expression</code></em> result as its
context item, to find the value of the column. If
no <em class="replaceable"><code>column_expression</code></em> is given, then the
column name is used as an implicit path.
</p><p>
If a column's XPath expression returns a non-XML value (which is limited
to string, boolean, or double in XPath 1.0) and the column has a
PostgreSQL type other than <code class="type">xml</code>, the column will be set
as if by assigning the value's string representation to the PostgreSQL
type. (If the value is a boolean, its string representation is taken
to be <code class="literal">1</code> or <code class="literal">0</code> if the output
column's type category is numeric, otherwise <code class="literal">true</code> or
<code class="literal">false</code>.)
</p><p>
If a column's XPath expression returns a non-empty set of XML nodes
and the column's PostgreSQL type is <code class="type">xml</code>, the column will
be assigned the expression result exactly, if it is of document or
content form.
<a href="#ftn.id-1.5.8.21.7.5.15.2" class="footnote"><sup class="footnote" id="id-1.5.8.21.7.5.15.2">[8]</sup></a>
</p><p>
A non-XML result assigned to an <code class="type">xml</code> output column produces
content, a single text node with the string value of the result.
An XML result assigned to a column of any other type may not have more than
one node, or an error is raised. If there is exactly one node, the column
will be set as if by assigning the node's string
value (as defined for the XPath 1.0 <code class="function">string</code> function)
to the PostgreSQL type.
</p><p>
The string value of an XML element is the concatenation, in document order,
of all text nodes contained in that element and its descendants. The string
value of an element with no descendant text nodes is an
empty string (not <code class="literal">NULL</code>).
Any <code class="literal">xsi:nil</code> attributes are ignored.
Note that the whitespace-only <code class="literal">text()</code> node between two non-text
elements is preserved, and that leading whitespace on a <code class="literal">text()</code>
node is not flattened.
The XPath 1.0 <code class="function">string</code> function may be consulted for the
rules defining the string value of other XML node types and non-XML values.
</p><p>
The conversion rules presented here are not exactly those of the SQL
standard, as discussed in <a class="xref" href="xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-CASTS" title="D.3.1.3. Mappings between SQL and XML Data Types and Values">Section D.3.1.3</a>.
</p><p>
If the path expression returns an empty node-set
(typically, when it does not match)
for a given row, the column will be set to <code class="literal">NULL</code>, unless
a <em class="replaceable"><code>default_expression</code></em> is specified; then the
value resulting from evaluating that expression is used.
</p><p>
A <em class="replaceable"><code>default_expression</code></em>, rather than being
evaluated immediately when <code class="function">xmltable</code> is called,
is evaluated each time a default is needed for the column.
If the expression qualifies as stable or immutable, the repeat
evaluation may be skipped.
This means that you can usefully use volatile functions like
<code class="function">nextval</code> in
<em class="replaceable"><code>default_expression</code></em>.
</p><p>
Columns may be marked <code class="literal">NOT NULL</code>. If the
<em class="replaceable"><code>column_expression</code></em> for a <code class="literal">NOT
NULL</code> column does not match anything and there is
no <code class="literal">DEFAULT</code> or
the <em class="replaceable"><code>default_expression</code></em> also evaluates to null,
an error is reported.
</p><p>
Examples:
</p><pre class="screen">
CREATE TABLE xmldata AS SELECT
xml $$
<ROWS>
<ROW id="1">
<COUNTRY_ID>AU</COUNTRY_ID>
<COUNTRY_NAME>Australia</COUNTRY_NAME>
</ROW>
<ROW id="5">
<COUNTRY_ID>JP</COUNTRY_ID>
<COUNTRY_NAME>Japan</COUNTRY_NAME>
<PREMIER_NAME>Shinzo Abe</PREMIER_NAME>
<SIZE unit="sq_mi">145935</SIZE>
</ROW>
<ROW id="6">
<COUNTRY_ID>SG</COUNTRY_ID>
<COUNTRY_NAME>Singapore</COUNTRY_NAME>
<SIZE unit="sq_km">697</SIZE>
</ROW>
</ROWS>
$$ AS data;
SELECT xmltable.*
FROM xmldata,
XMLTABLE('//ROWS/ROW'
PASSING data
COLUMNS id int PATH '@id',
ordinality FOR ORDINALITY,
"COUNTRY_NAME" text,
country_id text PATH 'COUNTRY_ID',
size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
size_other text PATH
'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');
id | ordinality | COUNTRY_NAME | country_id | size_sq_km | size_other | premier_name
----+------------+--------------+------------+------------+--------------+---------------
1 | 1 | Australia | AU | | | not specified
5 | 2 | Japan | JP | | 145935 sq_mi | Shinzo Abe
6 | 3 | Singapore | SG | 697 | | not specified
</pre><p>
The following example shows concatenation of multiple text() nodes,
usage of the column name as XPath filter, and the treatment of whitespace,
XML comments and processing instructions:
</p><pre class="screen">
CREATE TABLE xmlelements AS SELECT
xml $$
<root>
<element> Hello<!-- xyxxz -->2a2<?aaaaa?> <!--x--> bbb<x>xxx</x>CC </element>
</root>
$$ AS data;
SELECT xmltable.*
FROM xmlelements, XMLTABLE('/root' PASSING data COLUMNS element text);
element
-------------------------
Hello2a2 bbbxxxCC
</pre><p>
</p><p>
The following example illustrates how
the <code class="literal">XMLNAMESPACES</code> clause can be used to specify
a list of namespaces
used in the XML document as well as in the XPath expressions:
</p><pre class="screen">
WITH xmldata(data) AS (VALUES ('
<example xmlns="http://example.com/myns" xmlns:B="http://example.com/b">
<item foo="1" B:bar="2"/>
<item foo="3" B:bar="4"/>
<item foo="4" B:bar="5"/>
</example>'::xml)
)
SELECT xmltable.*
FROM XMLTABLE(XMLNAMESPACES('http://example.com/myns' AS x,
'http://example.com/b' AS "B"),
'/x:example/x:item'
PASSING (SELECT data FROM xmldata)
COLUMNS foo int PATH '@foo',
bar int PATH '@B:bar');
foo | bar
-----+-----
1 | 2
3 | 4
4 | 5
(3 rows)
</pre><p>
</p></div></div><div class="sect2" id="FUNCTIONS-XML-MAPPING"><div class="titlepage"><div><div><h3 class="title">9.15.4. Mapping Tables to XML</h3></div></div></div><a id="id-1.5.8.21.8.2" class="indexterm"></a><p>
The following functions map the contents of relational tables to
XML values. They can be thought of as XML export functionality:
</p><pre class="synopsis">
<code class="function">table_to_xml</code> ( <em class="parameter"><code>table</code></em> <code class="type">regclass</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
<code class="function">query_to_xml</code> ( <em class="parameter"><code>query</code></em> <code class="type">text</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
<code class="function">cursor_to_xml</code> ( <em class="parameter"><code>cursor</code></em> <code class="type">refcursor</code>, <em class="parameter"><code>count</code></em> <code class="type">integer</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
</pre><p>
</p><p>
<code class="function">table_to_xml</code> maps the content of the named
table, passed as parameter <em class="parameter"><code>table</code></em>. The
<code class="type">regclass</code> type accepts strings identifying tables using the
usual notation, including optional schema qualification and
double quotes (see <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a> for details).
<code class="function">query_to_xml</code> executes the
query whose text is passed as parameter
<em class="parameter"><code>query</code></em> and maps the result set.
<code class="function">cursor_to_xml</code> fetches the indicated number of
rows from the cursor specified by the parameter
<em class="parameter"><code>cursor</code></em>. This variant is recommended if
large tables have to be mapped, because the result value is built
up in memory by each function.
</p><p>
If <em class="parameter"><code>tableforest</code></em> is false, then the resulting
XML document looks like this:
</p><pre class="screen">
<tablename>
<row>
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</row>
<row>
...
</row>
...
</tablename>
</pre><p>
If <em class="parameter"><code>tableforest</code></em> is true, the result is an
XML content fragment that looks like this:
</p><pre class="screen">
<tablename>
<columnname1>data</columnname1>
<columnname2>data</columnname2>
</tablename>
<tablename>
...
</tablename>
...
</pre><p>
If no table name is available, that is, when mapping a query or a
cursor, the string <code class="literal">table</code> is used in the first
format, <code class="literal">row</code> in the second format.
</p><p>
The choice between these formats is up to the user. The first
format is a proper XML document, which will be important in many
applications. The second format tends to be more useful in the
<code class="function">cursor_to_xml</code> function if the result values are to be
reassembled into one document later on. The functions for
producing XML content discussed above, in particular
<code class="function">xmlelement</code>, can be used to alter the results
to taste.
</p><p>
The data values are mapped in the same way as described for the
function <code class="function">xmlelement</code> above.
</p><p>
The parameter <em class="parameter"><code>nulls</code></em> determines whether null
values should be included in the output. If true, null values in
columns are represented as:
</p><pre class="screen">
<columnname xsi:nil="true"/>
</pre><p>
where <code class="literal">xsi</code> is the XML namespace prefix for XML
Schema Instance. An appropriate namespace declaration will be
added to the result value. If false, columns containing null
values are simply omitted from the output.
</p><p>
The parameter <em class="parameter"><code>targetns</code></em> specifies the
desired XML namespace of the result. If no particular namespace
is wanted, an empty string should be passed.
</p><p>
The following functions return XML Schema documents describing the
mappings performed by the corresponding functions above:
</p><pre class="synopsis">
<code class="function">table_to_xmlschema</code> ( <em class="parameter"><code>table</code></em> <code class="type">regclass</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
<code class="function">query_to_xmlschema</code> ( <em class="parameter"><code>query</code></em> <code class="type">text</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
<code class="function">cursor_to_xmlschema</code> ( <em class="parameter"><code>cursor</code></em> <code class="type">refcursor</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
</pre><p>
It is essential that the same parameters are passed in order to
obtain matching XML data mappings and XML Schema documents.
</p><p>
The following functions produce XML data mappings and the
corresponding XML Schema in one document (or forest), linked
together. They can be useful where self-contained and
self-describing results are wanted:
</p><pre class="synopsis">
<code class="function">table_to_xml_and_xmlschema</code> ( <em class="parameter"><code>table</code></em> <code class="type">regclass</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
<code class="function">query_to_xml_and_xmlschema</code> ( <em class="parameter"><code>query</code></em> <code class="type">text</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
</pre><p>
</p><p>
In addition, the following functions are available to produce
analogous mappings of entire schemas or the entire current
database:
</p><pre class="synopsis">
<code class="function">schema_to_xml</code> ( <em class="parameter"><code>schema</code></em> <code class="type">name</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
<code class="function">schema_to_xmlschema</code> ( <em class="parameter"><code>schema</code></em> <code class="type">name</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
<code class="function">schema_to_xml_and_xmlschema</code> ( <em class="parameter"><code>schema</code></em> <code class="type">name</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
<code class="function">database_to_xml</code> ( <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
<code class="function">database_to_xmlschema</code> ( <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
<code class="function">database_to_xml_and_xmlschema</code> ( <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,
<em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code>
</pre><p>
These functions ignore tables that are not readable by the current user.
The database-wide functions additionally ignore schemas that the current
user does not have <code class="literal">USAGE</code> (lookup) privilege for.
</p><p>
Note that these potentially produce a lot of data, which needs to
be built up in memory. When requesting content mappings of large
schemas or databases, it might be worthwhile to consider mapping the
tables separately instead, possibly even through a cursor.
</p><p>
The result of a schema content mapping looks like this:
</p><pre class="screen">
<schemaname>
table1-mapping
table2-mapping
...
</schemaname></pre><p>
where the format of a table mapping depends on the
<em class="parameter"><code>tableforest</code></em> parameter as explained above.
</p><p>
The result of a database content mapping looks like this:
</p><pre class="screen">
<dbname>
<schema1name>
...
</schema1name>
<schema2name>
...
</schema2name>
...
</dbname></pre><p>
where the schema mapping is as above.
</p><p>
As an example of using the output produced by these functions,
<a class="xref" href="functions-xml.html#XSLT-XML-HTML" title="Example 9.1. XSLT Stylesheet for Converting SQL/XML Output to HTML">Example 9.1</a> shows an XSLT stylesheet that
converts the output of
<code class="function">table_to_xml_and_xmlschema</code> to an HTML
document containing a tabular rendition of the table data. In a
similar manner, the results from these functions can be
converted into other XML-based formats.
</p><div class="example" id="XSLT-XML-HTML"><p class="title"><strong>Example 9.1. XSLT Stylesheet for Converting SQL/XML Output to HTML</strong></p><div class="example-contents"><pre class="programlisting">
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.w3.org/1999/xhtml"
>
<xsl:output method="xml"
doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"
doctype-public="-//W3C/DTD XHTML 1.0 Strict//EN"
indent="yes"/>
<xsl:template match="/*">
<xsl:variable name="schema" select="//xsd:schema"/>
<xsl:variable name="tabletypename"
select="$schema/xsd:element[@name=name(current())]/@type"/>
<xsl:variable name="rowtypename"
select="$schema/xsd:complexType[@name=$tabletypename]/xsd:sequence/xsd:element[@name='row']/@type"/>
<html>
<head>
<title><xsl:value-of select="name(current())"/></title>
</head>
<body>
<table>
<tr>
<xsl:for-each select="$schema/xsd:complexType[@name=$rowtypename]/xsd:sequence/xsd:element/@name">
<th><xsl:value-of select="."/></th>
</xsl:for-each>
</tr>
<xsl:for-each select="row">
<tr>
<xsl:for-each select="*">
<td><xsl:value-of select="."/></td>
</xsl:for-each>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
</pre></div></div><br class="example-break" /></div><div class="footnotes"><br /><hr style="width:100; text-align:left;margin-left: 0" /><div id="ftn.id-1.5.8.21.7.5.15.2" class="footnote"><p><a href="#id-1.5.8.21.7.5.15.2" class="para"><sup class="para">[8] </sup></a>
A result containing more than one element node at the top level, or
non-whitespace text outside of an element, is an example of content form.
An XPath result can be of neither form, for example if it returns an
attribute node selected from the element that contains it. Such a result
will be put into content form with each such disallowed node replaced by
its string value, as defined for the XPath 1.0
<code class="function">string</code> function.
</p></div></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-uuid.html" title="9.14. UUID Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-json.html" title="9.16. JSON Functions and Operators">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.14. UUID Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.16. JSON Functions and Operators</td></tr></table></div></body></html>
|
