CODEWITHSUNDEEP
db2xquery-sql
Mohan
Mohan

Reputation: 3

DB2 how do I replace a double quote returned by an XML element

I am able to successfully retrieve the value of XML element using the following SQL

*SELECT XMLQUERY('$item/*:ItemSpec/*:AdditionalDescription/*:ABCDescription/text()' PASSING productval.value_xml as "item") AS H_DESCRIPTION FROM USER1.XMETA*

This returns a value that has double quotes (") in it. How do I replace it with a different value in the same select query. I tried something like this but it didn't work

Select REPLACE(XMLQUERY('$item/*:ItemSpec/*:AdditionalDescription/*:ABCDescription/text()' PASSING productval.value_xml as "item"),'"','QUOT') AS H_DESCRIPTION

The error is No authorized routine named "REPLACE" of type "FUNCTION" having compatible arguments was found...SQLCODE=-440,SQLSTATE==-42884.

Upvotes: 0

Views: 874

Answers (1)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

XMLQUERY returnes an XML type, try casting it to varchar before applying REPLACE on it:

REPLACE(XMLCAST(XMLQUERY('$item/*:ItemSpec/*:Addition ...) AS VARCHAR(...)), '"','QUOT') AS ..

Upvotes: 0

Related Questions