Create a function that converts from an XML to char
ALTER MODULE DB2UNIT ADD
FUNCTION XML_TO_CHAR(
_IF __SQLSTATE_429BB _THEN
IN VALUE CLOB
_ELSE
IN VALUE XML
_END
) RETURNS VARCHAR(500)
LANGUAGE SQL
SPECIFIC F_XML_TO_CHAR
DETERMINISTIC
NO EXTERNAL ACTION
PARAMETER CCSID UNICODE
F_XML_TO_CHAR: BEGIN
DECLARE RET VARCHAR(500) DEFAULT ' ';
IF (VALUE IS NULL) THEN
SET RET = 'NULL';
ELSE
_IF __SQLSTATE_429BB _THEN
SET RET = LEFT(VALUE, 500);
_ELSE
SET RET = XMLSERIALIZE(VALUE AS VARCHAR(500));
_END
END IF;
RETURN RET;
END F_XML_TO_CHAR @
Create a function that converts from an XML to char
ALTER MODULE DB2UNIT ADD FUNCTION XML_TO_CHAR( _IF __SQLSTATE_429BB _THEN IN VALUE CLOB _ELSE IN VALUE XML _END ) RETURNS VARCHAR(500) LANGUAGE SQL SPECIFIC F_XML_TO_CHAR DETERMINISTIC NO EXTERNAL ACTION PARAMETER CCSID UNICODE F_XML_TO_CHAR: BEGIN DECLARE RET VARCHAR(500) DEFAULT ' ';
IF (VALUE IS NULL) THEN SET RET = 'NULL'; ELSE _IF __SQLSTATE_429BB _THEN SET RET = LEFT(VALUE, 500); _ELSE SET RET = XMLSERIALIZE(VALUE AS VARCHAR(500)); _END END IF; RETURN RET; END F_XML_TO_CHAR @