Oracle – Using WHERE Clause in an XML Column

oraclexml

I have the following XML column in my Oracle 11 G DB:

<?xml version="1.0" encoding="UTF-8"?>
<l-analysis version="2.9">
   <vehicle>
      <odo-reading latest="true">
         <reading value="653463">653,463</reading>
         <reading-date value="2018-12-04">4-Dec-2018</reading-date>
         <reading-unit code="K">Km</reading-unit>
      </odo-reading>
  <odo-reading latest="false">
     <reading value="391264">393,264</reading>
     <reading-date value="2018-12-04">4-Dec-2018</reading-date>
     <reading-unit code="K">Km</reading-unit>
  </odo-reading>
   </vehicle>
</l-analysis>

I need to query the reading value but only where odo-reading latest="true".

How can I do that in a CLOB column?

Best Answer

drop table t1 purge;
create table t1 (id number, cxml clob);
insert into t1 values (1,
'<?xml version="1.0" encoding="UTF-8"?>
<l-analysis version="2.9">
   <vehicle>
      <odo-reading latest="true">
         <reading value="653463">653,463</reading>
         <reading-date value="2018-12-04">4-Dec-2018</reading-date>
         <reading-unit code="K">Km</reading-unit>
      </odo-reading>
  <odo-reading latest="false">
     <reading value="391264">393,264</reading>
     <reading-date value="2018-12-04">4-Dec-2018</reading-date>
     <reading-unit code="K">Km</reading-unit>
  </odo-reading>
   </vehicle>
</l-analysis>');
commit;

select extractvalue(xmltype(cxml), 'l-analysis/vehicle/odo-reading[@latest="true"]/reading/@value') as value from t1;

VALUE     
----------
653463

Answer to the original question before it was edited:

drop table t1 purge;
create table t1 (id number, cxml clob);
insert into t1 values (1,
'<?xml version="1.0" encoding="UTF-8"?>
<l-analysis version="2.9">
   <vehicle>
      <odo-reading latest="true">
         <reading value="653463">653,463</reading>
         <reading-date value="2018-12-04">4-Dec-2018</reading-date>
         <reading-unit code="K">Km</reading-unit>
      </odo-reading>
   </vehicle>
</l-analysis>');
insert into t1 values (2,
'<?xml version="1.0" encoding="UTF-8"?>
<l-analysis version="2.9">
   <vehicle>
      <odo-reading latest="false">
         <reading value="653463">653,464</reading>
         <reading-date value="2018-12-04">4-Dec-2018</reading-date>
         <reading-unit code="K">Km</reading-unit>
      </odo-reading>
   </vehicle>
</l-analysis>');
commit;

select id, extractvalue(xmltype(cxml), 'l-analysis/vehicle/odo-reading/reading/@value') as value from t1
where extractvalue(xmltype(cxml), 'l-analysis/vehicle/odo-reading/@latest') = 'true';   

        ID VALUE     
---------- ----------
         1 653463
Related Question