Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
functions-xml.html912 linesDownload Raw Back to html
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 &lt;!--hello--&gt;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('&lt;abc/&gt;', '&lt;bar&gt;foo&lt;/bar&gt;');46 47      xmlconcat48----------------------49 &lt;abc/&gt;&lt;bar&gt;foo&lt;/bar&gt;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('&lt;?xml version="1.1"?&gt;&lt;foo/&gt;', '&lt;?xml version="1.1" standalone="no"?&gt;&lt;bar/&gt;');69 70             xmlconcat71-----------------------------------72 &lt;?xml version="1.1"?&gt;&lt;foo/&gt;&lt;bar/&gt;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 &lt;foo/&gt;95 96SELECT xmlelement(name foo, xmlattributes('xyz' as bar));97 98    xmlelement99------------------100 &lt;foo bar="xyz"/&gt;101 102SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent');103 104             xmlelement105-------------------------------------106 &lt;foo bar="2007-01-26"&gt;content&lt;/foo&gt;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&amp;b"));116 117            xmlelement118----------------------------------119 &lt;foo_x0024_bar a_x0026_b="xyz"/&gt;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 &lt;foo bar="xyz"&gt;&lt;abc/&gt;&lt;!--test--&gt;&lt;xyz/&gt;&lt;/foo&gt;149</pre><p>150 151     Content of other types will be formatted into valid XML character152     data.  This means in particular that the characters &lt;, &gt;,153     and &amp; 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 &lt;foo&gt;abc&lt;/foo&gt;&lt;bar&gt;123&lt;/bar&gt;177 178 179SELECT xmlforest(table_name, column_name)180FROM information_schema.columns181WHERE table_schema = 'pg_catalog';182 183                                xmlforest184------------------------------------​-----------------------------------185 &lt;table_name&gt;pg_authid&lt;/table_name&gt;​&lt;column_name&gt;rolname&lt;/column_name&gt;186 &lt;table_name&gt;pg_authid&lt;/table_name&gt;​&lt;column_name&gt;rolsuper&lt;/column_name&gt;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">?&gt;</code>.213    </p><p>214     Example:215</p><pre class="screen">216SELECT xmlpi(name php, 'echo "hello world";');217 218            xmlpi219-----------------------------220 &lt;?php echo "hello world";?&gt;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 '&lt;?xml version="1.1"?&gt;&lt;content&gt;abc&lt;/content&gt;'),233               version '1.0', standalone yes);234 235                xmlroot236----------------------------------------237 &lt;?xml version="1.0" standalone="yes"?&gt;238 &lt;content&gt;abc&lt;/content&gt;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, '&lt;foo&gt;abc&lt;/foo&gt;');255INSERT INTO test VALUES (2, '&lt;bar/&gt;');256SELECT xmlagg(x) FROM test;257        xmlagg258----------------------259 &lt;foo&gt;abc&lt;/foo&gt;&lt;bar/&gt;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 &lt;bar/&gt;&lt;foo&gt;abc&lt;/foo&gt;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 &lt;bar/&gt;&lt;foo&gt;abc&lt;/foo&gt;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 '&lt;towns&gt;&lt;town&gt;Toronto&lt;/town&gt;&lt;town&gt;Ottawa&lt;/town&gt;&lt;/towns&gt;');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('&lt;&gt;');353 xml_is_well_formed354--------------------355 f356(1 row)357 358SELECT xml_is_well_formed('&lt;abc/&gt;');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('&lt;pg:foo xmlns:pg="http://postgresql.org/stuff"&gt;bar&lt;/pg:foo&gt;');372 xml_is_well_formed_document373-----------------------------374 t375(1 row)376 377SELECT xml_is_well_formed_document('&lt;pg:foo xmlns:pg="http://postgresql.org/stuff"&gt;bar&lt;/my:foo&gt;');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()', '&lt;my:a xmlns:my="http://example.com"&gt;test&lt;/my:a&gt;',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()', '&lt;a xmlns="http://example.com"&gt;&lt;b&gt;test&lt;/b&gt;&lt;/a&gt;',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()', '&lt;my:a xmlns:my="http://example.com"&gt;test&lt;/my:a&gt;',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&lt;ROWS&gt;592  &lt;ROW id="1"&gt;593    &lt;COUNTRY_ID&gt;AU&lt;/COUNTRY_ID&gt;594    &lt;COUNTRY_NAME&gt;Australia&lt;/COUNTRY_NAME&gt;595  &lt;/ROW&gt;596  &lt;ROW id="5"&gt;597    &lt;COUNTRY_ID&gt;JP&lt;/COUNTRY_ID&gt;598    &lt;COUNTRY_NAME&gt;Japan&lt;/COUNTRY_NAME&gt;599    &lt;PREMIER_NAME&gt;Shinzo Abe&lt;/PREMIER_NAME&gt;600    &lt;SIZE unit="sq_mi"&gt;145935&lt;/SIZE&gt;601  &lt;/ROW&gt;602  &lt;ROW id="6"&gt;603    &lt;COUNTRY_ID&gt;SG&lt;/COUNTRY_ID&gt;604    &lt;COUNTRY_NAME&gt;Singapore&lt;/COUNTRY_NAME&gt;605    &lt;SIZE unit="sq_km"&gt;697&lt;/SIZE&gt;606  &lt;/ROW&gt;607&lt;/ROWS&gt;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  &lt;root&gt;638   &lt;element&gt;  Hello&lt;!-- xyxxz --&gt;2a2&lt;?aaaaa?&gt; &lt;!--x--&gt;  bbb&lt;x&gt;xxx&lt;/x&gt;CC  &lt;/element&gt;639  &lt;/root&gt;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&lt;example xmlns="http://example.com/myns" xmlns:B="http://example.com/b"&gt;657 &lt;item foo="1" B:bar="2"/&gt;658 &lt;item foo="3" B:bar="4"/&gt;659 &lt;item foo="4" B:bar="5"/&gt;660&lt;/example&gt;'::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&lt;tablename&gt;706  &lt;row&gt;707    &lt;columnname1&gt;data&lt;/columnname1&gt;708    &lt;columnname2&gt;data&lt;/columnname2&gt;709  &lt;/row&gt;710 711  &lt;row&gt;712    ...713  &lt;/row&gt;714 715  ...716&lt;/tablename&gt;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&lt;tablename&gt;723  &lt;columnname1&gt;data&lt;/columnname1&gt;724  &lt;columnname2&gt;data&lt;/columnname2&gt;725&lt;/tablename&gt;726 727&lt;tablename&gt;728  ...729&lt;/tablename&gt;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&lt;columnname xsi:nil="true"/&gt;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&lt;schemaname&gt;821 822table1-mapping823 824table2-mapping825 826...827 828&lt;/schemaname&gt;</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&lt;dbname&gt;837 838&lt;schema1name&gt;839  ...840&lt;/schema1name&gt;841 842&lt;schema2name&gt;843  ...844&lt;/schema2name&gt;845 846...847 848&lt;/dbname&gt;</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&lt;?xml version="1.0"?&gt;861&lt;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&gt;866 867  &lt;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"/&gt;871 872  &lt;xsl:template match="/*"&gt;873    &lt;xsl:variable name="schema" select="//xsd:schema"/&gt;874    &lt;xsl:variable name="tabletypename"875                  select="$schema/xsd:element[@name=name(current())]/@type"/&gt;876    &lt;xsl:variable name="rowtypename"877                  select="$schema/xsd:complexType[@name=$tabletypename]/xsd:sequence/xsd:element[@name='row']/@type"/&gt;878 879    &lt;html&gt;880      &lt;head&gt;881        &lt;title&gt;&lt;xsl:value-of select="name(current())"/&gt;&lt;/title&gt;882      &lt;/head&gt;883      &lt;body&gt;884        &lt;table&gt;885          &lt;tr&gt;886            &lt;xsl:for-each select="$schema/xsd:complexType[@name=$rowtypename]/xsd:sequence/xsd:element/@name"&gt;887              &lt;th&gt;&lt;xsl:value-of select="."/&gt;&lt;/th&gt;888            &lt;/xsl:for-each&gt;889          &lt;/tr&gt;890 891          &lt;xsl:for-each select="row"&gt;892            &lt;tr&gt;893              &lt;xsl:for-each select="*"&gt;894                &lt;td&gt;&lt;xsl:value-of select="."/&gt;&lt;/td&gt;895              &lt;/xsl:for-each&gt;896            &lt;/tr&gt;897          &lt;/xsl:for-each&gt;898        &lt;/table&gt;899      &lt;/body&gt;900    &lt;/html&gt;901  &lt;/xsl:template&gt;902 903&lt;/xsl:stylesheet&gt;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>
codekingpro/portable-devtools · Team Ai