XMLSERIALIZE

The XMLSERIALIZE function returns a serialized XML value of the specified data type that is generated from the first argument.

                    .-CONTENT-.                                      
>>-XMLSERIALIZE--(--+---------+--XML-expression--AS--| data-type |-->

   .--------------------------------------.      
   V  (1).-VERSION--'1.0'---------------. |      
>--------+------------------------------+-+--)-----------------><
         | .-EXCLUDING XMLDECLARATION-. |        
         '-+-INCLUDING XMLDECLARATION-+-'

Notes:

The same clause must not be specified more than one time.

data-type

>>-+-+-+-CHARACTER-+--LARGE OBJECT-+-+-------------------------->
   | | '-CHAR------'               | |   
   | '-CLOB------------------------' |   
   +-DBCLOB--------------------------+   
   '-+-BINARY LARGE OBJECT-+---------'   
     '-BLOB----------------'             

   .-(--1M--)-------------.   
>--+----------------------+------------------------------------><
   '-(--integer--+---+--)-'   
                 +-K-+        
                 +-M-+        
                 '-G-'

The schema is SYSIBM.

CONTENT

Specifies that any XML value can be specified and the result of the serialization is based on this input value.

XML-expression

An expression that returns an XML value that is not an attribute node. The atomic values in the input sequence must be able to be cast to xs:string. XML-expression is the input to the serialization process.

AS data type

Specifies the data type of the result. The implicit or explicit length attribute for the specified result data type must be sufficient to contain the serialized output.

The CCSID of a resulting character or graphic string is determined by the data type of the result:

If the result is a CLOB, the CCSID for mixed Unicode data (1208).
If the result is a DBCLOB, the CCSID for graphic Unicode data (1200).

VERSION '1.0'

Specifies the XML version of the serialized value. The only version that is supported is '1.0', which must be specified as a string constant.

EXCLUDING XMLDECLARATION or INCLUDING XMLDECLARATION

Specifies whether an XML declaration is included in the result.

EXCLUDING XMLDECLARATION: Specifies that an XML declaration is not included in the result.
EXCLUDING XMLDECLARATION is the default.
INCLUDING XMLDECLARATION: Specifies that an XML declaration is included in the result. The XML declaration contains values for XML serialization version 1.0 and an encoding specification of UTF-8. An XML sequence is effectively converted to have a single document node by applying the XMLDOCUMENT function to XML-expression prior to serializing the resulting XML nodes.

The data type and length attribute of the result are determined from the specified data-type. The result can be null; if the XML-expression argument is null, the result is the null value.

Serializing a sequence: The value of the input argument to XMLSERIALIZE is a sequence. Before a sequence is serialized, it is normalized. The purpose of sequence normalization is to create a sequence that can be serialized as a well-formed XML document or external general parsed entity, that also reflects the content of the input sequence to the extent possible. If the input sequence is an XML empty string, the result of serialization is an empty string. Otherwise, the result is constructed as follows:

For each item in the sequence, if the item is atomic, the lexical representation of the item is obtained by casting it to an xs:string
Each subsequence of adjacent strings in the sequence is merged into a single string with the values of the adjacent strings separated by a single space.
For each item in the sequence, if the item is a string, a text node is created with a value that is equal to the string.
For each node in the sequence, if the node is a document node, it is replaced it by its children.
Each node must not be an attribute node.
Each subsequence of adjacent text nodes in the sequence are merged into a single text node that with the values of the adjacent text nodes concatenated in order without a space between each node. Any text nodes of zero length are dropped.
A document node is created and the sequence of nodes that was generated is copied as the children of the new document node.

Let S be any sequence, the normalization described in the preceding list is equivalent to XMLDOCUMENT(S). Therefore, the following two expressions produce the same result:

XMLSERIALIZE(S AS CLOB)
XMLSERIALIZE(XMLDOCUMENT(S) AS CLOB)

Each instance of the following characters that appear in the content of a text node or in the value of an attribute node is mapped as following during serialization:

Character in content of text node	during serialization, the character is mapped to
'&' (X'26')	'&'
'<'(X'3C')	'<'
'>'(X'3E')	'>'
carriage return (X'0D')	' '
quote (X'22')¹	'"'
Note: The quote character is only mapped if it is inside of an attribute value.

Syntax alternatives: XML2CLOB(XML-expression) can be specified as an alternative to XMLSERIALIZE(XML-expression AS CLOB(2G)). XML2CLOB is supported only for compatibility with previous releases of DB2®.

Example 1: Serialize into CLOB of UTF-8, the XML value that is returned by the XMLELEMENT function, which is a simple XML element with "Emp" as the element name, and an employee name as the element content:

   SELECT e.id, XMLSERIALIZE(XMLELEMENT ( NAME "Emp",
                       e.fname || ' ' || e.lname)
                AS CLOB(100)) AS "result"
      FROM employees e;

The result looks similar to the following results:

   ID    result
   ---   ----------------------------

   1001  <Emp>John Smith</Emp>
   1206  <Emp>Mary Martin</Emp>

Example 2: Serialize into a string of BLOB type, the XML value that is returned by the XMLELEMENT function:

   SELECT XMLSERIALIZE(XMLELEMENT(NAME "emp",
                         e.fname || ' ' || e.lname))
                  AS BLOB(1K)
                  VERSION '1.0') AS result
    FROM employee e WHERE e.id = '1001';

The result looks similar to the following results:

  result
------------------------

<emp>John Smith</emp>