0

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.

Rod Xavier
  • 3,983
  • 1
  • 29
  • 41
Mohan
  • 3
  • 1

1 Answers1

0

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 ..
Lennart - Slava Ukraini
  • 6,936
  • 1
  • 20
  • 32