CODEWITHSUNDEEP
db2
ashu
ashu

Reputation: 1

xmlcast is returing null value in Db2

I am always getting a null value from using XMLCAST function in Db2. But when using a normal function call it is returning value in xml format... as below query proper xml data is coming. function Getdatavalue is returning xml datatype. using Linux 8.2 and db2 11.5

 db2 "select sample.Getdatavalue(1,4504)) from sysibm.sysdummy1"

in the above result we have some double quotes in output xml.

but once we have used xmlcast function as below, it is returning the hexa value of null. i am using MD5 hashing mechanism.

db2 "select HEX(HASH(xmlcast(sample.Getdatavalue(1,4504) as varchar(32672)),0)) as hash_val from sysibm.sysdummy1";

even once i tried without Hash it was returning null result for below query. it looks xmlcast is not working.

db2 "select xmlcast(sample.Getdatavalue(1,4504) as varchar(32672)) as hash_val from sysibm.sysdummy1";

is there any way to convert xml datatype to clob or varchar.

body of Getdatavalue as:

CREATE FUNCTION sample.Getdatavalue (v1 smallint, v2 integer)
RETURNS xml
LANGUAGE SQL
BEGIN ATOMIC
DECLARE v_Data xml;
set v_Data = (SELECT XMLELEMENT( NAME "rt_ky_cmpnnt", 
XMLAGG(XMLELEMENT(NAME "rt", XMLAttributes(rt.rt_field_add_val AS 
"rt_add_val",
rt.rt_name AS 
 "rt_name",
rt.rt_name AS 
 "rt_name",
rt.rt_val1 AS "rt_val1",
rt_lim AS "rt_lim",
rt.rt_val2 AS "rt__val2",
rt.rt_whole_val AS "rt_whole_val" ))order by 
rf.rt_num asc)OPTION null ON NULL)
FROM sample.cmpnnt_val rt inner join sample.field_val rf
on rf.abc=rt.abc
and rf.pqr=rt.pqr
and rt.xyz=rf.xyz
where rt.abc = v1
AND rt.bcd = v2 );
RETURN v_Data;
END!

can anyone please explain how to handle xmlcast.

Upvotes: 0

Views: 125

Answers (1)

data_henrik
data_henrik

Reputation: 17156

To retrieve a string from an XML value, use the function XMLSERIALIZE.

The XMLSERIALIZE function returns a serialized XML value of the specified data type generated from the XML-expression argument.

Upvotes: 1

Related Questions