XMLTEXT scalar function

The XMLTEXT function returns an XML value with a single text node that contains the value of the argument.

The schema is SYSIBM.

string-expression: An expression that returns a value of a built-in character or graphic string that is not bit data. Any character in the resulting string must be a valid XML 1.0 character when it is converted to UTF-8.
If string-expression is an empty string, an empty text node is returned.

The result of the function is an XML value.

The result can be null; if the argument is null, the result is the null value.

Example 1: The following example returns an XML value with a single text node that contains the specified value:

  SELECT XMLTEXT('The stock symbol for Johnson&Johnson is JNJ.') AS "Result"
     FROM SYSIBM.SYSDUMMY1;

The result looks similar to the following results:

Result
---------------------------------------------------

The stock symbol for Johnson&amp;Johnson is JNJ.

Example 2: The XMLTEXT function enables the XMLAGG function to construct mixed content, as in the following example:

 SELECT XMLELEMENT(NAME "para",
         XMLAGG(XMLCONCAT( XMLTEXT( plaintext),
                            XMLELEMENT( NAME "emphasis",
                            emphtext ))
                  ORDER BY seqno ), '.' ) as "result"
    FROM T;

Suppose that the content of the table T is as the following example:

seqno    plaintext                                          emphtext
-----    -------------------------------------------------  ----------------
1        This query shows how to construct                  mixed content
2        using XMLAGG and XMLTEXT. Without                  XMLTEXT
3        XMLAGG cannot group text nodes with other nodes,   mixed content
         therefore, cannot generate

The result looks like the following result:

result
--------------------------------------------------------------------------
<para>This query shows how to construct <emphasis>mixed content</emphasis>
using XMLAGG and XMLTEXT. Without <emphasis>XMLTEXT</emphasis>, XMLAGG
cannot group text nodes with other nodes, therefore, cannot generate
<emphasis>mixed content</emphasis>.</para>