Thanks for the question, Jesus.

Asked: November 20, 2020 - 2:25 pm UTC

Last updated: November 15, 2022 - 5:27 pm UTC

Version: C2M

Viewed 10K+ times! This question is

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 Chris 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

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

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;

Extracting Attributes using XMLTABLE

BC, November 15, 2022 - 1:51 am UTC

Please help me extract the XML attributes using XMLTABLE, below is what my data looks like

with mvr_dtl (vendor_xml) as (
  select to_clob('<Output name="ReadableResponse">
  <Envelope>
    <Body>
      <GetReportResponse>
        <GetReportResult>
            <report>
              <report_data>
                <generation_date_time>11/14/2022 2:32:35 PM</generation_date_time>
                <paypal_id>308897373</paypal_id>
                <report_id>127417202</report_id>
                <contract_detail_included_cnt>1</contract_detail_included_cnt>
                <sic_codes>
                  <sic_code code="0339">GUZZMANN AND SONS</sic_code>
                </sic_codes>
                <managers>
                  <manager title="Secretary">DIANA LEMKE</manager>
                </managers>
                <corporate_registration>
                  <registration_state/>
                  <registration_date_incorporated>1900-01-01</registration_date_incorporated>
                  <registration_business_type>CORPORATE</registration_business_type>
                  <registration_status/>
                </corporate_registration>
                <total_employees/>
                <years_in_business>24</years_in_business>
              </report_data>
            </report>
        </GetReportResult>
      </GetReportResponse>
    </Body>
  </Envelope>
</Output>')
  from dual
)
select   x.*
from     mvr_dtl m,
xmltable ( '/Output/Envelope/Body/GetReportResponse/GetReportResult/report/report_data'
   passing xmltype( m.vendor_xml )
   returning sequence by ref
   columns
      paypal_id                       varchar2(32)     path 'paypal_id',
      report_id                       varchar2(32)     path 'report_id',
      manager                         varchar2(32)     path 'managers',
      title                           varchar2(32)     path '@title',
      code                            varchar2(32)     path '@code',
      sic_codes                       varchar2(32)     path 'sic_codes'
         ) as x

This is what the current query returns

PAYPAL_ID REPORT_ID MANAGER TITLE CODE SIC_CODES
---------- ---------- --------------- ---------- ---------- --------------------------------
308897373 127417202 DIANA LEMKE <<null>> <<null>> GUZZMANN AND SONS

This is what I would like to see

PAYPAL_ID REPORT_ID MANAGER TITLE CODE SIC_CODES
---------- ---------- --------------- ---------- ---------- --------------------------------
308897373 127417202 DIANA LEMKE Secretary 0339 GUZZMANN AND SONS

November 15, 2022 - 7:23 am UTC

with mvr_dtl (vendor_xml) as (
  select to_clob('<Output name="ReadableResponse">
  <Envelope>
    <Body>
      <GetReportResponse>
        <GetReportResult>
            <report>
              <report_data>
                <generation_date_time>11/14/2022 2:32:35 PM</generation_date_time>
                <paypal_id>308897373</paypal_id>
                <report_id>127417202</report_id>
                <contract_detail_included_cnt>1</contract_detail_included_cnt>
                <sic_codes>
                  <sic_code code="0339">GUZZMANN AND SONS</sic_code>
                </sic_codes>
                <managers>
                  <manager title="Secretary">DIANA LEMKE</manager>
                </managers>
                <corporate_registration>
                  <registration_state/>
                  <registration_date_incorporated>1900-01-01</registration_date_incorporated>
                  <registration_business_type>CORPORATE</registration_business_type>
                  <registration_status/>
                </corporate_registration>
                <total_employees/>
                <years_in_business>24</years_in_business>
              </report_data>
            </report>
        </GetReportResult>
      </GetReportResponse>
    </Body>
  </Envelope>
</Output>')
  from dual
)
select   x.*
from     mvr_dtl m,
xmltable ( '/Output/Envelope/Body/GetReportResponse/GetReportResult/report/report_data'
   passing xmltype( m.vendor_xml )
   returning sequence by ref
   columns
      paypal_id                       varchar2(32)     path 'paypal_id',
      report_id                       varchar2(32)     path 'report_id',
      manager                         varchar2(32)     path 'managers',
      title                           varchar2(32)     path 'managers/manager/@title',
      code                            varchar2(32)     path 'sic_codes/sic_code/@code',
      sic_codes                       varchar2(32)     path 'sic_codes'
         ) as x;

Thank you !!

BC, November 15, 2022 - 1:35 pm UTC

Thank you so much !!

November 15, 2022 - 5:27 pm UTC

You're welcome

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer