You Asked

I have a clob column that as different tags in it, like the example below, I am trying to get the comments tag of all the rows, one of them is returning null, I am assuming it is because it has the word "comments" more than once, this is the query I am using:

select d.d1_activity_id, dbms_lob.substr(d.bo_data_area, dbms_lob.getlength(d.bo_data_area), 1) as DCLOB, extractValue(xmlparse(contentt d.bo_data_area),'comments' ) AS comnt from d1_activity d where dbms_lob.instr(d.bo_data_area,'comments') > 0

This is an example of the data we have in that column:

<comments>C2M Test Exchange Meter</comments><instructions>C2M Test Exchange Meter</instructions><replyToExternalSystem>D1YS</replyToExternalSystem><retryDetails><numberOfRetries>0</numberOfRetries><isToDoEntrySuppressed>false</isToDoEntrySuppressed></retryDetails><contactDetails/><connectNewDevice>D1CN</connectNewDevice><oldDeviceId>061840493997</oldDeviceId>
<isFieldActivityCompleted>D1NO</isFieldActivityCompleted><isAppointmentNecessary>N</isAppointmentNecessary><appointmentWindow/><comments>C2M Test for M-Exchange Orch to PragmaCad</comments><instructions>C2M Test for M-Exchange Orch to PragmaCad</instructions><isMeasurementFound>D1NO</isMeasurementFound><replyToExternalSystem>D1YS</replyToExternalSystem><retryDetails><numberOfRetries>0</numberOfRetries><isToDoEntrySuppressed>false</isToDoEntrySuppressed></retryDetails><allowParentTransition>true</allowParentTransition><overrideRestrictions>D1NA</overrideRestrictions><fieldWorkSystemAddress><address1>3456 BOWDEN CIR W</address1><address4>15305034560000&gt;&lt;193954</address4><crossStreet>6249</crossStreet><city>JACKSONVILLE</city><county>DUVAL</county><postal>32216</postal><country>USA</country><state>FL</state><geocodeLatitude>0.000000</geocodeLatitude><geocodeLongitude>0.000000</geocodeLongitude></fieldWorkSystemAddress><contactDetails/>
<updateSpecificActivity>D1YS</updateSpecificActivity><updateableItems><comments>Editing comments</comments><instructions>Editing comments</instructions><startDateTime>2020-10-27-00.00.00</startDateTime></updateableItems><isAppointmentNecessary>N</isAppointmentNecessary><appointmentWindow/><allowParentTransition>true</allowParentTransition><replyToExternalSystem>D1YS</replyToExternalSystem><retryDetails><numberOfRetries>0</numberOfRetries><isToDoEntrySuppressed>false</isToDoEntrySuppressed></retryDetails>

and we said...

You can use XMLTable to generate a row for each occurrence of an element.

To do this:

- Wrap your whole document with a parent element so it's well-formed
- Convert it to an XMLType
- Pass it to XMLTable, searching for the comments elements

Here's an example from a simplified version of your sample:

create table t (
  c1 clob
);
set define off
insert into t values ('<comments>C2M Test Exchange Meter
</comments>
<instructions>C2M Test Exchange Meter
</instructions>
<replyToExternalSystem>D1YS
</replyToExternalSystem>
<retryDetails>
  <numberOfRetries>0</numberOfRetries>
  <isToDoEntrySuppressed>false</isToDoEntrySuppressed>
</retryDetails>
<contactDetails />
<connectNewDevice>D1CN
</connectNewDevice>
<appointmentWindow />
<comments>C2M Test for M-Exchange Orch to PragmaCad
</comments>
<instructions>C2M Test for M-Exchange Orch to PragmaCad
</instructions>
<isMeasurementFound>D1NO
</isMeasurementFound>');
commit;

select x.*
from   t, xmltable (
  '/record/comments'
  passing xmltype ( '<record>' || c1 || '</record>' )
  columns 
    comments varchar2(100) path '/'  
) x;

COMMENTS                                     
C2M Test Exchange Meter
C2M Test for M-Exchange Orch to PragmaCad

Rating

(2 ratings)

Comments

How to extract specific tags from a clob colum storing XML

Jesus Cedillo, November 23, 2020 - 4:38 pm UTC

Thank you for taking the time to respond to my question, I have tried in different ways with including yours, I cannot get the example below:

create table t (
c1 clob
);
set define off
insert into t values ('<updateSpecificActivity>D1YS</updateSpecificActivity>
<updateableItems>
<comments>Editing comments</comments>
<instructions>Editing comments</instructions>
<startDateTime>2020-10-27-00.00.00</startDateTime>
</updateableItems>
<isAppointmentNecessary>N</isAppointmentNecessary>
<appointmentWindow/>
<allowParentTransition>true</allowParentTransition>
<replyToExternalSystem>D1YS</replyToExternalSystem>
<retryDetails>
<numberOfRetries>0</numberOfRetries>
<isToDoEntrySuppressed>false</isToDoEntrySuppressed>
</retryDetails>');
commit;

select x.*
from t, xmltable (
'/record/comments'
passing xmltype ( '<record>' || c1 || '</record>' )
columns
comments varchar2(100) path '/'
) x;

It does not return the comments.

November 24, 2020 - 1:27 am UTC

"Comments" sits underneath "updateable items" so

SQL> create table t (
  2  c1 clob
  3  );

Table created.

SQL> set define off
SQL> begin
  2  insert into t values ('<updateSpecificActivity>D1YS</updateSpecificActivity>
  3  <updateableItems>
  4  <comments>Editing comments</comments>
  5  <instructions>Editing comments</instructions>
  6  <startDateTime>2020-10-27-00.00.00</startDateTime>
  7  </updateableItems>
  8  <isAppointmentNecessary>N</isAppointmentNecessary>
  9  <appointmentWindow/>
 10  <allowParentTransition>true</allowParentTransition>
 11  <replyToExternalSystem>D1YS</replyToExternalSystem>
 12  <retryDetails>
 13  <numberOfRetries>0</numberOfRetries>
 14  <isToDoEntrySuppressed>false</isToDoEntrySuppressed>
 15  </retryDetails>');
 16  commit;
 17  end;
 18  /

PL/SQL procedure successfully completed.

SQL>
SQL> select x.*
  2  from t, xmltable (
  3  '/record/comments'
  4  passing xmltype ( '<record>' || c1 || '</record>' )
  5  columns
  6  comments varchar2(100) path '/'
  7  ) x;

no rows selected

SQL> select x.*
  2  from t, xmltable (
  3  '/record/updateableItems/comments'
  4  passing xmltype ( '<record>' || c1 || '</record>' )
  5  columns
  6  comments varchar2(100) path '/'
  7  ) x;

COMMENTS
-----------------------------------------------------------------------------------------------
Editing comments

SQL>

A little review of the XML

Paul, November 23, 2020 - 5:48 pm UTC

Try this one...
select x.*
from t, xmltable (
'/record'
passing xmltype ( '<record>' || c1 || '</record>' )
columns
comments varchar2(100) path '//comments'
) x;

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer