codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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 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 16.3 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 /></div><div class="sect1" id="FUNCTIONS-XML"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.15. XML Functions <a href="#FUNCTIONS-XML" class="id_link">#</a></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>3 The functions and function-like expressions described in this4 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>5 type. The function-like expressions <code class="function">xmlparse</code>6 and <code class="function">xmlserialize</code> for converting to and from7 type <code class="type">xml</code> are documented there, not in this section.8 </p><p>9 Use of most of these functions10 requires <span class="productname">PostgreSQL</span> to have been built11 with <code class="command">configure --with-libxml</code>.12 </p><div class="sect2" id="FUNCTIONS-PRODUCING-XML"><div class="titlepage"><div><div><h3 class="title">9.15.1. Producing XML Content <a href="#FUNCTIONS-PRODUCING-XML" class="id_link">#</a></h3></div></div></div><p>13 A set of functions and function-like expressions is available for14 producing XML content from SQL data. As such, they are15 particularly suitable for formatting query results into XML16 documents for processing in client applications.17 </p><div class="sect3" id="FUNCTIONS-PRODUCING-XML-XMLCOMMENT"><div class="titlepage"><div><div><h4 class="title">9.15.1.1. <code class="literal">xmlcomment</code> <a href="#FUNCTIONS-PRODUCING-XML-XMLCOMMENT" class="id_link">#</a></h4></div></div></div><a id="id-1.5.8.21.5.3.2" class="indexterm"></a><pre class="synopsis">18<code class="function">xmlcomment</code> ( <code class="type">text</code> ) → <code class="returnvalue">xml</code>19</pre><p>20 The function <code class="function">xmlcomment</code> creates an XML value21 containing an XML comment with the specified text as content.22 The text cannot contain <span class="quote">“<span class="quote"><code class="literal">--</code></span>”</span> or end with a23 <span class="quote">“<span class="quote"><code class="literal">-</code></span>”</span>, otherwise the resulting construct24 would not be a valid XML comment.25 If the argument is null, the result is null.26 </p><p>27 Example:28</p><pre class="screen">29SELECT xmlcomment('hello');30 31 xmlcomment32--------------33 <!--hello-->34</pre><p>35 </p></div><div class="sect3" id="FUNCTIONS-PRODUCING-XML-XMLCONCAT"><div class="titlepage"><div><div><h4 class="title">9.15.1.2. <code class="literal">xmlconcat</code> <a href="#FUNCTIONS-PRODUCING-XML-XMLCONCAT" class="id_link">#</a></h4></div></div></div><a id="id-1.5.8.21.5.4.2" class="indexterm"></a><pre class="synopsis">36<code class="function">xmlconcat</code> ( <code class="type">xml</code> [<span class="optional">, ...</span>] ) → <code class="returnvalue">xml</code>37</pre><p>38 The function <code class="function">xmlconcat</code> concatenates a list39 of individual XML values to create a single value containing an40 XML content fragment. Null values are omitted; the result is41 only null if there are no nonnull arguments.42 </p><p>43 Example:44</p><pre class="screen">45SELECT xmlconcat('<abc/>', '<bar>foo</bar>');46 47 xmlconcat48----------------------49 <abc/><bar>foo</bar>50</pre><p>51 </p><p>52 XML declarations, if present, are combined as follows. If all53 argument values have the same XML version declaration, that54 version is used in the result, else no version is used. If all55 argument values have the standalone declaration value56 <span class="quote">“<span class="quote">yes</span>”</span>, then that value is used in the result. If57 all argument values have a standalone declaration value and at58 least one is <span class="quote">“<span class="quote">no</span>”</span>, then that is used in the result.59 Else the result will have no standalone declaration. If the60 result is determined to require a standalone declaration but no61 version declaration, a version declaration with version 1.0 will62 be used because XML requires an XML declaration to contain a63 version declaration. Encoding declarations are ignored and64 removed in all cases.65 </p><p>66 Example:67</p><pre class="screen">68SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>');69 70 xmlconcat71-----------------------------------72 <?xml version="1.1"?><foo/><bar/>73</pre><p>74 </p></div><div class="sect3" id="FUNCTIONS-PRODUCING-XML-XMLELEMENT"><div class="titlepage"><div><div><h4 class="title">9.15.1.3. <code class="literal">xmlelement</code> <a href="#FUNCTIONS-PRODUCING-XML-XMLELEMENT" class="id_link">#</a></h4></div></div></div><a id="id-1.5.8.21.5.5.2" class="indexterm"></a><pre class="synopsis">75<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>76</pre><p>77 The <code class="function">xmlelement</code> expression produces an XML78 element with the given name, attributes, and content.79 The <em class="replaceable"><code>name</code></em>80 and <em class="replaceable"><code>attname</code></em> items shown in the syntax are81 simple identifiers, not values. The <em class="replaceable"><code>attvalue</code></em>82 and <em class="replaceable"><code>content</code></em> items are expressions, which can83 yield any <span class="productname">PostgreSQL</span> data type. The84 argument(s) within <code class="literal">XMLATTRIBUTES</code> generate attributes85 of the XML element; the <em class="replaceable"><code>content</code></em> value(s) are86 concatenated to form its content.87 </p><p>88 Examples:89</p><pre class="screen">90SELECT xmlelement(name foo);91 92 xmlelement93------------94 <foo/>95 96SELECT xmlelement(name foo, xmlattributes('xyz' as bar));97 98 xmlelement99------------------100 <foo bar="xyz"/>101 102SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent');103 104 xmlelement105-------------------------------------106 <foo bar="2007-01-26">content</foo>107</pre><p>108 </p><p>109 Element and attribute names that are not valid XML names are110 escaped by replacing the offending characters by the sequence111 <code class="literal">_x<em class="replaceable"><code>HHHH</code></em>_</code>, where112 <em class="replaceable"><code>HHHH</code></em> is the character's Unicode113 codepoint in hexadecimal notation. For example:114</p><pre class="screen">115SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b"));116 117 xmlelement118----------------------------------119 <foo_x0024_bar a_x0026_b="xyz"/>120</pre><p>121 </p><p>122 An explicit attribute name need not be specified if the attribute123 value is a column reference, in which case the column's name will124 be used as the attribute name by default. In other cases, the125 attribute must be given an explicit name. So this example is126 valid:127</p><pre class="screen">128CREATE TABLE test (a xml, b xml);129SELECT xmlelement(name test, xmlattributes(a, b)) FROM test;130</pre><p>131 But these are not:132</p><pre class="screen">133SELECT xmlelement(name test, xmlattributes('constant'), a, b) FROM test;134SELECT xmlelement(name test, xmlattributes(func(a, b))) FROM test;135</pre><p>136 </p><p>137 Element content, if specified, will be formatted according to138 its data type. If the content is itself of type <code class="type">xml</code>,139 complex XML documents can be constructed. For example:140</p><pre class="screen">141SELECT xmlelement(name foo, xmlattributes('xyz' as bar),142 xmlelement(name abc),143 xmlcomment('test'),144 xmlelement(name xyz));145 146 xmlelement147----------------------------------------------148 <foo bar="xyz"><abc/><!--test--><xyz/></foo>149</pre><p>150 151 Content of other types will be formatted into valid XML character152 data. This means in particular that the characters <, >,153 and & will be converted to entities. Binary data (data type154 <code class="type">bytea</code>) will be represented in base64 or hex155 encoding, depending on the setting of the configuration parameter156 <a class="xref" href="runtime-config-client.html#GUC-XMLBINARY">xmlbinary</a>. The particular behavior for157 individual data types is expected to evolve in order to align the158 PostgreSQL mappings with those specified in SQL:2006 and later,159 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>.160 </p></div><div class="sect3" id="FUNCTIONS-PRODUCING-XML-XMLFOREST"><div class="titlepage"><div><div><h4 class="title">9.15.1.4. <code class="literal">xmlforest</code> <a href="#FUNCTIONS-PRODUCING-XML-XMLFOREST" class="id_link">#</a></h4></div></div></div><a id="id-1.5.8.21.5.6.2" class="indexterm"></a><pre class="synopsis">161<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>162</pre><p>163 The <code class="function">xmlforest</code> expression produces an XML164 forest (sequence) of elements using the given names and content.165 As for <code class="function">xmlelement</code>,166 each <em class="replaceable"><code>name</code></em> must be a simple identifier, while167 the <em class="replaceable"><code>content</code></em> expressions can have any data168 type.169 </p><p>170 Examples:171</p><pre class="screen">172SELECT xmlforest('abc' AS foo, 123 AS bar);173 174 xmlforest175------------------------------176 <foo>abc</foo><bar>123</bar>177 178 179SELECT xmlforest(table_name, column_name)180FROM information_schema.columns181WHERE table_schema = 'pg_catalog';182 183 xmlforest184-----------------------------------------------------------------------185 <table_name>pg_authid</table_name><column_name>rolname</column_name>186 <table_name>pg_authid</table_name><column_name>rolsuper</column_name>187 ...188</pre><p>189 190 As seen in the second example, the element name can be omitted if191 the content value is a column reference, in which case the column192 name is used by default. Otherwise, a name must be specified.193 </p><p>194 Element names that are not valid XML names are escaped as shown195 for <code class="function">xmlelement</code> above. Similarly, content196 data is escaped to make valid XML content, unless it is already197 of type <code class="type">xml</code>.198 </p><p>199 Note that XML forests are not valid XML documents if they consist200 of more than one element, so it might be useful to wrap201 <code class="function">xmlforest</code> expressions in202 <code class="function">xmlelement</code>.203 </p></div><div class="sect3" id="FUNCTIONS-PRODUCING-XML-XMLPI"><div class="titlepage"><div><div><h4 class="title">9.15.1.5. <code class="literal">xmlpi</code> <a href="#FUNCTIONS-PRODUCING-XML-XMLPI" class="id_link">#</a></h4></div></div></div><a id="id-1.5.8.21.5.7.2" class="indexterm"></a><pre class="synopsis">204<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>205</pre><p>206 The <code class="function">xmlpi</code> expression creates an XML207 processing instruction.208 As for <code class="function">xmlelement</code>,209 the <em class="replaceable"><code>name</code></em> must be a simple identifier, while210 the <em class="replaceable"><code>content</code></em> expression can have any data type.211 The <em class="replaceable"><code>content</code></em>, if present, must not contain the212 character sequence <code class="literal">?></code>.213 </p><p>214 Example:215</p><pre class="screen">216SELECT xmlpi(name php, 'echo "hello world";');217 218 xmlpi219-----------------------------220 <?php echo "hello world";?>221</pre><p>222 </p></div><div class="sect3" id="FUNCTIONS-PRODUCING-XML-XMLROOT"><div class="titlepage"><div><div><h4 class="title">9.15.1.6. <code class="literal">xmlroot</code> <a href="#FUNCTIONS-PRODUCING-XML-XMLROOT" class="id_link">#</a></h4></div></div></div><a id="id-1.5.8.21.5.8.2" class="indexterm"></a><pre class="synopsis">223<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>224</pre><p>225 The <code class="function">xmlroot</code> expression alters the properties226 of the root node of an XML value. If a version is specified,227 it replaces the value in the root node's version declaration; if a228 standalone setting is specified, it replaces the value in the229 root node's standalone declaration.230 </p><p>231</p><pre class="screen">232SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>abc</content>'),233 version '1.0', standalone yes);234 235 xmlroot236----------------------------------------237 <?xml version="1.0" standalone="yes"?>238 <content>abc</content>239</pre><p>240 </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> <a href="#FUNCTIONS-XML-XMLAGG" class="id_link">#</a></h4></div></div></div><a id="id-1.5.8.21.5.9.2" class="indexterm"></a><pre class="synopsis">241<code class="function">xmlagg</code> ( <code class="type">xml</code> ) → <code class="returnvalue">xml</code>242</pre><p>243 The function <code class="function">xmlagg</code> is, unlike the other244 functions described here, an aggregate function. It concatenates the245 input values to the aggregate function call,246 much like <code class="function">xmlconcat</code> does, except that concatenation247 occurs across rows rather than across expressions in a single row.248 See <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a> for additional information249 about aggregate functions.250 </p><p>251 Example:252</p><pre class="screen">253CREATE TABLE test (y int, x xml);254INSERT INTO test VALUES (1, '<foo>abc</foo>');255INSERT INTO test VALUES (2, '<bar/>');256SELECT xmlagg(x) FROM test;257 xmlagg258----------------------259 <foo>abc</foo><bar/>260</pre><p>261 </p><p>262 To determine the order of the concatenation, an <code class="literal">ORDER BY</code>263 clause may be added to the aggregate call as described in264 <a class="xref" href="sql-expressions.html#SYNTAX-AGGREGATES" title="4.2.7. Aggregate Expressions">Section 4.2.7</a>. For example:265 266</p><pre class="screen">267SELECT xmlagg(x ORDER BY y DESC) FROM test;268 xmlagg269----------------------270 <bar/><foo>abc</foo>271</pre><p>272 </p><p>273 The following non-standard approach used to be recommended274 in previous versions, and may still be useful in specific275 cases:276 277</p><pre class="screen">278SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;279 xmlagg280----------------------281 <bar/><foo>abc</foo>282</pre><p>283 </p></div></div><div class="sect2" id="FUNCTIONS-XML-PREDICATES"><div class="titlepage"><div><div><h3 class="title">9.15.2. XML Predicates <a href="#FUNCTIONS-XML-PREDICATES" class="id_link">#</a></h3></div></div></div><p>284 The expressions described in this section check properties285 of <code class="type">xml</code> values.286 </p><div class="sect3" id="FUNCTIONS-PRODUCING-XML-IS-DOCUMENT"><div class="titlepage"><div><div><h4 class="title">9.15.2.1. <code class="literal">IS DOCUMENT</code> <a href="#FUNCTIONS-PRODUCING-XML-IS-DOCUMENT" class="id_link">#</a></h4></div></div></div><a id="id-1.5.8.21.6.3.2" class="indexterm"></a><pre class="synopsis">287<code class="type">xml</code> <code class="literal">IS DOCUMENT</code> → <code class="returnvalue">boolean</code>288</pre><p>289 The expression <code class="literal">IS DOCUMENT</code> returns true if the290 argument XML value is a proper XML document, false if it is not291 (that is, it is a content fragment), or null if the argument is292 null. See <a class="xref" href="datatype-xml.html" title="8.13. XML Type">Section 8.13</a> about the difference293 between documents and content fragments.294 </p></div><div class="sect3" id="FUNCTIONS-PRODUCING-XML-IS-NOT-DOCUMENT"><div class="titlepage"><div><div><h4 class="title">9.15.2.2. <code class="literal">IS NOT DOCUMENT</code> <a href="#FUNCTIONS-PRODUCING-XML-IS-NOT-DOCUMENT" class="id_link">#</a></h4></div></div></div><a id="id-1.5.8.21.6.4.2" class="indexterm"></a><pre class="synopsis">295<code class="type">xml</code> <code class="literal">IS NOT DOCUMENT</code> → <code class="returnvalue">boolean</code>296</pre><p>297 The expression <code class="literal">IS NOT DOCUMENT</code> returns false if the298 argument XML value is a proper XML document, true if it is not (that is,299 it is a content fragment), or null if the argument is null.300 </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> <a href="#XML-EXISTS" class="id_link">#</a></h4></div></div></div><a id="id-1.5.8.21.6.5.2" class="indexterm"></a><pre class="synopsis">301<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>302</pre><p>303 The function <code class="function">xmlexists</code> evaluates an XPath 1.0304 expression (the first argument), with the passed XML value as its context305 item. The function returns false if the result of that evaluation306 yields an empty node-set, true if it yields any other value. The307 function returns null if any argument is null. A nonnull value308 passed as the context item must be an XML document, not a content309 fragment or any non-XML value.310 </p><p>311 Example:312 </p><pre class="screen">313SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY VALUE '<towns><town>Toronto</town><town>Ottawa</town></towns>');314 315 xmlexists316------------317 t318(1 row)319</pre><p>320 </p><p>321 The <code class="literal">BY REF</code> and <code class="literal">BY VALUE</code> clauses322 are accepted in <span class="productname">PostgreSQL</span>, but are ignored,323 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>.324 </p><p>325 In the SQL standard, the <code class="function">xmlexists</code> function326 evaluates an expression in the XML Query language,327 but <span class="productname">PostgreSQL</span> allows only an XPath 1.0328 expression, as discussed in329 <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>.330 </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> <a href="#XML-IS-WELL-FORMED" class="id_link">#</a></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">331<code class="function">xml_is_well_formed</code> ( <code class="type">text</code> ) → <code class="returnvalue">boolean</code>332<code class="function">xml_is_well_formed_document</code> ( <code class="type">text</code> ) → <code class="returnvalue">boolean</code>333<code class="function">xml_is_well_formed_content</code> ( <code class="type">text</code> ) → <code class="returnvalue">boolean</code>334</pre><p>335 These functions check whether a <code class="type">text</code> string represents336 well-formed XML, returning a Boolean result.337 <code class="function">xml_is_well_formed_document</code> checks for a well-formed338 document, while <code class="function">xml_is_well_formed_content</code> checks339 for well-formed content. <code class="function">xml_is_well_formed</code> does340 the former if the <a class="xref" href="runtime-config-client.html#GUC-XMLOPTION">xmloption</a> configuration341 parameter is set to <code class="literal">DOCUMENT</code>, or the latter if it is set to342 <code class="literal">CONTENT</code>. This means that343 <code class="function">xml_is_well_formed</code> is useful for seeing whether344 a simple cast to type <code class="type">xml</code> will succeed, whereas the other two345 functions are useful for seeing whether the corresponding variants of346 <code class="function">XMLPARSE</code> will succeed.347 </p><p>348 Examples:349 350</p><pre class="screen">351SET xmloption TO DOCUMENT;352SELECT xml_is_well_formed('<>');353 xml_is_well_formed354--------------------355 f356(1 row)357 358SELECT xml_is_well_formed('<abc/>');359 xml_is_well_formed360--------------------361 t362(1 row)363 364SET xmloption TO CONTENT;365SELECT xml_is_well_formed('abc');366 xml_is_well_formed367--------------------368 t369(1 row)370 371SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>');372 xml_is_well_formed_document373-----------------------------374 t375(1 row)376 377SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>');378 xml_is_well_formed_document379-----------------------------380 f381(1 row)382</pre><p>383 384 The last example shows that the checks include whether385 namespaces are correctly matched.386 </p></div></div><div class="sect2" id="FUNCTIONS-XML-PROCESSING"><div class="titlepage"><div><div><h3 class="title">9.15.3. Processing XML <a href="#FUNCTIONS-XML-PROCESSING" class="id_link">#</a></h3></div></div></div><p>387 To process values of data type <code class="type">xml</code>, PostgreSQL offers388 the functions <code class="function">xpath</code> and389 <code class="function">xpath_exists</code>, which evaluate XPath 1.0390 expressions, and the <code class="function">XMLTABLE</code>391 table function.392 </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> <a href="#FUNCTIONS-XML-PROCESSING-XPATH" class="id_link">#</a></h4></div></div></div><a id="id-1.5.8.21.7.3.2" class="indexterm"></a><pre class="synopsis">393<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>394</pre><p>395 The function <code class="function">xpath</code> evaluates the XPath 1.0396 expression <em class="parameter"><code>xpath</code></em> (given as text)397 against the XML value398 <em class="parameter"><code>xml</code></em>. It returns an array of XML values399 corresponding to the node-set produced by the XPath expression.400 If the XPath expression returns a scalar value rather than a node-set,401 a single-element array is returned.402 </p><p>403 The second argument must be a well formed XML document. In particular,404 it must have a single root node element.405 </p><p>406 The optional third argument of the function is an array of namespace407 mappings. This array should be a two-dimensional <code class="type">text</code> array with408 the length of the second axis being equal to 2 (i.e., it should be an409 array of arrays, each of which consists of exactly 2 elements).410 The first element of each array entry is the namespace name (alias), the411 second the namespace URI. It is not required that aliases provided in412 this array be the same as those being used in the XML document itself (in413 other words, both in the XML document and in the <code class="function">xpath</code>414 function context, aliases are <span class="emphasis"><em>local</em></span>).415 </p><p>416 Example:417</p><pre class="screen">418SELECT xpath('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',419 ARRAY[ARRAY['my', 'http://example.com']]);420 421 xpath422--------423 {test}424(1 row)425</pre><p>426 </p><p>427 To deal with default (anonymous) namespaces, do something like this:428</p><pre class="screen">429SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a>',430 ARRAY[ARRAY['mydefns', 'http://example.com']]);431 432 xpath433--------434 {test}435(1 row)436</pre><p>437 </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> <a href="#FUNCTIONS-XML-PROCESSING-XPATH-EXISTS" class="id_link">#</a></h4></div></div></div><a id="id-1.5.8.21.7.4.2" class="indexterm"></a><pre class="synopsis">438<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>439</pre><p>440 The function <code class="function">xpath_exists</code> is a specialized form441 of the <code class="function">xpath</code> function. Instead of returning the442 individual XML values that satisfy the XPath 1.0 expression, this function443 returns a Boolean indicating whether the query was satisfied or not444 (specifically, whether it produced any value other than an empty node-set).445 This function is equivalent to the <code class="literal">XMLEXISTS</code> predicate,446 except that it also offers support for a namespace mapping argument.447 </p><p>448 Example:449</p><pre class="screen">450SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>',451 ARRAY[ARRAY['my', 'http://example.com']]);452 453 xpath_exists454--------------455 t456(1 row)457</pre><p>458 </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> <a href="#FUNCTIONS-XML-PROCESSING-XMLTABLE" class="id_link">#</a></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">459<code class="function">XMLTABLE</code> (460 [<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>]461 <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>]462 <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>]463 | <code class="literal">FOR ORDINALITY</code> }464 [<span class="optional">, ...</span>]465) → <code class="returnvalue">setof record</code>466</pre><p>467 The <code class="function">xmltable</code> expression produces a table based468 on an XML value, an XPath filter to extract rows, and a469 set of column definitions.470 Although it syntactically resembles a function, it can only appear471 as a table in a query's <code class="literal">FROM</code> clause.472 </p><p>473 The optional <code class="literal">XMLNAMESPACES</code> clause gives a474 comma-separated list of namespace definitions, where475 each <em class="replaceable"><code>namespace_uri</code></em> is a <code class="type">text</code>476 expression and each <em class="replaceable"><code>namespace_name</code></em> is a simple477 identifier. It specifies the XML namespaces used in the document and478 their aliases. A default namespace specification is not currently479 supported.480 </p><p>481 The required <em class="replaceable"><code>row_expression</code></em> argument is an482 XPath 1.0 expression (given as <code class="type">text</code>) that is evaluated,483 passing the XML value <em class="replaceable"><code>document_expression</code></em> as484 its context item, to obtain a set of XML nodes. These nodes are what485 <code class="function">xmltable</code> transforms into output rows. No rows486 will be produced if the <em class="replaceable"><code>document_expression</code></em>487 is null, nor if the <em class="replaceable"><code>row_expression</code></em> produces488 an empty node-set or any value other than a node-set.489 </p><p>490 <em class="replaceable"><code>document_expression</code></em> provides the context491 item for the <em class="replaceable"><code>row_expression</code></em>. It must be a492 well-formed XML document; fragments/forests are not accepted.493 The <code class="literal">BY REF</code> and <code class="literal">BY VALUE</code> clauses494 are accepted but ignored, as discussed in495 <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>.496 </p><p>497 In the SQL standard, the <code class="function">xmltable</code> function498 evaluates expressions in the XML Query language,499 but <span class="productname">PostgreSQL</span> allows only XPath 1.0500 expressions, as discussed in501 <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>.502 </p><p>503 The required <code class="literal">COLUMNS</code> clause specifies the504 column(s) that will be produced in the output table.505 See the syntax summary above for the format.506 A name is required for each column, as is a data type507 (unless <code class="literal">FOR ORDINALITY</code> is specified, in which case508 type <code class="type">integer</code> is implicit). The path, default and509 nullability clauses are optional.510 </p><p>511 A column marked <code class="literal">FOR ORDINALITY</code> will be populated512 with row numbers, starting with 1, in the order of nodes retrieved from513 the <em class="replaceable"><code>row_expression</code></em>'s result node-set.514 At most one column may be marked <code class="literal">FOR ORDINALITY</code>.515 </p><div class="note"><h3 class="title">Note</h3><p>516 XPath 1.0 does not specify an order for nodes in a node-set, so code517 that relies on a particular order of the results will be518 implementation-dependent. Details can be found in519 <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>.520 </p></div><p>521 The <em class="replaceable"><code>column_expression</code></em> for a column is an522 XPath 1.0 expression that is evaluated for each row, with the current523 node from the <em class="replaceable"><code>row_expression</code></em> result as its524 context item, to find the value of the column. If525 no <em class="replaceable"><code>column_expression</code></em> is given, then the526 column name is used as an implicit path.527 </p><p>528 If a column's XPath expression returns a non-XML value (which is limited529 to string, boolean, or double in XPath 1.0) and the column has a530 PostgreSQL type other than <code class="type">xml</code>, the column will be set531 as if by assigning the value's string representation to the PostgreSQL532 type. (If the value is a boolean, its string representation is taken533 to be <code class="literal">1</code> or <code class="literal">0</code> if the output534 column's type category is numeric, otherwise <code class="literal">true</code> or535 <code class="literal">false</code>.)536 </p><p>537 If a column's XPath expression returns a non-empty set of XML nodes538 and the column's PostgreSQL type is <code class="type">xml</code>, the column will539 be assigned the expression result exactly, if it is of document or540 content form.541 <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>542 </p><p>543 A non-XML result assigned to an <code class="type">xml</code> output column produces544 content, a single text node with the string value of the result.545 An XML result assigned to a column of any other type may not have more than546 one node, or an error is raised. If there is exactly one node, the column547 will be set as if by assigning the node's string548 value (as defined for the XPath 1.0 <code class="function">string</code> function)549 to the PostgreSQL type.550 </p><p>551 The string value of an XML element is the concatenation, in document order,552 of all text nodes contained in that element and its descendants. The string553 value of an element with no descendant text nodes is an554 empty string (not <code class="literal">NULL</code>).555 Any <code class="literal">xsi:nil</code> attributes are ignored.556 Note that the whitespace-only <code class="literal">text()</code> node between two non-text557 elements is preserved, and that leading whitespace on a <code class="literal">text()</code>558 node is not flattened.559 The XPath 1.0 <code class="function">string</code> function may be consulted for the560 rules defining the string value of other XML node types and non-XML values.561 </p><p>562 The conversion rules presented here are not exactly those of the SQL563 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>.564 </p><p>565 If the path expression returns an empty node-set566 (typically, when it does not match)567 for a given row, the column will be set to <code class="literal">NULL</code>, unless568 a <em class="replaceable"><code>default_expression</code></em> is specified; then the569 value resulting from evaluating that expression is used.570 </p><p>571 A <em class="replaceable"><code>default_expression</code></em>, rather than being572 evaluated immediately when <code class="function">xmltable</code> is called,573 is evaluated each time a default is needed for the column.574 If the expression qualifies as stable or immutable, the repeat575 evaluation may be skipped.576 This means that you can usefully use volatile functions like577 <code class="function">nextval</code> in578 <em class="replaceable"><code>default_expression</code></em>.579 </p><p>580 Columns may be marked <code class="literal">NOT NULL</code>. If the581 <em class="replaceable"><code>column_expression</code></em> for a <code class="literal">NOT582 NULL</code> column does not match anything and there is583 no <code class="literal">DEFAULT</code> or584 the <em class="replaceable"><code>default_expression</code></em> also evaluates to null,585 an error is reported.586 </p><p>587 Examples:588 </p><pre class="screen">589CREATE TABLE xmldata AS SELECT590xml $$591<ROWS>592 <ROW id="1">593 <COUNTRY_ID>AU</COUNTRY_ID>594 <COUNTRY_NAME>Australia</COUNTRY_NAME>595 </ROW>596 <ROW id="5">597 <COUNTRY_ID>JP</COUNTRY_ID>598 <COUNTRY_NAME>Japan</COUNTRY_NAME>599 <PREMIER_NAME>Shinzo Abe</PREMIER_NAME>600 <SIZE unit="sq_mi">145935</SIZE>601 </ROW>602 <ROW id="6">603 <COUNTRY_ID>SG</COUNTRY_ID>604 <COUNTRY_NAME>Singapore</COUNTRY_NAME>605 <SIZE unit="sq_km">697</SIZE>606 </ROW>607</ROWS>608$$ AS data;609 610SELECT xmltable.*611 FROM xmldata,612 XMLTABLE('//ROWS/ROW'613 PASSING data614 COLUMNS id int PATH '@id',615 ordinality FOR ORDINALITY,616 "COUNTRY_NAME" text,617 country_id text PATH 'COUNTRY_ID',618 size_sq_km float PATH 'SIZE[@unit = "sq_km"]',619 size_other text PATH620 'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',621 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified');622 623 id | ordinality | COUNTRY_NAME | country_id | size_sq_km | size_other | premier_name624----+------------+--------------+------------+------------+--------------+---------------625 1 | 1 | Australia | AU | | | not specified626 5 | 2 | Japan | JP | | 145935 sq_mi | Shinzo Abe627 6 | 3 | Singapore | SG | 697 | | not specified628</pre><p>629 630 The following example shows concatenation of multiple text() nodes,631 usage of the column name as XPath filter, and the treatment of whitespace,632 XML comments and processing instructions:633 634 </p><pre class="screen">635CREATE TABLE xmlelements AS SELECT636xml $$637 <root>638 <element> Hello<!-- xyxxz -->2a2<?aaaaa?> <!--x--> bbb<x>xxx</x>CC </element>639 </root>640$$ AS data;641 642SELECT xmltable.*643 FROM xmlelements, XMLTABLE('/root' PASSING data COLUMNS element text);644 element645-------------------------646 Hello2a2 bbbxxxCC647</pre><p>648 </p><p>649 The following example illustrates how650 the <code class="literal">XMLNAMESPACES</code> clause can be used to specify651 a list of namespaces652 used in the XML document as well as in the XPath expressions:653 654 </p><pre class="screen">655WITH xmldata(data) AS (VALUES ('656<example xmlns="http://example.com/myns" xmlns:B="http://example.com/b">657 <item foo="1" B:bar="2"/>658 <item foo="3" B:bar="4"/>659 <item foo="4" B:bar="5"/>660</example>'::xml)661)662SELECT xmltable.*663 FROM XMLTABLE(XMLNAMESPACES('http://example.com/myns' AS x,664 'http://example.com/b' AS "B"),665 '/x:example/x:item'666 PASSING (SELECT data FROM xmldata)667 COLUMNS foo int PATH '@foo',668 bar int PATH '@B:bar');669 foo | bar670-----+-----671 1 | 2672 3 | 4673 4 | 5674(3 rows)675</pre><p>676 </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 <a href="#FUNCTIONS-XML-MAPPING" class="id_link">#</a></h3></div></div></div><a id="id-1.5.8.21.8.2" class="indexterm"></a><p>677 The following functions map the contents of relational tables to678 XML values. They can be thought of as XML export functionality:679</p><pre class="synopsis">680<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>,681 <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>682<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>,683 <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>684<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>,685 <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>686</pre><p>687 </p><p>688 <code class="function">table_to_xml</code> maps the content of the named689 table, passed as parameter <em class="parameter"><code>table</code></em>. The690 <code class="type">regclass</code> type accepts strings identifying tables using the691 usual notation, including optional schema qualification and692 double quotes (see <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a> for details).693 <code class="function">query_to_xml</code> executes the694 query whose text is passed as parameter695 <em class="parameter"><code>query</code></em> and maps the result set.696 <code class="function">cursor_to_xml</code> fetches the indicated number of697 rows from the cursor specified by the parameter698 <em class="parameter"><code>cursor</code></em>. This variant is recommended if699 large tables have to be mapped, because the result value is built700 up in memory by each function.701 </p><p>702 If <em class="parameter"><code>tableforest</code></em> is false, then the resulting703 XML document looks like this:704</p><pre class="screen">705<tablename>706 <row>707 <columnname1>data</columnname1>708 <columnname2>data</columnname2>709 </row>710 711 <row>712 ...713 </row>714 715 ...716</tablename>717</pre><p>718 719 If <em class="parameter"><code>tableforest</code></em> is true, the result is an720 XML content fragment that looks like this:721</p><pre class="screen">722<tablename>723 <columnname1>data</columnname1>724 <columnname2>data</columnname2>725</tablename>726 727<tablename>728 ...729</tablename>730 731...732</pre><p>733 734 If no table name is available, that is, when mapping a query or a735 cursor, the string <code class="literal">table</code> is used in the first736 format, <code class="literal">row</code> in the second format.737 </p><p>738 The choice between these formats is up to the user. The first739 format is a proper XML document, which will be important in many740 applications. The second format tends to be more useful in the741 <code class="function">cursor_to_xml</code> function if the result values are to be742 reassembled into one document later on. The functions for743 producing XML content discussed above, in particular744 <code class="function">xmlelement</code>, can be used to alter the results745 to taste.746 </p><p>747 The data values are mapped in the same way as described for the748 function <code class="function">xmlelement</code> above.749 </p><p>750 The parameter <em class="parameter"><code>nulls</code></em> determines whether null751 values should be included in the output. If true, null values in752 columns are represented as:753</p><pre class="screen">754<columnname xsi:nil="true"/>755</pre><p>756 where <code class="literal">xsi</code> is the XML namespace prefix for XML757 Schema Instance. An appropriate namespace declaration will be758 added to the result value. If false, columns containing null759 values are simply omitted from the output.760 </p><p>761 The parameter <em class="parameter"><code>targetns</code></em> specifies the762 desired XML namespace of the result. If no particular namespace763 is wanted, an empty string should be passed.764 </p><p>765 The following functions return XML Schema documents describing the766 mappings performed by the corresponding functions above:767</p><pre class="synopsis">768<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>,769 <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>770<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>,771 <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>772<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>,773 <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>774</pre><p>775 It is essential that the same parameters are passed in order to776 obtain matching XML data mappings and XML Schema documents.777 </p><p>778 The following functions produce XML data mappings and the779 corresponding XML Schema in one document (or forest), linked780 together. They can be useful where self-contained and781 self-describing results are wanted:782</p><pre class="synopsis">783<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>,784 <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>785<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>,786 <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>787</pre><p>788 </p><p>789 In addition, the following functions are available to produce790 analogous mappings of entire schemas or the entire current791 database:792</p><pre class="synopsis">793<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>,794 <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>795<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>,796 <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>797<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>,798 <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>799 800<code class="function">database_to_xml</code> ( <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,801 <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>802<code class="function">database_to_xmlschema</code> ( <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,803 <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>804<code class="function">database_to_xml_and_xmlschema</code> ( <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>,805 <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>806</pre><p>807 808 These functions ignore tables that are not readable by the current user.809 The database-wide functions additionally ignore schemas that the current810 user does not have <code class="literal">USAGE</code> (lookup) privilege for.811 </p><p>812 Note that these potentially produce a lot of data, which needs to813 be built up in memory. When requesting content mappings of large814 schemas or databases, it might be worthwhile to consider mapping the815 tables separately instead, possibly even through a cursor.816 </p><p>817 The result of a schema content mapping looks like this:818 819</p><pre class="screen">820<schemaname>821 822table1-mapping823 824table2-mapping825 826...827 828</schemaname></pre><p>829 830 where the format of a table mapping depends on the831 <em class="parameter"><code>tableforest</code></em> parameter as explained above.832 </p><p>833 The result of a database content mapping looks like this:834 835</p><pre class="screen">836<dbname>837 838<schema1name>839 ...840</schema1name>841 842<schema2name>843 ...844</schema2name>845 846...847 848</dbname></pre><p>849 850 where the schema mapping is as above.851 </p><p>852 As an example of using the output produced by these functions,853 <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 that854 converts the output of855 <code class="function">table_to_xml_and_xmlschema</code> to an HTML856 document containing a tabular rendition of the table data. In a857 similar manner, the results from these functions can be858 converted into other XML-based formats.859 </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">860<?xml version="1.0"?>861<xsl:stylesheet version="1.0"862 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"863 xmlns:xsd="http://www.w3.org/2001/XMLSchema"864 xmlns="http://www.w3.org/1999/xhtml"865>866 867 <xsl:output method="xml"868 doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"869 doctype-public="-//W3C/DTD XHTML 1.0 Strict//EN"870 indent="yes"/>871 872 <xsl:template match="/*">873 <xsl:variable name="schema" select="//xsd:schema"/>874 <xsl:variable name="tabletypename"875 select="$schema/xsd:element[@name=name(current())]/@type"/>876 <xsl:variable name="rowtypename"877 select="$schema/xsd:complexType[@name=$tabletypename]/xsd:sequence/xsd:element[@name='row']/@type"/>878 879 <html>880 <head>881 <title><xsl:value-of select="name(current())"/></title>882 </head>883 <body>884 <table>885 <tr>886 <xsl:for-each select="$schema/xsd:complexType[@name=$rowtypename]/xsd:sequence/xsd:element/@name">887 <th><xsl:value-of select="."/></th>888 </xsl:for-each>889 </tr>890 891 <xsl:for-each select="row">892 <tr>893 <xsl:for-each select="*">894 <td><xsl:value-of select="."/></td>895 </xsl:for-each>896 </tr>897 </xsl:for-each>898 </table>899 </body>900 </html>901 </xsl:template>902 903</xsl:stylesheet>904</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>905 A result containing more than one element node at the top level, or906 non-whitespace text outside of an element, is an example of content form.907 An XPath result can be of neither form, for example if it returns an908 attribute node selected from the element that contains it. Such a result909 will be put into content form with each such disallowed node replaced by910 its string value, as defined for the XPath 1.0911 <code class="function">string</code> function.912 </p></div></div></div><div class="navfooter"><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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.16. JSON Functions and Operators</td></tr></table></div></body></html>