2 Replies Latest reply: Jul 25, 2013 4:24 AM by vikramrathour RSS

    Retrieve XML stored in CLOB as columns

    vikramrathour

      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