3 Replies Latest reply: Jan 31, 2013 10:01 AM by Jason_(A_Non) RSS

    Variablize the datatypes in the select of data from XML to Oracle

      Hi All,
      I'm new to XML to Oracle data integration. I do have clob column that has the XML data and trying to bring it into the Oracle relational tables, as a part of it we are developing PL/SQL procedure that calls this CLOB column, I want to know if we can variablize the data types ( Just like tablename.columnname%type) in the select of the XML data:

      select v.instrid, v.endtoendid, v.txid, v.cd, v.ccy,
      replace(v.intrbksttlmamt,'.',',') as intrbksttlmamt, v.chrgbr, v.bic1, v.nm1, v.adrline11, v.adrline12, v.ctry1, v.iban1,
      v.bic2, v.bic3, v.nm2, v.adrline21, v.adrline22, v.ctry2, v.iban2, v.cdtrref, v.addtlrmtinf
      from the_data w,
      xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:pacs.008.001.01'),
      passing xmltype(w.the_column)
      columns instrid varchar2(300) path 'PmtId/InstrId',
      endtoendid varchar2(150) path 'PmtId/EndToEndId',
      txid varchar2(200) path 'PmtId/TxId',
      cd varchar2(50) path 'PmtTpInf/SvcLvl/Cd',
      ) v

      As you see above in the bold I do define the data types after every column name , the problem is incase there is change in datatype I do need to manually edit the procedure to change the datatype is there a way I can variablize them something like tablename.columname%type.

      Any answers are greatly appreciated.