diff options
Diffstat (limited to 'doc/src/sgml/html/functions-xml.html')
| -rw-r--r-- | doc/src/sgml/html/functions-xml.html | 912 |
1 files changed, 912 insertions, 0 deletions
diff --git a/doc/src/sgml/html/functions-xml.html b/doc/src/sgml/html/functions-xml.html new file mode 100644 index 0000000..793ae33 --- /dev/null +++ b/doc/src/sgml/html/functions-xml.html @@ -0,0 +1,912 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.15. XML Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-uuid.html" title="9.14. UUID Functions" /><link rel="next" href="functions-json.html" title="9.16. JSON Functions and Operators" /></head><body id="docContent" class="container-fluid col-10"><div 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.2 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> + The functions and function-like expressions described in this + section operate on values of type <code class="type">xml</code>. See <a class="xref" href="datatype-xml.html" title="8.13. XML Type">Section 8.13</a> for information about the <code class="type">xml</code> + type. The function-like expressions <code class="function">xmlparse</code> + and <code class="function">xmlserialize</code> for converting to and from + type <code class="type">xml</code> are documented there, not in this section. + </p><p> + Use of most of these functions + requires <span class="productname">PostgreSQL</span> to have been built + with <code class="command">configure --with-libxml</code>. + </p><div class="sect2" id="FUNCTIONS-PRODUCING-XML"><div class="titlepage"><div><div><h3 class="title">9.15.1. Producing XML Content <a href="#FUNCTIONS-PRODUCING-XML" class="id_link">#</a></h3></div></div></div><p> + A set of functions and function-like expressions is available for + producing XML content from SQL data. As such, they are + particularly suitable for formatting query results into XML + documents for processing in client applications. + </p><div class="sect3" id="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"> +<code class="function">xmlcomment</code> ( <code class="type">text</code> ) → <code class="returnvalue">xml</code> +</pre><p> + The function <code class="function">xmlcomment</code> creates an XML value + containing an XML comment with the specified text as content. + The text cannot contain <span class="quote">“<span class="quote"><code class="literal">--</code></span>”</span> or end with a + <span class="quote">“<span class="quote"><code class="literal">-</code></span>”</span>, otherwise the resulting construct + would not be a valid XML comment. + If the argument is null, the result is null. + </p><p> + Example: +</p><pre class="screen"> +SELECT xmlcomment('hello'); + + xmlcomment +-------------- + <!--hello--> +</pre><p> + </p></div><div class="sect3" id="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"> +<code class="function">xmlconcat</code> ( <code class="type">xml</code> [<span class="optional">, ...</span>] ) → <code class="returnvalue">xml</code> +</pre><p> + The function <code class="function">xmlconcat</code> concatenates a list + of individual XML values to create a single value containing an + XML content fragment. Null values are omitted; the result is + only null if there are no nonnull arguments. + </p><p> + Example: +</p><pre class="screen"> +SELECT xmlconcat('<abc/>', '<bar>foo</bar>'); + + xmlconcat +---------------------- + <abc/><bar>foo</bar> +</pre><p> + </p><p> + XML declarations, if present, are combined as follows. If all + argument values have the same XML version declaration, that + version is used in the result, else no version is used. If all + argument values have the standalone declaration value + <span class="quote">“<span class="quote">yes</span>”</span>, then that value is used in the result. If + all argument values have a standalone declaration value and at + least one is <span class="quote">“<span class="quote">no</span>”</span>, then that is used in the result. + Else the result will have no standalone declaration. If the + result is determined to require a standalone declaration but no + version declaration, a version declaration with version 1.0 will + be used because XML requires an XML declaration to contain a + version declaration. Encoding declarations are ignored and + removed in all cases. + </p><p> + Example: +</p><pre class="screen"> +SELECT xmlconcat('<?xml version="1.1"?><foo/>', '<?xml version="1.1" standalone="no"?><bar/>'); + + xmlconcat +----------------------------------- + <?xml version="1.1"?><foo/><bar/> +</pre><p> + </p></div><div class="sect3" id="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"> +<code class="function">xmlelement</code> ( <code class="literal">NAME</code> <em class="replaceable"><code>name</code></em> [<span class="optional">, <code class="literal">XMLATTRIBUTES</code> ( <em class="replaceable"><code>attvalue</code></em> [<span class="optional"> <code class="literal">AS</code> <em class="replaceable"><code>attname</code></em> </span>] [<span class="optional">, ...</span>] ) </span>] [<span class="optional">, <em class="replaceable"><code>content</code></em> [<span class="optional">, ...</span>]</span>] ) → <code class="returnvalue">xml</code> +</pre><p> + The <code class="function">xmlelement</code> expression produces an XML + element with the given name, attributes, and content. + The <em class="replaceable"><code>name</code></em> + and <em class="replaceable"><code>attname</code></em> items shown in the syntax are + simple identifiers, not values. The <em class="replaceable"><code>attvalue</code></em> + and <em class="replaceable"><code>content</code></em> items are expressions, which can + yield any <span class="productname">PostgreSQL</span> data type. The + argument(s) within <code class="literal">XMLATTRIBUTES</code> generate attributes + of the XML element; the <em class="replaceable"><code>content</code></em> value(s) are + concatenated to form its content. + </p><p> + Examples: +</p><pre class="screen"> +SELECT xmlelement(name foo); + + xmlelement +------------ + <foo/> + +SELECT xmlelement(name foo, xmlattributes('xyz' as bar)); + + xmlelement +------------------ + <foo bar="xyz"/> + +SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent'); + + xmlelement +------------------------------------- + <foo bar="2007-01-26">content</foo> +</pre><p> + </p><p> + Element and attribute names that are not valid XML names are + escaped by replacing the offending characters by the sequence + <code class="literal">_x<em class="replaceable"><code>HHHH</code></em>_</code>, where + <em class="replaceable"><code>HHHH</code></em> is the character's Unicode + codepoint in hexadecimal notation. For example: +</p><pre class="screen"> +SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&b")); + + xmlelement +---------------------------------- + <foo_x0024_bar a_x0026_b="xyz"/> +</pre><p> + </p><p> + An explicit attribute name need not be specified if the attribute + value is a column reference, in which case the column's name will + be used as the attribute name by default. In other cases, the + attribute must be given an explicit name. So this example is + valid: +</p><pre class="screen"> +CREATE TABLE test (a xml, b xml); +SELECT xmlelement(name test, xmlattributes(a, b)) FROM test; +</pre><p> + But these are not: +</p><pre class="screen"> +SELECT xmlelement(name test, xmlattributes('constant'), a, b) FROM test; +SELECT xmlelement(name test, xmlattributes(func(a, b))) FROM test; +</pre><p> + </p><p> + Element content, if specified, will be formatted according to + its data type. If the content is itself of type <code class="type">xml</code>, + complex XML documents can be constructed. For example: +</p><pre class="screen"> +SELECT xmlelement(name foo, xmlattributes('xyz' as bar), + xmlelement(name abc), + xmlcomment('test'), + xmlelement(name xyz)); + + xmlelement +---------------------------------------------- + <foo bar="xyz"><abc/><!--test--><xyz/></foo> +</pre><p> + + Content of other types will be formatted into valid XML character + data. This means in particular that the characters <, >, + and & will be converted to entities. Binary data (data type + <code class="type">bytea</code>) will be represented in base64 or hex + encoding, depending on the setting of the configuration parameter + <a class="xref" href="runtime-config-client.html#GUC-XMLBINARY">xmlbinary</a>. The particular behavior for + individual data types is expected to evolve in order to align the + PostgreSQL mappings with those specified in SQL:2006 and later, + as discussed in <a class="xref" href="xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-CASTS" title="D.3.1.3. Mappings between SQL and XML Data Types and Values">Section D.3.1.3</a>. + </p></div><div class="sect3" id="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"> +<code class="function">xmlforest</code> ( <em class="replaceable"><code>content</code></em> [<span class="optional"> <code class="literal">AS</code> <em class="replaceable"><code>name</code></em> </span>] [<span class="optional">, ...</span>] ) → <code class="returnvalue">xml</code> +</pre><p> + The <code class="function">xmlforest</code> expression produces an XML + forest (sequence) of elements using the given names and content. + As for <code class="function">xmlelement</code>, + each <em class="replaceable"><code>name</code></em> must be a simple identifier, while + the <em class="replaceable"><code>content</code></em> expressions can have any data + type. + </p><p> + Examples: +</p><pre class="screen"> +SELECT xmlforest('abc' AS foo, 123 AS bar); + + xmlforest +------------------------------ + <foo>abc</foo><bar>123</bar> + + +SELECT xmlforest(table_name, column_name) +FROM information_schema.columns +WHERE table_schema = 'pg_catalog'; + + xmlforest +----------------------------------------------------------------------- + <table_name>pg_authid</table_name><column_name>rolname</column_name> + <table_name>pg_authid</table_name><column_name>rolsuper</column_name> + ... +</pre><p> + + As seen in the second example, the element name can be omitted if + the content value is a column reference, in which case the column + name is used by default. Otherwise, a name must be specified. + </p><p> + Element names that are not valid XML names are escaped as shown + for <code class="function">xmlelement</code> above. Similarly, content + data is escaped to make valid XML content, unless it is already + of type <code class="type">xml</code>. + </p><p> + Note that XML forests are not valid XML documents if they consist + of more than one element, so it might be useful to wrap + <code class="function">xmlforest</code> expressions in + <code class="function">xmlelement</code>. + </p></div><div class="sect3" id="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"> +<code class="function">xmlpi</code> ( <code class="literal">NAME</code> <em class="replaceable"><code>name</code></em> [<span class="optional">, <em class="replaceable"><code>content</code></em> </span>] ) → <code class="returnvalue">xml</code> +</pre><p> + The <code class="function">xmlpi</code> expression creates an XML + processing instruction. + As for <code class="function">xmlelement</code>, + the <em class="replaceable"><code>name</code></em> must be a simple identifier, while + the <em class="replaceable"><code>content</code></em> expression can have any data type. + The <em class="replaceable"><code>content</code></em>, if present, must not contain the + character sequence <code class="literal">?></code>. + </p><p> + Example: +</p><pre class="screen"> +SELECT xmlpi(name php, 'echo "hello world";'); + + xmlpi +----------------------------- + <?php echo "hello world";?> +</pre><p> + </p></div><div class="sect3" id="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"> +<code class="function">xmlroot</code> ( <code class="type">xml</code>, <code class="literal">VERSION</code> {<code class="type">text</code>|<code class="literal">NO VALUE</code>} [<span class="optional">, <code class="literal">STANDALONE</code> {<code class="literal">YES</code>|<code class="literal">NO</code>|<code class="literal">NO VALUE</code>} </span>] ) → <code class="returnvalue">xml</code> +</pre><p> + The <code class="function">xmlroot</code> expression alters the properties + of the root node of an XML value. If a version is specified, + it replaces the value in the root node's version declaration; if a + standalone setting is specified, it replaces the value in the + root node's standalone declaration. + </p><p> +</p><pre class="screen"> +SELECT xmlroot(xmlparse(document '<?xml version="1.1"?><content>abc</content>'), + version '1.0', standalone yes); + + xmlroot +---------------------------------------- + <?xml version="1.0" standalone="yes"?> + <content>abc</content> +</pre><p> + </p></div><div class="sect3" id="FUNCTIONS-XML-XMLAGG"><div class="titlepage"><div><div><h4 class="title">9.15.1.7. <code class="literal">xmlagg</code> <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"> +<code class="function">xmlagg</code> ( <code class="type">xml</code> ) → <code class="returnvalue">xml</code> +</pre><p> + The function <code class="function">xmlagg</code> is, unlike the other + functions described here, an aggregate function. It concatenates the + input values to the aggregate function call, + much like <code class="function">xmlconcat</code> does, except that concatenation + occurs across rows rather than across expressions in a single row. + See <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a> for additional information + about aggregate functions. + </p><p> + Example: +</p><pre class="screen"> +CREATE TABLE test (y int, x xml); +INSERT INTO test VALUES (1, '<foo>abc</foo>'); +INSERT INTO test VALUES (2, '<bar/>'); +SELECT xmlagg(x) FROM test; + xmlagg +---------------------- + <foo>abc</foo><bar/> +</pre><p> + </p><p> + To determine the order of the concatenation, an <code class="literal">ORDER BY</code> + clause may be added to the aggregate call as described in + <a class="xref" href="sql-expressions.html#SYNTAX-AGGREGATES" title="4.2.7. Aggregate Expressions">Section 4.2.7</a>. For example: + +</p><pre class="screen"> +SELECT xmlagg(x ORDER BY y DESC) FROM test; + xmlagg +---------------------- + <bar/><foo>abc</foo> +</pre><p> + </p><p> + The following non-standard approach used to be recommended + in previous versions, and may still be useful in specific + cases: + +</p><pre class="screen"> +SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab; + xmlagg +---------------------- + <bar/><foo>abc</foo> +</pre><p> + </p></div></div><div class="sect2" id="FUNCTIONS-XML-PREDICATES"><div class="titlepage"><div><div><h3 class="title">9.15.2. XML Predicates <a href="#FUNCTIONS-XML-PREDICATES" class="id_link">#</a></h3></div></div></div><p> + The expressions described in this section check properties + of <code class="type">xml</code> values. + </p><div class="sect3" id="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"> +<code class="type">xml</code> <code class="literal">IS DOCUMENT</code> → <code class="returnvalue">boolean</code> +</pre><p> + The expression <code class="literal">IS DOCUMENT</code> returns true if the + argument XML value is a proper XML document, false if it is not + (that is, it is a content fragment), or null if the argument is + null. See <a class="xref" href="datatype-xml.html" title="8.13. XML Type">Section 8.13</a> about the difference + between documents and content fragments. + </p></div><div class="sect3" id="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"> +<code class="type">xml</code> <code class="literal">IS NOT DOCUMENT</code> → <code class="returnvalue">boolean</code> +</pre><p> + The expression <code class="literal">IS NOT DOCUMENT</code> returns false if the + argument XML value is a proper XML document, true if it is not (that is, + it is a content fragment), or null if the argument is null. + </p></div><div class="sect3" id="XML-EXISTS"><div class="titlepage"><div><div><h4 class="title">9.15.2.3. <code class="literal">XMLEXISTS</code> <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"> +<code class="function">XMLEXISTS</code> ( <code class="type">text</code> <code class="literal">PASSING</code> [<span class="optional"><code class="literal">BY</code> {<code class="literal">REF</code>|<code class="literal">VALUE</code>}</span>] <code class="type">xml</code> [<span class="optional"><code class="literal">BY</code> {<code class="literal">REF</code>|<code class="literal">VALUE</code>}</span>] ) → <code class="returnvalue">boolean</code> +</pre><p> + The function <code class="function">xmlexists</code> evaluates an XPath 1.0 + expression (the first argument), with the passed XML value as its context + item. The function returns false if the result of that evaluation + yields an empty node-set, true if it yields any other value. The + function returns null if any argument is null. A nonnull value + passed as the context item must be an XML document, not a content + fragment or any non-XML value. + </p><p> + Example: + </p><pre class="screen"> +SELECT xmlexists('//town[text() = ''Toronto'']' PASSING BY VALUE '<towns><town>Toronto</town><town>Ottawa</town></towns>'); + + xmlexists +------------ + t +(1 row) +</pre><p> + </p><p> + The <code class="literal">BY REF</code> and <code class="literal">BY VALUE</code> clauses + are accepted in <span class="productname">PostgreSQL</span>, but are ignored, + as discussed in <a class="xref" href="xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-POSTGRESQL" title="D.3.2. Incidental Limits of the Implementation">Section D.3.2</a>. + </p><p> + In the SQL standard, the <code class="function">xmlexists</code> function + evaluates an expression in the XML Query language, + but <span class="productname">PostgreSQL</span> allows only an XPath 1.0 + expression, as discussed in + <a class="xref" href="xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-XPATH1" title="D.3.1. Queries Are Restricted to XPath 1.0">Section D.3.1</a>. + </p></div><div class="sect3" id="XML-IS-WELL-FORMED"><div class="titlepage"><div><div><h4 class="title">9.15.2.4. <code class="literal">xml_is_well_formed</code> <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"> +<code class="function">xml_is_well_formed</code> ( <code class="type">text</code> ) → <code class="returnvalue">boolean</code> +<code class="function">xml_is_well_formed_document</code> ( <code class="type">text</code> ) → <code class="returnvalue">boolean</code> +<code class="function">xml_is_well_formed_content</code> ( <code class="type">text</code> ) → <code class="returnvalue">boolean</code> +</pre><p> + These functions check whether a <code class="type">text</code> string represents + well-formed XML, returning a Boolean result. + <code class="function">xml_is_well_formed_document</code> checks for a well-formed + document, while <code class="function">xml_is_well_formed_content</code> checks + for well-formed content. <code class="function">xml_is_well_formed</code> does + the former if the <a class="xref" href="runtime-config-client.html#GUC-XMLOPTION">xmloption</a> configuration + parameter is set to <code class="literal">DOCUMENT</code>, or the latter if it is set to + <code class="literal">CONTENT</code>. This means that + <code class="function">xml_is_well_formed</code> is useful for seeing whether + a simple cast to type <code class="type">xml</code> will succeed, whereas the other two + functions are useful for seeing whether the corresponding variants of + <code class="function">XMLPARSE</code> will succeed. + </p><p> + Examples: + +</p><pre class="screen"> +SET xmloption TO DOCUMENT; +SELECT xml_is_well_formed('<>'); + xml_is_well_formed +-------------------- + f +(1 row) + +SELECT xml_is_well_formed('<abc/>'); + xml_is_well_formed +-------------------- + t +(1 row) + +SET xmloption TO CONTENT; +SELECT xml_is_well_formed('abc'); + xml_is_well_formed +-------------------- + t +(1 row) + +SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</pg:foo>'); + xml_is_well_formed_document +----------------------------- + t +(1 row) + +SELECT xml_is_well_formed_document('<pg:foo xmlns:pg="http://postgresql.org/stuff">bar</my:foo>'); + xml_is_well_formed_document +----------------------------- + f +(1 row) +</pre><p> + + The last example shows that the checks include whether + namespaces are correctly matched. + </p></div></div><div class="sect2" id="FUNCTIONS-XML-PROCESSING"><div class="titlepage"><div><div><h3 class="title">9.15.3. Processing XML <a href="#FUNCTIONS-XML-PROCESSING" class="id_link">#</a></h3></div></div></div><p> + To process values of data type <code class="type">xml</code>, PostgreSQL offers + the functions <code class="function">xpath</code> and + <code class="function">xpath_exists</code>, which evaluate XPath 1.0 + expressions, and the <code class="function">XMLTABLE</code> + table function. + </p><div class="sect3" id="FUNCTIONS-XML-PROCESSING-XPATH"><div class="titlepage"><div><div><h4 class="title">9.15.3.1. <code class="literal">xpath</code> <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"> +<code class="function">xpath</code> ( <em class="parameter"><code>xpath</code></em> <code class="type">text</code>, <em class="parameter"><code>xml</code></em> <code class="type">xml</code> [<span class="optional">, <em class="parameter"><code>nsarray</code></em> <code class="type">text[]</code> </span>] ) → <code class="returnvalue">xml[]</code> +</pre><p> + The function <code class="function">xpath</code> evaluates the XPath 1.0 + expression <em class="parameter"><code>xpath</code></em> (given as text) + against the XML value + <em class="parameter"><code>xml</code></em>. It returns an array of XML values + corresponding to the node-set produced by the XPath expression. + If the XPath expression returns a scalar value rather than a node-set, + a single-element array is returned. + </p><p> + The second argument must be a well formed XML document. In particular, + it must have a single root node element. + </p><p> + The optional third argument of the function is an array of namespace + mappings. This array should be a two-dimensional <code class="type">text</code> array with + the length of the second axis being equal to 2 (i.e., it should be an + array of arrays, each of which consists of exactly 2 elements). + The first element of each array entry is the namespace name (alias), the + second the namespace URI. It is not required that aliases provided in + this array be the same as those being used in the XML document itself (in + other words, both in the XML document and in the <code class="function">xpath</code> + function context, aliases are <span class="emphasis"><em>local</em></span>). + </p><p> + Example: +</p><pre class="screen"> +SELECT xpath('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>', + ARRAY[ARRAY['my', 'http://example.com']]); + + xpath +-------- + {test} +(1 row) +</pre><p> + </p><p> + To deal with default (anonymous) namespaces, do something like this: +</p><pre class="screen"> +SELECT xpath('//mydefns:b/text()', '<a xmlns="http://example.com"><b>test</b></a>', + ARRAY[ARRAY['mydefns', 'http://example.com']]); + + xpath +-------- + {test} +(1 row) +</pre><p> + </p></div><div class="sect3" id="FUNCTIONS-XML-PROCESSING-XPATH-EXISTS"><div class="titlepage"><div><div><h4 class="title">9.15.3.2. <code class="literal">xpath_exists</code> <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"> +<code class="function">xpath_exists</code> ( <em class="parameter"><code>xpath</code></em> <code class="type">text</code>, <em class="parameter"><code>xml</code></em> <code class="type">xml</code> [<span class="optional">, <em class="parameter"><code>nsarray</code></em> <code class="type">text[]</code> </span>] ) → <code class="returnvalue">boolean</code> +</pre><p> + The function <code class="function">xpath_exists</code> is a specialized form + of the <code class="function">xpath</code> function. Instead of returning the + individual XML values that satisfy the XPath 1.0 expression, this function + returns a Boolean indicating whether the query was satisfied or not + (specifically, whether it produced any value other than an empty node-set). + This function is equivalent to the <code class="literal">XMLEXISTS</code> predicate, + except that it also offers support for a namespace mapping argument. + </p><p> + Example: +</p><pre class="screen"> +SELECT xpath_exists('/my:a/text()', '<my:a xmlns:my="http://example.com">test</my:a>', + ARRAY[ARRAY['my', 'http://example.com']]); + + xpath_exists +-------------- + t +(1 row) +</pre><p> + </p></div><div class="sect3" id="FUNCTIONS-XML-PROCESSING-XMLTABLE"><div class="titlepage"><div><div><h4 class="title">9.15.3.3. <code class="literal">xmltable</code> <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"> +<code class="function">XMLTABLE</code> ( + [<span class="optional"> <code class="literal">XMLNAMESPACES</code> ( <em class="replaceable"><code>namespace_uri</code></em> <code class="literal">AS</code> <em class="replaceable"><code>namespace_name</code></em> [<span class="optional">, ...</span>] ), </span>] + <em class="replaceable"><code>row_expression</code></em> <code class="literal">PASSING</code> [<span class="optional"><code class="literal">BY</code> {<code class="literal">REF</code>|<code class="literal">VALUE</code>}</span>] <em class="replaceable"><code>document_expression</code></em> [<span class="optional"><code class="literal">BY</code> {<code class="literal">REF</code>|<code class="literal">VALUE</code>}</span>] + <code class="literal">COLUMNS</code> <em class="replaceable"><code>name</code></em> { <em class="replaceable"><code>type</code></em> [<span class="optional"><code class="literal">PATH</code> <em class="replaceable"><code>column_expression</code></em></span>] [<span class="optional"><code class="literal">DEFAULT</code> <em class="replaceable"><code>default_expression</code></em></span>] [<span class="optional"><code class="literal">NOT NULL</code> | <code class="literal">NULL</code></span>] + | <code class="literal">FOR ORDINALITY</code> } + [<span class="optional">, ...</span>] +) → <code class="returnvalue">setof record</code> +</pre><p> + The <code class="function">xmltable</code> expression produces a table based + on an XML value, an XPath filter to extract rows, and a + set of column definitions. + Although it syntactically resembles a function, it can only appear + as a table in a query's <code class="literal">FROM</code> clause. + </p><p> + The optional <code class="literal">XMLNAMESPACES</code> clause gives a + comma-separated list of namespace definitions, where + each <em class="replaceable"><code>namespace_uri</code></em> is a <code class="type">text</code> + expression and each <em class="replaceable"><code>namespace_name</code></em> is a simple + identifier. It specifies the XML namespaces used in the document and + their aliases. A default namespace specification is not currently + supported. + </p><p> + The required <em class="replaceable"><code>row_expression</code></em> argument is an + XPath 1.0 expression (given as <code class="type">text</code>) that is evaluated, + passing the XML value <em class="replaceable"><code>document_expression</code></em> as + its context item, to obtain a set of XML nodes. These nodes are what + <code class="function">xmltable</code> transforms into output rows. No rows + will be produced if the <em class="replaceable"><code>document_expression</code></em> + is null, nor if the <em class="replaceable"><code>row_expression</code></em> produces + an empty node-set or any value other than a node-set. + </p><p> + <em class="replaceable"><code>document_expression</code></em> provides the context + item for the <em class="replaceable"><code>row_expression</code></em>. It must be a + well-formed XML document; fragments/forests are not accepted. + The <code class="literal">BY REF</code> and <code class="literal">BY VALUE</code> clauses + are accepted but ignored, as discussed in + <a class="xref" href="xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-POSTGRESQL" title="D.3.2. Incidental Limits of the Implementation">Section D.3.2</a>. + </p><p> + In the SQL standard, the <code class="function">xmltable</code> function + evaluates expressions in the XML Query language, + but <span class="productname">PostgreSQL</span> allows only XPath 1.0 + expressions, as discussed in + <a class="xref" href="xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-XPATH1" title="D.3.1. Queries Are Restricted to XPath 1.0">Section D.3.1</a>. + </p><p> + The required <code class="literal">COLUMNS</code> clause specifies the + column(s) that will be produced in the output table. + See the syntax summary above for the format. + A name is required for each column, as is a data type + (unless <code class="literal">FOR ORDINALITY</code> is specified, in which case + type <code class="type">integer</code> is implicit). The path, default and + nullability clauses are optional. + </p><p> + A column marked <code class="literal">FOR ORDINALITY</code> will be populated + with row numbers, starting with 1, in the order of nodes retrieved from + the <em class="replaceable"><code>row_expression</code></em>'s result node-set. + At most one column may be marked <code class="literal">FOR ORDINALITY</code>. + </p><div class="note"><h3 class="title">Note</h3><p> + XPath 1.0 does not specify an order for nodes in a node-set, so code + that relies on a particular order of the results will be + implementation-dependent. Details can be found in + <a class="xref" href="xml-limits-conformance.html#XML-XPATH-1-SPECIFICS" title="D.3.1.2. Restriction of XPath to 1.0">Section D.3.1.2</a>. + </p></div><p> + The <em class="replaceable"><code>column_expression</code></em> for a column is an + XPath 1.0 expression that is evaluated for each row, with the current + node from the <em class="replaceable"><code>row_expression</code></em> result as its + context item, to find the value of the column. If + no <em class="replaceable"><code>column_expression</code></em> is given, then the + column name is used as an implicit path. + </p><p> + If a column's XPath expression returns a non-XML value (which is limited + to string, boolean, or double in XPath 1.0) and the column has a + PostgreSQL type other than <code class="type">xml</code>, the column will be set + as if by assigning the value's string representation to the PostgreSQL + type. (If the value is a boolean, its string representation is taken + to be <code class="literal">1</code> or <code class="literal">0</code> if the output + column's type category is numeric, otherwise <code class="literal">true</code> or + <code class="literal">false</code>.) + </p><p> + If a column's XPath expression returns a non-empty set of XML nodes + and the column's PostgreSQL type is <code class="type">xml</code>, the column will + be assigned the expression result exactly, if it is of document or + content form. + <a href="#ftn.id-1.5.8.21.7.5.15.2" class="footnote"><sup class="footnote" id="id-1.5.8.21.7.5.15.2">[8]</sup></a> + </p><p> + A non-XML result assigned to an <code class="type">xml</code> output column produces + content, a single text node with the string value of the result. + An XML result assigned to a column of any other type may not have more than + one node, or an error is raised. If there is exactly one node, the column + will be set as if by assigning the node's string + value (as defined for the XPath 1.0 <code class="function">string</code> function) + to the PostgreSQL type. + </p><p> + The string value of an XML element is the concatenation, in document order, + of all text nodes contained in that element and its descendants. The string + value of an element with no descendant text nodes is an + empty string (not <code class="literal">NULL</code>). + Any <code class="literal">xsi:nil</code> attributes are ignored. + Note that the whitespace-only <code class="literal">text()</code> node between two non-text + elements is preserved, and that leading whitespace on a <code class="literal">text()</code> + node is not flattened. + The XPath 1.0 <code class="function">string</code> function may be consulted for the + rules defining the string value of other XML node types and non-XML values. + </p><p> + The conversion rules presented here are not exactly those of the SQL + standard, as discussed in <a class="xref" href="xml-limits-conformance.html#FUNCTIONS-XML-LIMITS-CASTS" title="D.3.1.3. Mappings between SQL and XML Data Types and Values">Section D.3.1.3</a>. + </p><p> + If the path expression returns an empty node-set + (typically, when it does not match) + for a given row, the column will be set to <code class="literal">NULL</code>, unless + a <em class="replaceable"><code>default_expression</code></em> is specified; then the + value resulting from evaluating that expression is used. + </p><p> + A <em class="replaceable"><code>default_expression</code></em>, rather than being + evaluated immediately when <code class="function">xmltable</code> is called, + is evaluated each time a default is needed for the column. + If the expression qualifies as stable or immutable, the repeat + evaluation may be skipped. + This means that you can usefully use volatile functions like + <code class="function">nextval</code> in + <em class="replaceable"><code>default_expression</code></em>. + </p><p> + Columns may be marked <code class="literal">NOT NULL</code>. If the + <em class="replaceable"><code>column_expression</code></em> for a <code class="literal">NOT + NULL</code> column does not match anything and there is + no <code class="literal">DEFAULT</code> or + the <em class="replaceable"><code>default_expression</code></em> also evaluates to null, + an error is reported. + </p><p> + Examples: + </p><pre class="screen"> +CREATE TABLE xmldata AS SELECT +xml $$ +<ROWS> + <ROW id="1"> + <COUNTRY_ID>AU</COUNTRY_ID> + <COUNTRY_NAME>Australia</COUNTRY_NAME> + </ROW> + <ROW id="5"> + <COUNTRY_ID>JP</COUNTRY_ID> + <COUNTRY_NAME>Japan</COUNTRY_NAME> + <PREMIER_NAME>Shinzo Abe</PREMIER_NAME> + <SIZE unit="sq_mi">145935</SIZE> + </ROW> + <ROW id="6"> + <COUNTRY_ID>SG</COUNTRY_ID> + <COUNTRY_NAME>Singapore</COUNTRY_NAME> + <SIZE unit="sq_km">697</SIZE> + </ROW> +</ROWS> +$$ AS data; + +SELECT xmltable.* + FROM xmldata, + XMLTABLE('//ROWS/ROW' + PASSING data + COLUMNS id int PATH '@id', + ordinality FOR ORDINALITY, + "COUNTRY_NAME" text, + country_id text PATH 'COUNTRY_ID', + size_sq_km float PATH 'SIZE[@unit = "sq_km"]', + size_other text PATH + 'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)', + premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'); + + id | ordinality | COUNTRY_NAME | country_id | size_sq_km | size_other | premier_name +----+------------+--------------+------------+------------+--------------+--------------- + 1 | 1 | Australia | AU | | | not specified + 5 | 2 | Japan | JP | | 145935 sq_mi | Shinzo Abe + 6 | 3 | Singapore | SG | 697 | | not specified +</pre><p> + + The following example shows concatenation of multiple text() nodes, + usage of the column name as XPath filter, and the treatment of whitespace, + XML comments and processing instructions: + + </p><pre class="screen"> +CREATE TABLE xmlelements AS SELECT +xml $$ + <root> + <element> Hello<!-- xyxxz -->2a2<?aaaaa?> <!--x--> bbb<x>xxx</x>CC </element> + </root> +$$ AS data; + +SELECT xmltable.* + FROM xmlelements, XMLTABLE('/root' PASSING data COLUMNS element text); + element +------------------------- + Hello2a2 bbbxxxCC +</pre><p> + </p><p> + The following example illustrates how + the <code class="literal">XMLNAMESPACES</code> clause can be used to specify + a list of namespaces + used in the XML document as well as in the XPath expressions: + + </p><pre class="screen"> +WITH xmldata(data) AS (VALUES (' +<example xmlns="http://example.com/myns" xmlns:B="http://example.com/b"> + <item foo="1" B:bar="2"/> + <item foo="3" B:bar="4"/> + <item foo="4" B:bar="5"/> +</example>'::xml) +) +SELECT xmltable.* + FROM XMLTABLE(XMLNAMESPACES('http://example.com/myns' AS x, + 'http://example.com/b' AS "B"), + '/x:example/x:item' + PASSING (SELECT data FROM xmldata) + COLUMNS foo int PATH '@foo', + bar int PATH '@B:bar'); + foo | bar +-----+----- + 1 | 2 + 3 | 4 + 4 | 5 +(3 rows) +</pre><p> + </p></div></div><div class="sect2" id="FUNCTIONS-XML-MAPPING"><div class="titlepage"><div><div><h3 class="title">9.15.4. Mapping Tables to XML <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> + The following functions map the contents of relational tables to + XML values. They can be thought of as XML export functionality: +</p><pre class="synopsis"> +<code class="function">table_to_xml</code> ( <em class="parameter"><code>table</code></em> <code class="type">regclass</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> +<code class="function">query_to_xml</code> ( <em class="parameter"><code>query</code></em> <code class="type">text</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> +<code class="function">cursor_to_xml</code> ( <em class="parameter"><code>cursor</code></em> <code class="type">refcursor</code>, <em class="parameter"><code>count</code></em> <code class="type">integer</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> +</pre><p> + </p><p> + <code class="function">table_to_xml</code> maps the content of the named + table, passed as parameter <em class="parameter"><code>table</code></em>. The + <code class="type">regclass</code> type accepts strings identifying tables using the + usual notation, including optional schema qualification and + double quotes (see <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a> for details). + <code class="function">query_to_xml</code> executes the + query whose text is passed as parameter + <em class="parameter"><code>query</code></em> and maps the result set. + <code class="function">cursor_to_xml</code> fetches the indicated number of + rows from the cursor specified by the parameter + <em class="parameter"><code>cursor</code></em>. This variant is recommended if + large tables have to be mapped, because the result value is built + up in memory by each function. + </p><p> + If <em class="parameter"><code>tableforest</code></em> is false, then the resulting + XML document looks like this: +</p><pre class="screen"> +<tablename> + <row> + <columnname1>data</columnname1> + <columnname2>data</columnname2> + </row> + + <row> + ... + </row> + + ... +</tablename> +</pre><p> + + If <em class="parameter"><code>tableforest</code></em> is true, the result is an + XML content fragment that looks like this: +</p><pre class="screen"> +<tablename> + <columnname1>data</columnname1> + <columnname2>data</columnname2> +</tablename> + +<tablename> + ... +</tablename> + +... +</pre><p> + + If no table name is available, that is, when mapping a query or a + cursor, the string <code class="literal">table</code> is used in the first + format, <code class="literal">row</code> in the second format. + </p><p> + The choice between these formats is up to the user. The first + format is a proper XML document, which will be important in many + applications. The second format tends to be more useful in the + <code class="function">cursor_to_xml</code> function if the result values are to be + reassembled into one document later on. The functions for + producing XML content discussed above, in particular + <code class="function">xmlelement</code>, can be used to alter the results + to taste. + </p><p> + The data values are mapped in the same way as described for the + function <code class="function">xmlelement</code> above. + </p><p> + The parameter <em class="parameter"><code>nulls</code></em> determines whether null + values should be included in the output. If true, null values in + columns are represented as: +</p><pre class="screen"> +<columnname xsi:nil="true"/> +</pre><p> + where <code class="literal">xsi</code> is the XML namespace prefix for XML + Schema Instance. An appropriate namespace declaration will be + added to the result value. If false, columns containing null + values are simply omitted from the output. + </p><p> + The parameter <em class="parameter"><code>targetns</code></em> specifies the + desired XML namespace of the result. If no particular namespace + is wanted, an empty string should be passed. + </p><p> + The following functions return XML Schema documents describing the + mappings performed by the corresponding functions above: +</p><pre class="synopsis"> +<code class="function">table_to_xmlschema</code> ( <em class="parameter"><code>table</code></em> <code class="type">regclass</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> +<code class="function">query_to_xmlschema</code> ( <em class="parameter"><code>query</code></em> <code class="type">text</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> +<code class="function">cursor_to_xmlschema</code> ( <em class="parameter"><code>cursor</code></em> <code class="type">refcursor</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> +</pre><p> + It is essential that the same parameters are passed in order to + obtain matching XML data mappings and XML Schema documents. + </p><p> + The following functions produce XML data mappings and the + corresponding XML Schema in one document (or forest), linked + together. They can be useful where self-contained and + self-describing results are wanted: +</p><pre class="synopsis"> +<code class="function">table_to_xml_and_xmlschema</code> ( <em class="parameter"><code>table</code></em> <code class="type">regclass</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> +<code class="function">query_to_xml_and_xmlschema</code> ( <em class="parameter"><code>query</code></em> <code class="type">text</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> +</pre><p> + </p><p> + In addition, the following functions are available to produce + analogous mappings of entire schemas or the entire current + database: +</p><pre class="synopsis"> +<code class="function">schema_to_xml</code> ( <em class="parameter"><code>schema</code></em> <code class="type">name</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> +<code class="function">schema_to_xmlschema</code> ( <em class="parameter"><code>schema</code></em> <code class="type">name</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> +<code class="function">schema_to_xml_and_xmlschema</code> ( <em class="parameter"><code>schema</code></em> <code class="type">name</code>, <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> + +<code class="function">database_to_xml</code> ( <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> +<code class="function">database_to_xmlschema</code> ( <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> +<code class="function">database_to_xml_and_xmlschema</code> ( <em class="parameter"><code>nulls</code></em> <code class="type">boolean</code>, + <em class="parameter"><code>tableforest</code></em> <code class="type">boolean</code>, <em class="parameter"><code>targetns</code></em> <code class="type">text</code> ) → <code class="returnvalue">xml</code> +</pre><p> + + These functions ignore tables that are not readable by the current user. + The database-wide functions additionally ignore schemas that the current + user does not have <code class="literal">USAGE</code> (lookup) privilege for. + </p><p> + Note that these potentially produce a lot of data, which needs to + be built up in memory. When requesting content mappings of large + schemas or databases, it might be worthwhile to consider mapping the + tables separately instead, possibly even through a cursor. + </p><p> + The result of a schema content mapping looks like this: + +</p><pre class="screen"> +<schemaname> + +table1-mapping + +table2-mapping + +... + +</schemaname></pre><p> + + where the format of a table mapping depends on the + <em class="parameter"><code>tableforest</code></em> parameter as explained above. + </p><p> + The result of a database content mapping looks like this: + +</p><pre class="screen"> +<dbname> + +<schema1name> + ... +</schema1name> + +<schema2name> + ... +</schema2name> + +... + +</dbname></pre><p> + + where the schema mapping is as above. + </p><p> + As an example of using the output produced by these functions, + <a class="xref" href="functions-xml.html#XSLT-XML-HTML" title="Example 9.1. XSLT Stylesheet for Converting SQL/XML Output to HTML">Example 9.1</a> shows an XSLT stylesheet that + converts the output of + <code class="function">table_to_xml_and_xmlschema</code> to an HTML + document containing a tabular rendition of the table data. In a + similar manner, the results from these functions can be + converted into other XML-based formats. + </p><div class="example" id="XSLT-XML-HTML"><p class="title"><strong>Example 9.1. XSLT Stylesheet for Converting SQL/XML Output to HTML</strong></p><div class="example-contents"><pre class="programlisting"> +<?xml version="1.0"?> +<xsl:stylesheet version="1.0" + xmlns:xsl="http://www.w3.org/1999/XSL/Transform" + xmlns:xsd="http://www.w3.org/2001/XMLSchema" + xmlns="http://www.w3.org/1999/xhtml" +> + + <xsl:output method="xml" + doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd" + doctype-public="-//W3C/DTD XHTML 1.0 Strict//EN" + indent="yes"/> + + <xsl:template match="/*"> + <xsl:variable name="schema" select="//xsd:schema"/> + <xsl:variable name="tabletypename" + select="$schema/xsd:element[@name=name(current())]/@type"/> + <xsl:variable name="rowtypename" + select="$schema/xsd:complexType[@name=$tabletypename]/xsd:sequence/xsd:element[@name='row']/@type"/> + + <html> + <head> + <title><xsl:value-of select="name(current())"/></title> + </head> + <body> + <table> + <tr> + <xsl:for-each select="$schema/xsd:complexType[@name=$rowtypename]/xsd:sequence/xsd:element/@name"> + <th><xsl:value-of select="."/></th> + </xsl:for-each> + </tr> + + <xsl:for-each select="row"> + <tr> + <xsl:for-each select="*"> + <td><xsl:value-of select="."/></td> + </xsl:for-each> + </tr> + </xsl:for-each> + </table> + </body> + </html> + </xsl:template> + +</xsl:stylesheet> +</pre></div></div><br class="example-break" /></div><div class="footnotes"><br /><hr style="width:100; text-align:left;margin-left: 0" /><div id="ftn.id-1.5.8.21.7.5.15.2" class="footnote"><p><a href="#id-1.5.8.21.7.5.15.2" class="para"><sup class="para">[8] </sup></a> + A result containing more than one element node at the top level, or + non-whitespace text outside of an element, is an example of content form. + An XPath result can be of neither form, for example if it returns an + attribute node selected from the element that contains it. Such a result + will be put into content form with each such disallowed node replaced by + its string value, as defined for the XPath 1.0 + <code class="function">string</code> function. + </p></div></div></div><div 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.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.16. JSON Functions and Operators</td></tr></table></div></body></html>
\ No newline at end of file |