Extracting Xml Sub-Tags from a Clob in Oracle via SQL

Extracting XML sub-tags from a clob in Oracle via SQL

EXTRACTVALUE is deprecated in Oracle 12 - it is being replaced by XMLTABLE or XMLQUERY.

SELECT x.description
FROM   your_table t
       CROSS JOIN
       XMLTABLE(
         '//object/'
         PASSING XMLTYPE( t.your_clob_column )
         COLUMNS description VARCHAR2(4000) PATH './property[@name="description"]/@value'
       ) x;

SELECT XMLQUERY(
         '//object/property[@name="description"]/@value'
         PASSING XMLTYPE( your_clob_column )
         RETURNING CONTENT
       ).getStringVal()
FROM   your_table;

But if you do want to use EXTRACTVALUE you can do:

SELECT EXTRACTVALUE(
         XMLTYPE( your_clob_column ),
         '//object/property[@name="description"]/@value'
       )
FROM   your_table;

How to extract values from a single xmltype tag into different columns in oracle?

Use XMLTABLE:

SELECT object_id,
       x.*
FROM   object_history h
       CROSS APPLY XMLTABLE(
         '/LOCATION'
         PASSING h.xml_data
         COLUMNS
           type VARCHAR2(3)  PATH './@TYPE',
           epsg NUMBER       PATH './@EPSG',
           unit VARCHAR2(10) PATH './@UNIT',
           x    NUMBER       PATH './X',
           y    NUMBER       PATH './Y'
       ) x

Which, for the sample data:

CREATE TABLE object_history ( object_id, xml_data ) AS
SELECT 1, XMLTYPE('<LOCATION TYPE="ABS" EPSG="4277" UNIT="decLL"><X>-2</X><Y>49</Y></LOCATION>') FROM DUAL UNION ALL
SELECT 2, XMLTYPE('<LOCATION TYPE="ABS" EPSG="4277" UNIT="decLL"><X>-1</X><Y>50</Y></LOCATION>') FROM DUAL UNION ALL
SELECT 3, XMLTYPE('<LOCATION TYPE="REL" EPSG="4277" UNIT="decLL"><X>0</X><Y>51</Y></LOCATION>') FROM DUAL;

Outputs:

OBJECT_ID	TYPE	EPSG	UNIT	X	Y
1	ABS	4277	decLL	-2	49
2	ABS	4277	decLL	-1	50
3	REL	4277	decLL	0	51

XML Tag Value Extract from Clob column with Multiple Tags with Same Name in Oracle

You are doing cross join 2x2 = 4. You should get atrbtList as table (sequence of Atrbts):

 SELECT XMLTYPE(xml_clob).extract('//id/text()').getStringVal() AS Id,
        EXTRACTVALUE( s.COLUMN_VALUE, '/Atrbts/atrTyp' ) AS AtrTyp,
        EXTRACTVALUE( s.COLUMN_VALUE, '/Atrbts/atrVal' ) AS AtrVal
    FROM   table_name T,
           TABLE(
             XMLSequence(
               EXTRACT(
                 XMLType( T.xml_clob ),
                 '/data/atrbtList/*'
               )
             )
           ) s

Extracting value from CLOB XML column

Created a query which takes your XML (in WITH part) and performs extracts (scroll to the end).

WITH test_table
  AS (SELECT TO_CLOB('<EventPayloadDetails 
     xmlns:bpmn="http://schemas.oracle.com/bpm/xpath" 
     xmlns:ns3="urn:qi.com/EventPayloadDetails"  
     xmlns="urn:qi.com/EventPayloadDetails">
       <ns3:PayloadDetailsList>
           <ns3:PayloadDetail>
               <ns3:key>NUMBER</ns3:key>
               <ns3:value>0014</ns3:value>
           </ns3:PayloadDetail>
           <ns3:PayloadDetail>
               <ns3:key>Dep Num</ns3:key>
               <ns3:value>120</ns3:value>
           </ns3:PayloadDetail>
           <ns3:PayloadDetail>
               <ns3:key>CreatedByUserID</ns3:key>
               <ns3:value>7934665</ns3:value>
           </ns3:PayloadDetail>
           <ns3:PayloadDetail>
               <ns3:key>CreatedByFirstName</ns3:key>
               <ns3:value>M</ns3:value>
           </ns3:PayloadDetail>
           <ns3:PayloadDetail>
               <ns3:key>CreatedByLastName</ns3:key>
               <ns3:value>Ash</ns3:value>
           </ns3:PayloadDetail>
           <ns3:PayloadDetail>
               <ns3:key>Comments</ns3:key>
               <ns3:value>"Review after this 
   query"
   </ns3:value>
           </ns3:PayloadDetail>
       </ns3:PayloadDetailsList>
   </EventPayloadDetails>') as test_data
   from dual)
SELECT XMLTYPE(test_data) xml_data
     , EXTRACTVALUE(
          XMLTYPE(test_data)
         ,'//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value'
         ,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
       ) value
     , XMLTYPE(test_data).extract(
                             '//*/ns3:PayloadDetailsList/ns3:PayloadDetail[ns3:key="CreatedByUserID"]/ns3:value/text()'
                            ,'xmlns:ns3="urn:qi.com/EventPayloadDetails"'
                          ).getStringVal() value2
  FROM test_table

How to extract value from xml clob with identical tags using FOR LOOP in PL/SQL

If I were you, I would avoid the use of the deprecated extract (and extractvalue) and use XMLTABLE to query the contents of the xml document. This will be faster than looping over the same document to extract the contents individually, since it means the xml doc will only need to be read once.

You can do this like so:

DECLARE
  v_xml XMLTYPE := XMLTYPE('<SuggestResponse>
  <suggestions>
    <value>fred</value>
    <node2>abc</node2>
    <node3>1</node3>
  </suggestions>
  <suggestions>
    <value>bob</value>
    <node2>def</node2>
  </suggestions>
</SuggestResponse>');
BEGIN
  FOR rec IN (SELECT *
              FROM   XMLTABLE('/SuggestResponse/suggestions' PASSING v_xml
                              COLUMNS val varchar2(100) PATH 'value',
                                      node2 VARCHAR2(10) PATH 'node2',
                                      node3 NUMBER PATH 'node3'))
  LOOP
    IF rec.node2 = 'abc'
    THEN
      dbms_output.put_line(rec.val||': hurrah!');
    ELSE
      dbms_output.put_line(rec.val||': boo!');
    END IF;
  END LOOP;
END;
/

fred: hurrah!
bob: boo!

Extracting values from clob in oracle db separated by a delimiter

Please avoid unnecessary '//' operators... and the column patterns are implicitly relative to the row pattern

SQL> with MY_TABLE as
  2  (
  3    select 1 as PRIMARY_KEY,
  4           XMLTYPE(
  5  '<xml>
  6    <ShipConfirm>
  7     <ShipmentID>000000196</ShipmentID>
  8    </ShipConfirm>
  9    <ShipConfirm>
 10      <ShipmentID>000000197</ShipmentID>
 11    </ShipConfirm>
 12    <ShipConfirm>
 13      <ShipmentID>000000198</ShipmentID>
 14     </ShipConfirm>
 15  </xml>') as XML_DOC
 16      from DUAL
 17  )
 18  SELECT LISTAGG( x.ShipmentID, ',' ) WITHIN GROUP ( ORDER BY idx )  AS ShipmentIDs
 19    FROM MY_TABLE t,
 20         XMLTABLE(
 21           '/xml/ShipConfirm'
 22            PASSING XML_DOC
 23            COLUMNS
 24              idx FOR ORDINALITY,
 25              ShipmentID CHAR(9) PATH 'ShipmentID'
 26         ) x
 27  GROUP BY t.primary_key
 28  /

SHIPMENTIDS
--------------------------------------------------------------------------------
000000196,000000197,000000198

SQL>
SQL>

Substr more than one value in a Oracle CLOB column

If you have XML then treat is as XML, don't try to extract data from it using substrings. Presumably this is a fragment of a larger document that has parent nodes and declares the tns namespace; so you can do:

select x.description
from your_table t
cross apply xmltable(
  xmlnamespaces('https://some/url' as "tns"),
  '//tns:ExceptionList/tns:Exception'
  passing xmltype(t.your_clob)
  columns description path 'tns:Description'
) x

DESCRIPTION
Both HPI Make and HPI Category have to be entered - one cannot exist without the other.<br>
Exception List must be empty
AgreementNumber must not be supplied
IntroducerCode or Name is invalid
SupplierCode or Name invalid. Must exist on pancredit.

Extracting Xml Sub-Tags from a Clob in Oracle via SQL