XMLTEXT

The XMLTEXT function returns an XML value that contains the value of string-expression.

XMLTEXT(string-expression)
string-expression
An expression that returns a value of a built-in character or graphic string. It cannot be CHAR or VARCHAR bit data.

The result of the function is an XML value. If the result of string-expression can be null, the result can be null; if the result of string-expression is null, the result is the null value. If the result of string-expression is an empty string, the result value is empty text.

Example

  • Create a simple XMLTEXT query.
      VALUES (XMLTEXT (
                'The stock symbol for Johnson&Johnson is JNJ.'))

    This query produces the following serialized result:

    The stock symbol for Johnson&Johnson is JNJ.
    Note that the '&' sign is mapped to '&' when the text is serialized.
  • Use XMLTEXT with XMLAGG to construct mixed content. Suppose that the content of table T is as follows:
  • SEQNO  PLAINTEXT                                                     EMPHTEXT
    -----  ----------------------------------------------------------    ----------
    1      This query shows how to construct                             mixed content
    2       using XMLAGG and XMLTEXT. Without                            XMLTEXT
    3      , XMLAGG will not have text nodes to group with other nodes,  mixed content
           therefore, cannot generate             
    SELECT XMLELEMENT(NAME "para", 
                  XMLAGG(XMLCONCAT(
                                     XMLTEXT(PLAINTEXT),
                                     XMLELEMENT(
                                          NAME "emphasis", EMPHTEXT))
                           ORDER BY SEQNO), '.') AS "result"
      FROM T
    This query produces the following result:
    result
    ------------------------------------------------------------------------------
    <para>This query shows how to construct <emphasis>mixed content</emphasis>
    using XMLAGG and XMLTEXT. Without <emphasis>XMLTEXT</emphasis>, XMLAGG
    will not have text nodes to group with other nodes, therefore, cannot generate
    <emphasis>mixed content</emphasis>.</para>