Extract Data from Xml Clob Using SQL from Oracle Database

Extract data from XML Clob using SQL from Oracle Database

Try

SELECT EXTRACTVALUE(xmltype(testclob), '/DCResponse/ContextData/Field[@key="Decision"]') 
FROM traptabclob;

Here is a sqlfiddle demo

Oracle 10g: Extract data (select) from XML (CLOB Type)

Try this instead:

select xmltype(t.xml).extract('//fax/text()').getStringVal() from mytab t

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;

or

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;

extract xml tag value from clob field using oracle

You are confusing attribute and node selection. SectionId is not an attribute of the section, which is what your [@SectionId=...] is looking for.

You could do this by identifying the node text values and walking back up the tree:

select extractvalue(xmltype(req_details),
  '/FM/SectionsList/Section/SectionId[text()="Section_two"]/../Fields/FormField/FieldId[text()="REQUESTNAME"]/../FieldValue')
  as result
from your_table

RESULT              
--------------------
JASMINE

or as extractvalue() is deprecated, with an XMLQuery instead:

select xmlquery(
  '/FM/SectionsList/Section/SectionId[text()="Section_two"]/../Fields/FormField/FieldId[text()="REQUESTNAME"]/../FieldValue/text()'
  passing xmltype(req_details)
  returning content) as result
from your_table

RESULT              
--------------------
JASMINE

Or with a more explicit XPath that avoids having to walk back up the tree (so a little easier to follow, and harder to get lost):

select xmlquery(
  'for $i in /FM/SectionsList/Section where $i/SectionId="Section_two"
   return
     for $j in $i/Fields/FormField where $j/FieldId="REQUESTNAME"
       return $j/FieldValue/text()'
  passing xmltype(req_details)
  returning content) as result
from your_table;

RESULT              
--------------------
JASMINE

Extracting value from xml clob with Namespace using Oracle pl/sql

Your updated XML has a namespace, which finally reveals the issue. You need to specify the namespace as part of the XML extraction, which is simpler with the XMLTable approach; in this case you can just treat it as the default namespace:

select itc.element_name, x.user_classification3
from i_transaction itc
cross join xmltable(
  xmlnamespaces(default 'http://xmlns.oracle.com/apps/otm'),
    '/TenderOffer/Shipment/RATE_OFFERING/RATE_OFFERING_ROW'
  passing xmltype(itc.xml_blob)
  columns user_classification3 varchar2(10) path 'USER_CLASSIFICATION3'
) x
where itc.i_transaction_no = 31553115
and rownum = 1;

ELEMENT_NA USER_CLASS
---------- ----------
dummy      ZXF       

or with XMLQuery:

select itc.element_name, xmlquery(
  'declare default element namespace "http://xmlns.oracle.com/apps/otm"; (: :)
    /TenderOffer/Shipment/RATE_OFFERING/RATE_OFFERING_ROW/USER_CLASSIFICATION3/text()'
  passing xmltype(itc.xml_blob)
  returning content
) x
from i_transaction itc
where itc.i_transaction_no = 31553115
and rownum = 1;

ELEMENT_NA X                                                                               
---------- --------------------------------------------------------------------------------
dummy      ZXF                                                                             

If you wanted to keep using the deprecated extractvalue() function you can supply the namespace as an argument to that too, again as shown in the documentation:

select itc.element_name,
  extractvalue(xmltype(xml_blob),
    '/TenderOffer/Shipment/RATE_OFFERING/RATE_OFFERING_ROW/USER_CLASSIFICATION3/text()',
    'xmlns="http://xmlns.oracle.com/apps/otm"')
from i_transaction itc where itc.i_transaction_no = 31553115 and rownum = 1;

Oracle 12c: Extract data (select) from XML (CLOB Type)

Example xml has namespaces. And you have to use it.

xmltype.extract('/otm:ShipmentStatus', 'xmlns:gtm="http://xmlns.oracle.com/apps/gtm/transmission/v6.4" 
xmlns:otm="http://xmlns.oracle.com/apps/otm/transmission/v6.4"')  extacting node from specify namespace 

xmltype.extract('/*:ShipmentStatus')  extractin node from any namespace 

Extract rows from oracle based on XML Clob search string

Your xml data does not actually match the xml format you gave us. :)

One of the levels in your XPath expression is returning 2 nodes instead of 1. I'm guessing it's the <List> node which has multiple <Message> children, but it could be anything. Here's how you solve that problem:

-- sample data
with SPT_IDENTITY_REQUEST as (select to_clob('<Attributes>
  <Map>
    <entry key="messages">
      <value>
        <List>
          <Message key="err_exception" type="Error">
            <Parameters>
              <Message key="CIS policy sample exception #1" type="Error"/>
            </Parameters>
          </Message>
          <Message key="err_exception" type="Error">
            <Parameters>
              <Message key="CIS policy sample exception #2" type="Error"/>
            </Parameters>
          </Message>
        </List>
      </value>
    </entry>
  </Map>
</Attributes>') as attributes from dual)
-- your query
select * from (select *
from identityiq.SPT_IDENTITY_REQUEST att,
      xmltable('Attributes'
        passing xmltype(att.attributes)
        columns MessageXML XMLType path '/Attributes/Map/entry[@key="messages"]/value/List/Message[@key="err_exception"]' ) x,
      xmltable('/Message' -- add a second xmltable to handle multiple nodes at this level
        passing x.MessageXML
        columns Message varchar2(100) path '/Message/Parameters/Message/@key') m
      ) res
      where res.message is not null 
      and res.message like '%CIS policy%';

See also this answer to a similar question, which covers it in more depth.

Using SQL to extract values from xml CLOB

You could look for ways to PIVOT the output if you like, but here's a start to how you would read such an XML -

For simplicity, assuming that the XML is inside a variable named my_xml of the type XMLTYPE.

SELECT header_string, attribute_name, attribute_value
  FROM (WITH xm AS (SELECT my_xml AS x FROM DUAL)
        SELECT header_string, linked_hash_map
          FROM xm,
               XMLTABLE (
                  '//map/entry'
                  PASSING xm.x
                  COLUMNS header_string VARCHAR2 (100) PATH 'string[1]',
                          linked_hash_map XMLTYPE PATH 'string[2]/linked-hash-map'))
       l,
       XMLTABLE (
          '//linked-hash-map/entry'
          PASSING l.linked_hash_map
          COLUMNS attribute_name VARCHAR2 (100) PATH 'string[1]',
                  attribute_value VARCHAR2 (100) PATH 'string[2]');

I should mention that if possible, look for ways to improve the XML schema. From the question, it seems you already know that a better way is to use a key value pair where the key name isn't the actual data but the property of the element (if not the element name itself).

Also, don't use EXTRACT or EXTRACTVALUE, those functions have been deprecated by Oracle. Use XMLTABLE or XMLQUERY instead.

---

Related Topics