0 Replies Latest reply: Nov 18, 2012 11:55 PM by 970358 RSS

    Case Statement into XML , XMLType column

    970358
      We are storing following(example) xml into xmltype column (column name xmlfile , table name xml_table) .
      <?xml version = '1.0' encoding = 'UTF-8'?>
      <context id="reporting_options">
            <scenario>
               <abc:isAllow>false</abc:isAllow>
            </scenario>
         </context>
         <abc:tab1 contextRef="con1">89841</abc:tab1>
         <abc:tab1 contextRef="con2">-18514</abc:tab1>
         <abc:tab2 contextRef="con1">89841</abc:tab2>
         <abc:tab2 contextRef="con2">-18514</abc:tab2>
         <abc:tab3 contextRef="con1">By Current/Non-Current Classification</abc:tab3>
         <abc:tab3 contextRef="con2">0</abc:tab3>
      </xml>
      I want to extract the values for tab1 .. tab3 based on * <abc:isAllow>false</abc:isAllow>*. Now i'm doing in following way.Note , based on abc:isAllow values i need to pick contextRef="con1" or contextRef="con1".
      select 
      case extractValue(xmlfile ,'//abc:isAllow', 'xxx:bac="http://www.abc.com/abc"')
        when 'true' then extractValue(xmlfile ,'//abc:tab1[@contextRef="con1"]', 'xxx:bac="http://www.abc.com/abc"')
        else  extractValue(xmlfile ,'//*abc:tab1*[@contextRef="*con2*"]', 'xxx:bac="http://www.abc.com/abc"')
        end as tab1,
      
      case extractValue(xmlfile ,'//abc:isAllow', 'xxx:bac="http://www.abc.com/abc"')
        when 'true' then extractValue(xmlfile ,'//abc:tab2[@contextRef="con1"]', 'xxx:bac="http://www.abc.com/abc"')
        else  extractValue(xmlfile ,'//abc:tab2*[@contextRef="con2"]', 'xxx:bac="http://www.abc.com/abc"')
        end as tab2,
      
      case extractValue(xmlfile ,'//abc:isAllow', 'xxx:bac="http://www.abc.com/abc"')
        when 'true' then extractValue(xmlfile ,'//*abc:tab3*[@contextRef="con1"]', 'xxx:bac="http://www.abc.com/abc"')
        else  extractValue(xmlfile ,'//abc:tab3[@contextRef="con2"]', 'xxx:bac="http://www.abc.com/abc"')
        end as tab3
      from xml_table
      I think , above query might not be optimized because "case when" part will ready abc:isAllow for each tag. and my actual xml is containing more than 200 tag. Any one suggest good way to handle this query.

      Regards,
      thees_k.