This discussion is archived
2 Replies Latest reply: Jan 31, 2013 4:16 AM by 976707 RSS

XQuery syntax OK in 11R2 but not working in 10R2

976707 Newbie
Currently Being Moderated
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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points