This discussion is archived
2 Replies Latest reply: Jul 25, 2013 2:24 AM by vikramrathour RSS

Retrieve XML stored in CLOB as columns

vikramrathour Newbie
Currently Being Moderated

Hi,

 

I have the below table that holds XML in a CLOB

 

CREATE TABLE testxml
  (idcol NUMBER(3), xml_data CLOB
  );
  
INSERT
INTO testxml VALUES
  (
    201,
    '

<TRADE>
   <TRADETYPE>SWAP</TRADETYPE>
   <SUBTYPE>CDS</SUBTYPE>
   <TRADEHEADER>
      <ACTION>NEW</ACTION>
      <SOURCEID>ABS-CD</SOURCEID>
      <TRADEID>20595896</TRADEID>
   </TRADEHEADER>
   <TRADELEGS>
      <TRADELEG>
         <PAY_OR_RECEIVE>P</PAY_OR_RECEIVE>
         <FIXED_FLOAT_IND>FLT</FIXED_FLOAT_IND>
         <DAY_COUNT_BASIS>A365F</DAY_COUNT_BASIS>
         <ORIG_PRIMARY_CURRENCY>ZAR</ORIG_PRIMARY_CURRENCY>
         <FIRST_VALUE_DATE>20120511</FIRST_VALUE_DATE>
      </TRADELEG>
      <TRADELEG>
         <PAY_OR_RECEIVE>R</PAY_OR_RECEIVE>
         <FIXED_FLOAT_IND>FIX</FIXED_FLOAT_IND>
         <DAY_COUNT_BASIS>A365F</DAY_COUNT_BASIS>
         <ORIG_PRIMARY_CURRENCY>ZAR</ORIG_PRIMARY_CURRENCY>
         <FIRST_VALUE_DATE>20120511</FIRST_VALUE_DATE>
      </TRADELEG>
   </TRADELEGS>
   <PVS>
      <PV_SOURCE>ABS-CD</PV_SOURCE>
      <PV>
         <NPV>0</NPV>
         <NPV_CCY>ZAR</NPV_CCY>
         <VALUATION_DATE>20130628</VALUATION_DATE>
         <LEG_NUMBER>1</LEG_NUMBER>
      </PV>
      <PV>
         <NPV>2214864.54</NPV>
         <NPV_CCY>ZAR</NPV_CCY>
         <VALUATION_DATE>20130628</VALUATION_DATE>
         <LEG_NUMBER>2</LEG_NUMBER>
      </PV>
   </PVS>
   <CREDIT_DERIVATIVES>
      <CREDIT_DERIVATIVE>
         <NOMINAL>100000000</NOMINAL>
         <MATURITY_DATE>20170620</MATURITY_DATE>
         <BUY_SELL_INDICATOR>SELL</BUY_SELL_INDICATOR>
         <CURRENCY>ZAR</CURRENCY>
      </CREDIT_DERIVATIVE>
      <CREDIT_DERIVATIVE>
         <NOMINAL>100002000</NOMINAL>
         <MATURITY_DATE>20170620</MATURITY_DATE>
         <BUY_SELL_INDICATOR>BUY</BUY_SELL_INDICATOR>
         <CURRENCY>USD</CURRENCY>
      </CREDIT_DERIVATIVE>
   </CREDIT_DERIVATIVES>
</TRADE>
'
  );

 

I need the data to be retrieved as columns. I have tried the below SQL but it works for one XMLTable i.e. TRADELEGS. But If I try to use another XMLTable for PVS then it does a cartesian join.

 

SELECT id,
  xmltype(xml_data).extract('/TRADE/TRADETYPE/text()').getStringVal()          AS TRADETYPE,
  xmltype(xml_data).extract('/TRADE/SUBTYPE/text()').getStringVal()            AS SUBTYPE,
  xmltype(xml_data).extract('/TRADE/TRADEHEADER/ACTION/text()').getStringVal() AS ACTION,
  x.PAY_OR_RECEIVE,
  x.FIXED_FLOAT_IND
FROM testxml,
  XMLTable( '/TRADE/TRADELEGS/TRADELEG' passing XMLTYPE(xml_data) 
  columns 
  PAY_OR_RECEIVE VARCHAR2(20) PATH 'PAY_OR_RECEIVE' 
  , FIXED_FLOAT_IND VARCHAR2(20) PATH 'FIXED_FLOAT_IND' ) x;

 

The below query does not work:

 

SELECT id,
  xmltype(xml_data).extract('/TRADE/TRADETYPE/text()').getStringVal()          AS TRADETYPE,
  xmltype(xml_data).extract('/TRADE/SUBTYPE/text()').getStringVal()            AS SUBTYPE,
  xmltype(xml_data).extract('/TRADE/TRADEHEADER/ACTION/text()').getStringVal() AS ACTION,
  tradeleg.PAY_OR_RECEIVE,
  tradeleg.FIXED_FLOAT_IND
  ,xmltype(xml_data).extract('/TRADE/PVS/PV_SOURCE/text()').getStringVal() AS PV_SOURCE
  ,pvs.npv,
  pvs.VALUATION_DATE
FROM testxml,
  XMLTable( '/TRADE/TRADELEGS/TRADELEG' passing XMLTYPE(xml_data) 
  columns 
  PAY_OR_RECEIVE VARCHAR2(1) PATH 'PAY_OR_RECEIVE' 
, FIXED_FLOAT_IND VARCHAR2(3) PATH 'FIXED_FLOAT_IND' ) tradeleg
  ,XMLTable( '/TRADE/PVS/PV' passing XMLTYPE(xml_data) 
  columns 
  NPV NUMBER PATH 'NPV' 
, VALUATION_DATE VARCHAR2(8) PATH 'VALUATION_DATE' ) pvs
WHERE id = 1;

 

I need the output for TRADELEGS, PVS and CREDIT_DERIVATIVES.

 

Regards,

Vikram

Legend

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