XMLSERIALIZE scalar function

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

Read syntax diagramSkip visual syntax diagramXMLSERIALIZE(CONTENTXML-expressionAS data-typeVERSION'1.0'1EXCLUDING XMLDECLARATIONINCLUDING XMLDECLARATION)
Notes:
  • 1 The same clause must not be specified more than one time.

data-type

Read syntax diagramSkip visual syntax diagramCHARACTERCHARLARGE OBJECTCLOBDBCLOBBINARY LARGE OBJECTBLOB(1M)(integerKMG)

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.

Notes

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') '&lt;'
'>'(X'3E') '&gt;'
carriage return (X'0D') '&#x0d;'
quote (X'22')1 '&quot;'
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.
Start of change

Examples

Example 1:
Serialize XML values into CLOB(100) values. The XMLELEMENT function generates the XML values from the concatenation of the FIRSTNME and LASTNAME column values of the sample EMP table.
SELECT e.EMPNO as "ID",
 XMLSERIALIZE(
  XMLELEMENT(
   NAME "Emp",
   e.FIRSTNME || ' ' || e.LASTNAME)
  AS CLOB(100))
 AS "Result"
 FROM EMP e
 WHERE LASTNAME LIKE 'P%';
The query returns results similar to these: 
ID      Result
-----------------------------------
000070  <Emp>EVA PULASKI</Emp>
000160  <Emp>ELIZABETH PIANKA</Emp>
000270  <Emp>MARIA PEREZ</Emp>
000290  <Emp>JOHN PARKER</Emp>
Example 2:
Serialize XML values into BLOB(1K) values. The XMLELEMENT function generates the XML values from the concatenation of the FIRSTNME and LASTNAME column values of the sample EMP table.
SELECT e.EMPNO as "ID",
 XMLSERIALIZE(
  XMLELEMENT(
   NAME "Emp",
   e.FIRSTNME || ' ' || e.LASTNAME)
  AS BLOB(1K))
 AS "Result"
 FROM EMP e
 WHERE LASTNAME LIKE 'P%';
The query returns results similar to these: 
ID      Result
--------------------------------------------------------------
000070  3C456D703E4556412050554C41534B493C2F456D703E
000160  3C456D703E454C495A4142455448205049414E4B413C2F456D703E
000270  3C456D703E4D4152494120504552455A3C2F456D703E
000290  3C456D703E4A4F484E205041524B45523C2F456D703E
End of change