This discussion is archived
0 Replies Latest reply: Nov 18, 2012 9:55 PM by 970358 RSS

Case Statement into XML , XMLType column

970358 Newbie
Currently Being Moderated
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.

Legend

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