0

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?

0
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   
  • there are other odo-reading and I forgot to add it to my question. When running your query I get: ORA-19025: EXTRACTVALUE returns value of only one node Please have a look at my updated question. I believe this happens because there isn't only one odo-reading. – Manuela Feb 4 at 23:04
  • @Manuela Added that version to the answer. – Balazs Papp Feb 4 at 23:15
  • EXTRACTVALUE is deprecated. Use XMLTable() instead. – Michael Kutz Feb 5 at 2:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.