Hi All,

I am using following query to retrieve Reference Id field value from varchar field (xmldata) containing data in xml format.

select xmlquery('$y/my-record/my-entry[name="Reference Id"]/value/text()'
passing xmlparse(document a.xmldata ) as "y")
from data a where a.dataky = 100200

the query output is as following

<?xml version="1.0" encoding="UTF-16" ?>12731293

How can just get the 12731293 value in result without that xmltag?

Thanks
Onida

I think you need to use XMLCAST() to convert the XML fragment to another datatype, e.g. VARCHAR or INT.

wow...thanks a lot

I modified it as -

select xmlcast(xmlquery('$y/my-record/my-entry[name="Reference Id"]/value/text()' passing xmlparse(document a.xmldata ) as "y") AS VARCHAR(30)) from data a where a.dataky = 100200

and it worked.

Regards,
Onida