I'm trying to write a function that needs to extract a value from a string containing an XML document. I don't have XML columns in my DB but just strings also for XML, for historical reasons.
I don't have much experience with XMLQUERY so I would code my function like this, but it doesn't compile:
create function extractField
(in p_fieldName varchar(8000),
in p_fieldNumber integer,
in p_fieldsXml clob
return XMLCAST(XMLQUERY( '$FLD/ObjectFields[Name=$FLD_NAME,Number=$FLD_NUMBER]/Contents'
PASSING p_fieldsXml AS "FLD",
p_fieldName AS "FLD_NAME",
p_fieldNumber AS "FLD_NUMBER") as clob) ;
For example, if you pass the string
'<?xml version="1.0" encoding="UTF-8"?>
'Name' as p_fieldName and
0 as p_fieldNumber
the output of the function should be: 'DNS Name'
Could you please suggest the correct implementation for this?