2 Replies Latest reply: Jan 31, 2013 6:16 AM by 976707 RSS

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

      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?