Xml functions

[abercrombieande]

What am I doing wrong with XMLCAST? I get "S1000(-20338)[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQ20338 - The data type of the XMLCAST specification must be XML. (1.55 secs)". This has to be something easy that I am missing, XMLGROUP returns an XML datatype so I don't get it.

 
SELECT 
 XMLCAST(XMLGROUP(',' || DNCDE as a ORDER BY DNCDE) as varchar(2000))
 FROM TRPRTR/PLTRVT

[jamief]

Re: Xml functions

What ever happened with this?

jamie

[B.Hauser]

Re: Xml functions

If you want to convert a XML Document into a VarChar representation, you need the XMLSERIALIZE Scalar function instead of XMLCAST.
Also check your XMLGROUP syntax, I'm quite sure it won't return what you want.

Birgitta

[abercrombieande]

Re: Xml functions

XMLSERIALIZE works fine for changing the datatypes. I wished to use XMLCAST since I was told it would remove the XML tags. I know this may seem strange but I was trying to get a csv type column. I ended up making a procedure instead. The reason I thought XMLCAST would work was bc this ibm document.http://publib.boulder.ibm.com/infoce...2Fr0023486.htm

[B.Hauser]

Re: Xml functions

You refer to the SQL Reference for DB2 for Linux, Unix, Windows, which is not 100% identical to what is implemented in DB2 for i.
Please check the documentation for XMLCAST in DB2 for i Release 7.1:
XMLCAST specification

... data-type
Specifies the data type of the result. The data type must be XML or a distinct type based on XML.

If the CCSID attribute is not specified, then the CCSID value as specified by the SQL_XML_DATA_CCSID QAQQINI setting is used. See XML Values for more information.

If the CCSID attribute is specified, the data will be converted to that CCSID. If NORMALIZED is specified, the data will be normalized.

Birgitta