Sophie

Sophie

distrib > Mandriva > current > i586 > media > main-updates > by-pkgid > fc62ce67f262cdcd253dc7f849ce3223 > files > 230

postgresql8.4-docs-8.4.12-0.1mdv2010.2.i586.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>XML Functions</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.4.12 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Functions and Operators"
HREF="functions.html"><LINK
REL="PREVIOUS"
TITLE="Text Search Functions and Operators"
HREF="functions-textsearch.html"><LINK
REL="NEXT"
TITLE="Sequence Manipulation Functions"
HREF="functions-sequence.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2012-05-31T23:30:11"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.4.12 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions-textsearch.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 9. Functions and Operators</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions-sequence.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="FUNCTIONS-XML"
>9.14. XML Functions</A
></H1
><P
>   The functions and function-like expressions described in this
   section operate on values of type <TT
CLASS="TYPE"
>xml</TT
>.  Check <A
HREF="datatype-xml.html"
>Section 8.13</A
> for information about the <TT
CLASS="TYPE"
>xml</TT
>
   type.  The function-like expressions <CODE
CLASS="FUNCTION"
>xmlparse</CODE
>
   and <CODE
CLASS="FUNCTION"
>xmlserialize</CODE
> for converting to and from
   type <TT
CLASS="TYPE"
>xml</TT
> are not repeated here.  Use of many of these
   functions requires the installation to have been built
   with <TT
CLASS="COMMAND"
>configure --with-libxml</TT
>.
  </P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN14815"
>9.14.1. Producing XML Content</A
></H2
><P
>    A set of functions and function-like expressions are 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"
><H3
CLASS="SECT3"
><A
NAME="AEN14818"
>9.14.1.1. <TT
CLASS="LITERAL"
>xmlcomment</TT
></A
></H3
><A
NAME="AEN14821"
></A
><PRE
CLASS="SYNOPSIS"
><CODE
CLASS="FUNCTION"
>xmlcomment</CODE
>(<TT
CLASS="REPLACEABLE"
><I
>text</I
></TT
>)</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"
>"<TT
CLASS="LITERAL"
>--</TT
>"</SPAN
> or end with a
     <SPAN
CLASS="QUOTE"
>"<TT
CLASS="LITERAL"
>-</TT
>"</SPAN
> so that the resulting construct is a valid
     XML comment.  If the argument is null, the result is null.
    </P
><P
>     Example:
</P><PRE
CLASS="SCREEN"
>SELECT xmlcomment('hello');

  xmlcomment
--------------
 &#60;!--hello--&#62;</PRE
><P>
    </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN14834"
>9.14.1.2. <TT
CLASS="LITERAL"
>xmlconcat</TT
></A
></H3
><A
NAME="AEN14837"
></A
><PRE
CLASS="SYNOPSIS"
> <CODE
CLASS="FUNCTION"
>xmlconcat</CODE
>(<TT
CLASS="REPLACEABLE"
><I
>xml</I
></TT
>[<SPAN
CLASS="OPTIONAL"
>, ...</SPAN
>])
 </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('&#60;abc/&#62;', '&#60;bar&#62;foo&#60;/bar&#62;');

      xmlconcat
----------------------
 &#60;abc/&#62;&#60;bar&#62;foo&#60;/bar&#62;</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"
>"yes"</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"
>"no"</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('&#60;?xml version="1.1"?&#62;&#60;foo/&#62;', '&#60;?xml version="1.1" standalone="no"?&#62;&#60;bar/&#62;');

             xmlconcat
-----------------------------------
 &#60;?xml version="1.1"?&#62;&#60;foo/&#62;&#60;bar/&#62;</PRE
><P>
    </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN14852"
>9.14.1.3. <TT
CLASS="LITERAL"
>xmlelement</TT
></A
></H3
><A
NAME="AEN14855"
></A
><PRE
CLASS="SYNOPSIS"
> <CODE
CLASS="FUNCTION"
>xmlelement</CODE
>(name <TT
CLASS="REPLACEABLE"
><I
>name</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>, xmlattributes(<TT
CLASS="REPLACEABLE"
><I
>value</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>AS <TT
CLASS="REPLACEABLE"
><I
>attname</I
></TT
></SPAN
>] [<SPAN
CLASS="OPTIONAL"
>, ... </SPAN
>])</SPAN
>] [<SPAN
CLASS="OPTIONAL"
><TT
CLASS="REPLACEABLE"
><I
>, content, ...</I
></TT
></SPAN
>])
 </PRE
><P
>     The <CODE
CLASS="FUNCTION"
>xmlelement</CODE
> expression produces an XML
     element with the given name, attributes, and content.
    </P
><P
>     Examples:
</P><PRE
CLASS="SCREEN"
>SELECT xmlelement(name foo);

 xmlelement
------------
 &#60;foo/&#62;

SELECT xmlelement(name foo, xmlattributes('xyz' as bar));

    xmlelement
------------------
 &#60;foo bar="xyz"/&#62;

SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent');

             xmlelement
-------------------------------------
 &#60;foo bar="2007-01-26"&#62;content&#60;/foo&#62;</PRE
><P>
    </P
><P
>     Element and attribute names that are not valid XML names are
     escaped by replacing the offending characters by the sequence
     <TT
CLASS="LITERAL"
>_x<TT
CLASS="REPLACEABLE"
><I
>HHHH</I
></TT
>_</TT
>, where
     <TT
CLASS="REPLACEABLE"
><I
>HHHH</I
></TT
> is the character's Unicode
     codepoint in hexadecimal notation.  For example:
</P><PRE
CLASS="SCREEN"
>SELECT xmlelement(name "foo$bar", xmlattributes('xyz' as "a&#38;b"));

            xmlelement
----------------------------------
 &#60;foo_x0024_bar a_x0026_b="xyz"/&#62;</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 <TT
CLASS="TYPE"
>xml</TT
>,
     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
----------------------------------------------
 &#60;foo bar="xyz"&#62;&#60;abc/&#62;&#60;!--test--&#62;&#60;xyz/&#62;&#60;/foo&#62;</PRE
><P>

     Content of other types will be formatted into valid XML character
     data.  This means in particular that the characters &lt;, &gt;,
     and &amp; will be converted to entities.  Binary data (data type
     <TT
CLASS="TYPE"
>bytea</TT
>) will be represented in base64 or hex
     encoding, depending on the setting of the configuration parameter
     <A
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
     SQL and PostgreSQL data types with the XML Schema specification,
     at which point a more precise description will appear.
    </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN14884"
>9.14.1.4. <TT
CLASS="LITERAL"
>xmlforest</TT
></A
></H3
><A
NAME="AEN14887"
></A
><PRE
CLASS="SYNOPSIS"
> <CODE
CLASS="FUNCTION"
>xmlforest</CODE
>(<TT
CLASS="REPLACEABLE"
><I
>content</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>AS <TT
CLASS="REPLACEABLE"
><I
>name</I
></TT
></SPAN
>] [<SPAN
CLASS="OPTIONAL"
>, ...</SPAN
>])
 </PRE
><P
>     The <CODE
CLASS="FUNCTION"
>xmlforest</CODE
> expression produces an XML
     forest (sequence) of elements using the given names and content.
    </P
><P
>     Examples:
</P><PRE
CLASS="SCREEN"
>SELECT xmlforest('abc' AS foo, 123 AS bar);

          xmlforest
------------------------------
 &#60;foo&#62;abc&#60;/foo&#62;&#60;bar&#62;123&#60;/bar&#62;


SELECT xmlforest(table_name, column_name)
FROM information_schema.columns
WHERE table_schema = 'pg_catalog';

                                         xmlforest
-------------------------------------------------------------------------------------------
 &#60;table_name&#62;pg_authid&#60;/table_name&#62;&#60;column_name&#62;rolname&#60;/column_name&#62;
 &#60;table_name&#62;pg_authid&#60;/table_name&#62;&#60;column_name&#62;rolsuper&#60;/column_name&#62;
 ...</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 <TT
CLASS="TYPE"
>xml</TT
>.
    </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"
><H3
CLASS="SECT3"
><A
NAME="AEN14905"
>9.14.1.5. <TT
CLASS="LITERAL"
>xmlpi</TT
></A
></H3
><A
NAME="AEN14908"
></A
><PRE
CLASS="SYNOPSIS"
> <CODE
CLASS="FUNCTION"
>xmlpi</CODE
>(name <TT
CLASS="REPLACEABLE"
><I
>target</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>, <TT
CLASS="REPLACEABLE"
><I
>content</I
></TT
></SPAN
>])
 </PRE
><P
>     The <CODE
CLASS="FUNCTION"
>xmlpi</CODE
> expression creates an XML
     processing instruction.  The content, if present, must not
     contain the character sequence <TT
CLASS="LITERAL"
>?&gt;</TT
>.
    </P
><P
>     Example:
</P><PRE
CLASS="SCREEN"
>SELECT xmlpi(name php, 'echo "hello world";');

            xmlpi
-----------------------------
 &#60;?php echo "hello world";?&#62;</PRE
><P>
    </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN14920"
>9.14.1.6. <TT
CLASS="LITERAL"
>xmlroot</TT
></A
></H3
><A
NAME="AEN14923"
></A
><PRE
CLASS="SYNOPSIS"
> <CODE
CLASS="FUNCTION"
>xmlroot</CODE
>(<TT
CLASS="REPLACEABLE"
><I
>xml</I
></TT
>, version <TT
CLASS="REPLACEABLE"
><I
>text</I
></TT
> | no value [<SPAN
CLASS="OPTIONAL"
>, standalone yes|no|no value</SPAN
>])
 </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 '&#60;?xml version="1.1"?&#62;&#60;content&#62;abc&#60;/content&#62;'), 
               version '1.0', standalone yes);

                xmlroot
----------------------------------------
 &#60;?xml version="1.0" standalone="yes"?&#62;
 &#60;content&#62;abc&#60;/content&#62;</PRE
><P>
    </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="FUNCTIONS-XML-XMLAGG"
>9.14.1.7. <TT
CLASS="LITERAL"
>xmlagg</TT
></A
></H3
><A
NAME="AEN14937"
></A
><PRE
CLASS="SYNOPSIS"
><CODE
CLASS="FUNCTION"
>xmlagg</CODE
>(<TT
CLASS="REPLACEABLE"
><I
>xml</I
></TT
>)</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,
     like <CODE
CLASS="FUNCTION"
>xmlconcat</CODE
> does.
     See <A
HREF="functions-aggregate.html"
>Section 9.18</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, '&#60;foo&#62;abc&#60;/foo&#62;');
INSERT INTO test VALUES (2, '&#60;bar/&#62;');
SELECT xmlagg(x) FROM test;
        xmlagg
----------------------
 &#60;foo&#62;abc&#60;/foo&#62;&#60;bar/&#62;</PRE
><P>
    </P
><P
>     To determine the order of the concatenation, something like the
     following approach can be used:

</P><PRE
CLASS="SCREEN"
>SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y DESC) AS tab;
        xmlagg
----------------------
 &#60;bar/&#62;&#60;foo&#62;abc&#60;/foo&#62;</PRE
><P>

     Again, see <A
HREF="functions-aggregate.html"
>Section 9.18</A
> for additional
     information.
    </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="AEN14951"
>9.14.1.8. XML Predicates</A
></H3
><A
NAME="AEN14953"
></A
><PRE
CLASS="SYNOPSIS"
><TT
CLASS="REPLACEABLE"
><I
>xml</I
></TT
> IS DOCUMENT</PRE
><P
>     The expression <TT
CLASS="LITERAL"
>IS DOCUMENT</TT
> 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
HREF="datatype-xml.html"
>Section 8.13</A
> about the difference
     between documents and content fragments.
    </P
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="FUNCTIONS-XML-PROCESSING"
>9.14.2. Processing XML</A
></H2
><A
NAME="AEN14962"
></A
><P
>    To process values of data type <TT
CLASS="TYPE"
>xml</TT
>, PostgreSQL offers
    the function <CODE
CLASS="FUNCTION"
>xpath</CODE
>, which evaluates XPath 1.0
    expressions.
   </P
><PRE
CLASS="SYNOPSIS"
><CODE
CLASS="FUNCTION"
>xpath</CODE
>(<TT
CLASS="REPLACEABLE"
><I
>xpath</I
></TT
>, <TT
CLASS="REPLACEABLE"
><I
>xml</I
></TT
>[<SPAN
CLASS="OPTIONAL"
>, <TT
CLASS="REPLACEABLE"
><I
>nsarray</I
></TT
></SPAN
>])</PRE
><P
>    The function <CODE
CLASS="FUNCTION"
>xpath</CODE
> evaluates the XPath
    expression <TT
CLASS="REPLACEABLE"
><I
>xpath</I
></TT
> against the XML value
    <TT
CLASS="REPLACEABLE"
><I
>xml</I
></TT
>.  It returns an array of XML values
    corresponding to the node set produced by the XPath expression.
   </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 third argument of the function is an array of namespace
    mappings.  This array should be a two-dimensional 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, the
    second the namespace URI.
   </P
><P
>    Example:
</P><PRE
CLASS="SCREEN"
>SELECT xpath('/my:a/text()', '&#60;my:a xmlns:my="http://example.com"&#62;test&#60;/my:a&#62;', 
             ARRAY[ARRAY['my', 'http://example.com']]);

 xpath  
--------
 {test}
(1 row)</PRE
><P>
   </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="FUNCTIONS-XML-MAPPING"
>9.14.3. Mapping Tables to XML</A
></H2
><A
NAME="AEN14983"
></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"
>table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xml(query text, nulls boolean, tableforest boolean, targetns text)
cursor_to_xml(cursor refcursor, count int, nulls boolean, 
              tableforest boolean, targetns text)</PRE
><P>
    The return type of each function is <TT
CLASS="TYPE"
>xml</TT
>.
   </P
><P
>    <CODE
CLASS="FUNCTION"
>table_to_xml</CODE
> maps the content of the named
    table, passed as parameter <TT
CLASS="PARAMETER"
>tbl</TT
>.  The
    <TT
CLASS="TYPE"
>regclass</TT
> type accepts strings identifying tables using the
    usual notation, including optional schema qualifications and
    double quotes.  <CODE
CLASS="FUNCTION"
>query_to_xml</CODE
> executes the
    query whose text is passed as parameter
    <TT
CLASS="PARAMETER"
>query</TT
> 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
    <TT
CLASS="PARAMETER"
>cursor</TT
>.  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 <TT
CLASS="PARAMETER"
>tableforest</TT
> is false, then the resulting
    XML document looks like this:
</P><PRE
CLASS="SCREEN"
>&#60;tablename&#62;
  &#60;row&#62;
    &#60;columnname1&#62;data&#60;/columnname1&#62;
    &#60;columnname2&#62;data&#60;/columnname2&#62;
  &#60;/row&#62;

  &#60;row&#62;
    ...
  &#60;/row&#62;

  ...
&#60;/tablename&#62;</PRE
><P>

    If <TT
CLASS="PARAMETER"
>tableforest</TT
> is true, the result is an
    XML content fragment that looks like this:
</P><PRE
CLASS="SCREEN"
>&#60;tablename&#62;
  &#60;columnname1&#62;data&#60;/columnname1&#62;
  &#60;columnname2&#62;data&#60;/columnname2&#62;
&#60;/tablename&#62;

&#60;tablename&#62;
  ...
&#60;/tablename&#62;

...</PRE
><P>

    If no table name is available, that is, when mapping a query or a
    cursor, the string <TT
CLASS="LITERAL"
>table</TT
> is used in the first
    format, <TT
CLASS="LITERAL"
>row</TT
> 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 <TT
CLASS="PARAMETER"
>nulls</TT
> determines whether null
    values should be included in the output.  If true, null values in
    columns are represented as:
</P><PRE
CLASS="SCREEN"
>&#60;columnname xsi:nil="true"/&#62;</PRE
><P>
    where <TT
CLASS="LITERAL"
>xsi</TT
> 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 <TT
CLASS="PARAMETER"
>targetns</TT
> 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"
>table_to_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)
cursor_to_xmlschema(cursor refcursor, nulls boolean, tableforest boolean, targetns text)</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"
>table_to_xml_and_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text)
query_to_xml_and_xmlschema(query text, nulls boolean, tableforest boolean, targetns text)</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"
>schema_to_xml(schema name, nulls boolean, tableforest boolean, targetns text)
schema_to_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)
schema_to_xml_and_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text)

database_to_xml(nulls boolean, tableforest boolean, targetns text)
database_to_xmlschema(nulls boolean, tableforest boolean, targetns text)
database_to_xml_and_xmlschema(nulls boolean, tableforest boolean, targetns text)</PRE
><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"
>&#60;schemaname&#62;

table1-mapping

table2-mapping

...

&#60;/schemaname&#62;</PRE
><P>

    where the format of a table mapping depends on the
    <TT
CLASS="PARAMETER"
>tableforest</TT
> parameter as explained above.
   </P
><P
>    The result of a database content mapping looks like this:

</P><PRE
CLASS="SCREEN"
>&#60;dbname&#62;

&#60;schema1name&#62;
  ...
&#60;/schema1name&#62;

&#60;schema2name&#62;
  ...
&#60;/schema2name&#62;

...

&#60;/dbname&#62;</PRE
><P>

    where the schema mapping is as above.
   </P
><P
>    As an example of using the output produced by these functions,
    <A
HREF="functions-xml.html#XSLT-XML-HTML"
>Figure 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="FIGURE"
><A
NAME="XSLT-XML-HTML"
></A
><P
><B
>Figure 9-1. XSLT stylesheet for converting SQL/XML output to HTML</B
></P
><PRE
CLASS="PROGRAMLISTING"
>&#60;?xml version="1.0"?&#62;
&#60;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"
&#62;

  &#60;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"/&#62;

  &#60;xsl:template match="/*"&#62;
    &#60;xsl:variable name="schema" select="//xsd:schema"/&#62;
    &#60;xsl:variable name="tabletypename"
                  select="$schema/xsd:element[@name=name(current())]/@type"/&#62;
    &#60;xsl:variable name="rowtypename"
                  select="$schema/xsd:complexType[@name=$tabletypename]/xsd:sequence/xsd:element[@name='row']/@type"/&#62;

    &#60;html&#62;
      &#60;head&#62;
        &#60;title&#62;&#60;xsl:value-of select="name(current())"/&#62;&#60;/title&#62;
      &#60;/head&#62;
      &#60;body&#62;
        &#60;table&#62;
          &#60;tr&#62;
            &#60;xsl:for-each select="$schema/xsd:complexType[@name=$rowtypename]/xsd:sequence/xsd:element/@name"&#62;
              &#60;th&#62;&#60;xsl:value-of select="."/&#62;&#60;/th&#62;
            &#60;/xsl:for-each&#62;
          &#60;/tr&#62;

          &#60;xsl:for-each select="row"&#62;
            &#60;tr&#62;
              &#60;xsl:for-each select="*"&#62;
                &#60;td&#62;&#60;xsl:value-of select="."/&#62;&#60;/td&#62;
              &#60;/xsl:for-each&#62;
            &#60;/tr&#62;
          &#60;/xsl:for-each&#62;
        &#60;/table&#62;
      &#60;/body&#62;
    &#60;/html&#62;
  &#60;/xsl:template&#62;

&#60;/xsl:stylesheet&#62;</PRE
></DIV
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions-textsearch.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions-sequence.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Text Search Functions and Operators</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="functions.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Sequence Manipulation Functions</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>

Perl :: Catalyst :: PostgreSQL :: RPM Valid HTML 4.01 Transitional