I'm new to XQuery and I'm struggling a bit with it, I've written an insert statement in our 11R2 environment and when it was deployed to a customer's 10R2 environment it throws an error:
select x.stmt
from source_table vdx1
, xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')
,'for $i in /Document/BkToCstmrStmt/Stmt where $i/Bal/Amt/@Ccy = $ccy return $i'
passing vdx1.content
, cast(l_ccy_code as varchar2(3)) as "ccy"
columns Stmt xmltype path '/*'
) x
where vdx1.id = l_vdx_id
ORA-00932: inconsistent datatypes: expected - got CHAR
The problem consists in the line with "cast", I basically need only the data where the value of /Bal/Amt/@Ccy is equal to a certain value which is stored in the variable l_ccy_code. This value varies, so I cannot just hard-code it.
Is there a way to write this select statement that would be correct for both 10R2 and 11R2 environments? Is there a better way to write the XQuery for my case, this is what I came up with, but due to my lack of experience with XQuery it might not be the optimal solution?
On 10.2, the PASSING clause only allows XMLType datatype.