Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, sangita.

Asked: April 16, 2003 - 9:19 am UTC

Last updated: May 10, 2019 - 8:39 am UTC

Version: 9.0.1

Viewed 50K+ times! This question is

You Asked

Hi Tom

I have a table t_request
(request_number number,
request_detail clob)

For example the clob could have a value of
<REQUEST_DETAIL><GROUP_TYPE>PR</GROUP_TYPE><GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME><BUS_UNIT_ACRN>SCS-FCAT</BUS_UNIT_ACRN><PROJ_MGR_ID></PROJ_MGR_ID><PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME></REQUEST_DETAIL>

I need a query to extract the value in the Group_type tag .
I tried the following but am getting an error

sql > select e.request_detail.extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
from t_request e
where request_number = 9348605;
select e.request_detail.extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
*
ERROR at line 1:
ORA-22806: not an object or REF

However the same code works in a PL/SQL block where i convert the request_detail (clob) to sys.xmltype before using the extract
function on it.

Please show me how to extract the value out of an xml tag on a clob using a sql query . I have seen various examples of doing the
same on xmltype column but they do not work on a clob .

Thanks
Sangita

and Tom said...

Well, you know -- this is alot like storing a DATE in a number, a number in a string.

You know -- a bad idea. You'll spend the rest of your life converting -- and if the data in the clob isn't actually XML (well formed enough), you won't find out until you need it (just like when that string that should be a number contains 'hello world')

Here is a temporary solution -- long term, use the right type for the data:


ops$tkyte@ORA920LAP> create table t ( x clob );

Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> insert into t values (
2 '<REQUEST_DETAIL>
3 <GROUP_TYPE>PR</GROUP_TYPE>
4 <GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>
5 <BUS_UNIT_ACRN>SCS-FCAT</BUS_UNIT_ACRN>
6 <PROJ_MGR_ID></PROJ_MGR_ID>
7 <PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>
8 </REQUEST_DETAIL>' );

1 row created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select e.x.extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
2 from t e
3 /
select e.x.extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
*
ERROR at line 1:
ORA-22806: not an object or REF


ops$tkyte@ORA920LAP> select xmltype(e.x).extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
2 from t e
3 /

XMLTYPE(E.X).EXTRACT('/REQUEST_DETAIL/GROUP_TYPE/TEXT()').GETSTRINGVAL()
------------------------------------------------------------------------
PR


Rating

  (20 ratings)

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

Comments

Thanks very much

Sangita Saxena, April 16, 2003 - 11:23 am UTC

Thanks Tom - once again for a great solution .

Thanks

sangita saxena, April 16, 2003 - 11:40 am UTC

Hi tom
Thanks for the solution which i have already implemented .
But your statement "long term, use the right type for the data" . Isn't clob used for xml storage ? I thought clob and xmltype either could be used ?

Tom Kyte
April 16, 2003 - 12:00 pm UTC

You can store a number in a varchar2.

but it would be wrong. You spend lots of time converting strings into numbers over and over -- and the data isn't validated.


You can store XML in a clob.

but it would be wrong. You spend lots of time converting strings into XML documents over and over -- and the data isn't validated


Just substitute NUMBER for XML and VARCHAR for CLOB
Or
DATE for XML and NUMBER for CLOB
Or
<any specific type> for XML and <any more generic type> for CLOB

same argument. use the type that most closely models your data. XML -> xmltype. Unknown text -> clob.


getting an error

Anuja Batra, April 16, 2003 - 1:14 pm UTC

Tom
I tried the example and am getting an error
SQL > select
xmltype(e.x).extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
from t e;
xmltype(e.x).extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
*
ERROR at line 2:
ORA-00904: invalid column name

I also tried creating a table using xmltype and got an error
sql> create table yy(x xmltype);
create table yy(x xmltype)
*
ERROR at line 1:
ORA-00902: invalid datatype

However i could create the table using sys.xmltype
12:34:02 a246156@rmu2.world> create table yy(x sys.xmltype);

Table created.

So i replaced xmltype in your query with sys.xmltype and it still doesnt work .

sql > select
sys.xmltype(e.x).extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
from t e ;
sys.xmltype(e.x).extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
*
ERROR at line 2:
ORA-22876: this user-defined type is not allowed or it cannot be used in this context

Please advise
Thank You
Anuja

Tom Kyte
April 25, 2003 - 2:06 pm UTC

You're getting the error when you don't specify SYS.xmltype because you're on Oracle9i Release 1. In Release 2, you no longer have to specify SYS. For your query, try removing the (e.x) from the query... Not sure how/why that's there...

select sys.xmltype.extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
from t e;

Hope that helps!

How to insert into XMLType then

Nilendu, April 16, 2003 - 1:42 pm UTC

Tom,

I guess XMLType is a new 9i Rel. 2 feature.

We have been using CLOB for holding XML data too. We use DBMS_XMLSave.INSERTXML which inserts XML in the form of CLOB into the table.

But if we have XMLType column, how we insert the XML there?

I went to see spec for DBMS_XMLSave - but could not find INSERTXML has a XMLType argument.

Thanks,
Nilendu

9.0.1 feature

joe, April 16, 2003 - 3:32 pm UTC

from the 9.2 docs

Oracle9i Release 1 (9.0.1) introduced a new datatype, XMLType, to facilitate native handling of XML data in the database.

</code> http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96620/xdb04cre.htm#1656 <code>

Note:
In Oracle9i Release 1 (9.0.1), XMLType was only supported in the server in SQL, PL/SQL, and Java. In Oracle9i Release 2 (9.2), XMLType is also supported on the client side through SQL, Java, and protocols such as FTP and HTTP/WebDav.


Is XML a good idea to begin with?

Mikito Harakiri, April 16, 2003 - 8:18 pm UTC

How about

table REQUEST_DETAIL (
GROUP_TYPE integer,
GROUP_NAME varchar2,
...
)

?

XML has been promoted by document content folks who have no clue about data management. It's reincarnated EDI.

Tom Kyte
April 17, 2003 - 10:08 am UTC

XML is an excellent idea -- but like anything when taken to illogical extremes, can be made to look silly.

I've seen systems where people don't use subroutines (ok, in 21st century speak "methods"), they generate XML -- and soap it to eachother.

Why do something fast and efficient when you can do it "cool"...

Here I have to assume there is some logical reason they are storing XML in the database instead of using XMLSave to parse and load the XML as structured data.

Sonika, July 28, 2003 - 6:22 am UTC

Hi Tom,
understood that EXTRACT is used for searching in XML but
as in the above example, EXTRACT requires the full XPATH. I was trying to use wildcards and attributes in the XPATH.
I changed the XML to have a flag attribute.
<REQUEST_DETAIL>
<GROUP_TYPE flag="1">PR</GROUP_TYPE>
<GROUP_NAME flag="0">DATA ENGINEERING ASSET MANAG</GROUP_NAME>
<BUS_UNIT_ACRN flag="1">SCS-FCAT</BUS_UNIT_ACRN>
</REQUEST_DETAIL>
when I write the SQL : select e.request_detail('/REQUEST_DETAIL/*[@flag="1"]/text()').getStringVal()
from t_request e
This is the output which I got:
PRSCS-FCAT
The output gets clubbed as a single column. Is there any way that the output comes out as two different columns?
Thanks
Sonika

Tom Kyte
July 28, 2003 - 7:39 am UTC

getstringval by its very definition returns "a thing", not "some number of things"

think about it -- each and every row could/would return a different number of columns -- that won't work.

You would have to call getstringval for each attribute you wanted as a column

Thanks

Sonika, July 29, 2003 - 4:34 am UTC

Thanks Tom

..and 8.1.7

Steve Kiteley, November 18, 2003 - 9:13 am UTC

What is the best way to emulate this functionality (extracting a particular XML tag value from a clob) using Oracle 8.1.7?

Tom Kyte
December 08, 2003 - 10:11 am UTC

I asked Sean Dillon, our local XML technologist, to take a look at this here's what he had to say:
--

Hi Steve,

In Oracle8.1.7, your only option is the XML Developer's Kits on OTN (</code> http://otn.oracle.com/tech/xml/ <code>. In the XDKs, we have PL/SQL, C, Java and JavaBeans support for DOM & SAX parsers. That would be the best way to do what you're trying to do. The XDKs are backward compatible to 8.1.6, so you should have no problems downloading the latest kits into your 8.1.7 database and using the parsers.

Hope that helps.

XML Type not decoding xml properly?

Darren, October 21, 2004 - 11:06 am UTC

Tom,

why is xmltype not decoding ampersands?

SQL> create table t
  2  (
  3    cl clob
  4  )
  5  /

Table created.

SQL> insert into t values ('<root>test &amp; test</root>')
  2  /

1 row created.

SQL> select xmltype(t.cl).extract('/root/text()').getStringVal() 
  2    from t
  3  /

XMLTYPE(T.CL).EXTRACT('/ROOT/TEXT()').GETSTRINGVAL()
----------------------------------------------------------------------------
test &amp; test

SQL> 

shouldnt that be "test & test" ?



indeeed it doesnt like invalid XML..
SQL> insert into t values ('<root>test &test</root>');

1 row created.

SQL> select xmltype(t.cl).extract('/root/text()').getStringVal() 
  2    from t
  3  /
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00241: entity reference is not well formed
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 1



no rows selected

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

  

Tom Kyte
October 21, 2004 - 2:52 pm UTC

we are not an XML document viewer, that would be the job of that, we just extract text and give it to you.

i don't believe this to be improper behaviour personally -- but if you do, you can contact support and see if this is "correct" (seems correct and ok to me)

Ampersand in xml

Sasa, October 22, 2004 - 12:16 pm UTC

Hi Darren,
This is issue I posted several times and solution is to instead of extract function use extractvalue.
I will give you an example:
SELECT
xmlelement(
"TEST",
xmlforest(
id, testamp
)
).getstringval()
AS xmlpayload
FROM (SELECT 1 AS id, '1ST&2ND' AS testamp FROM dual) ;

and you have a such output:

<TEST><ID>1</ID><TESTAMP>1ST&amp;2ND</TESTAMP></TEST>

Using extractvalue (find in documentation , mentioned that is almost same with extract) you got next:

SELECT
extractvalue(value(d),'/TEST/TESTAMP/text()') as TESTAMP
FROM
TABLE(XMLSequence(xmltype('<TEST><ID>1</ID><TESTAMP>1ST&amp;2ND</TESTAMP></TEST>').extract('/TEST'))) d;


TESTAMP
-------
1ST&2ND

Hope that helps.
Regards,
Sasa


Tom Kyte
October 22, 2004 - 6:12 pm UTC

thanks!

Help regarding GROUP BY on CLOB datatype

Asim, August 17, 2008 - 9:55 pm UTC

Hi Tom,

I am looking for your help on this.

I am trying the following -
select b.app_name, a.qh_sql, count(*)
from nz_query_history a, nz_app_db b
where a.host_name='a.b.com'
and to_char(a.qh_tend,'yyyy-mm-dd') = '2008-08-04'
and a.host_name=b.host_name
and a.qh_database = b.db_name
group by b.app_name , a.qh_sql
order by count(*) desc;

Getting the error message - inconsistent datatype - expected got clob.

After googling sometime, some people suggested to use DBMS.SUBSTR function but looks like I can not use it for more than 4000 chars but my SQL query can be as long as 32767 characters.


Any suggestion on how do I handle this?

Thanks,
Asim

Tom Kyte
August 20, 2008 - 9:25 am UTC

you cannot group by on a lob

you'll need to find some other approach. for example, instead of storing just the sql query, maybe you store also the sql_id and group by sql_id (a hash of the sql statement)

or, you write your own "hash" function or use dbms_crypto to hash the long string into a short 'hash' to group by.

GROUP by on CLOB

Asim, August 22, 2008 - 5:33 pm UTC

Hi Tom,
Thanks for your reply.

This SQL statement is from datawarehouse appliance called Netezza so it does not have any SQL_ID. We are actually pulling the query history out of Netezza into Oracle and do some query audit.

Could you please give me an example how to use DBMS_CRYPTO? I have never used it before. All I have is a CLOB column having 32768 characters which needs to be grouped so that I could provide one statistics as most frequent queries?

Thanks,
Asim
Tom Kyte
August 26, 2008 - 7:41 pm UTC

dbms_crypto is documented. otn.oracle.com has all documentation. Calling it to generate a hash is really pretty straightforward.

use the hashing functions to create a "hopefully unique" signature.

manasa, February 16, 2011 - 3:22 am UTC

Hi Tom,
i have a similer question.
i am unig
pd.Content.value('(/BADirect/BADirectCSE/INARequestStatus)[1]', 'varchar(20)') this is working fine in SQL
i converted it to
select xmltype(pd.content).extract('/BADirect/BADirectCSE/INARequestStatus/text()').getstringval()
from gcsrept.ProductDetail pd
to make it compatable with oracle. but it is throwing an error
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00240: element-start tag is not well formed
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 1
please help me in resolving this.
Tom Kyte
February 16, 2011 - 8:40 am UTC

no idea what you are doing.

there is no example here whatsoever to work with.

xml

A reader, February 16, 2011 - 8:56 pm UTC


Clob Search

Deepak, May 28, 2013 - 2:40 pm UTC

Hi Tom,

We have a table column of CLOB type.

The values in this column are like...

ID_COL CLOB_COL
====== ========
10 A,B,C,D,E,...,X,Y,Z
20 A1,B1,C1,D1,E1,...,X1,Y1,Z1

Our query is required to return the rows matching a sub string of the CLOB value.

e.g.,

if we query for the sub string "D1" in the CLOB_COL it should return the row with ID_COL value 20.

The problem with this query is that it does a full table scan of the table as it searches the CLOB column.

We have thought of flattening the table by creating another table like the following.

ID_COL CLOB_SUBSTR_VAL
====== ===============
10 A
10 B
10 C
...
...
20 A1
20 B1


Need your help in understanding if there would be a better approach than this to avoid full table scan?

Tom Kyte
May 29, 2013 - 6:59 pm UTC

create a text index on it. use contains to search it.

now that said, it does look like someone is storing a delimited list in a clob - which means your existing table is wrong and your proposed fix is the actual and ONLY table you want....



similar problem

Pritish, June 18, 2013 - 2:21 pm UTC

set long 1000000000
set longchunksize 10000000

SET LINESIZE 30000
SET TRIMOUT ON
SET NUMWIDTH 40
SET HEADING OFF
--SET COLSEP '|'
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF6';

SELECT trigger_log_id || '|' || TO_CLOB(REPLACE(REPLACE(EXTRACT(response, '/ResponseFromAPI'), CHR(10), ''), CHR(13), '')) AS response
FROM klog_gc.t_trigger_log
WHERE update_date >= (
SELECT NVL(MAX(last_extract_date), TO_DATE('19000101', 'YYYYMMDD'))
FROM klog_batch.t_file_log
WHERE header = 'STG_TRIGGER_RESPONSE'
AND file_type_code = 'CDWETL'
);

Tom Kyte
June 18, 2013 - 4:04 pm UTC

similar problem to what exactly?

I sometimes wish I could rate "questions", this would get less than a half star.


to_clob only works in create table as select and insert as select, I presume that is the issue here - you cannot to_clob in a select like that.

similar problem

Pritish, June 18, 2013 - 2:23 pm UTC


Any solution for ora- 19011 error?
Tom Kyte
June 18, 2013 - 4:05 pm UTC

no creates
no inserts
no look

we'd sort of need a way to see what you see and reproduce the issue.

A reader, January 09, 2018 - 9:49 pm UTC

i used this
select xmltype(A.TXN_PAYLOAD).extract('/Transaction/UnitID/text()').getstringVal() from TALEND_ODS.POS_POSLOGU_PAYLOAD A.
and got Null values

Connor McDonald
January 10, 2018 - 12:52 am UTC

I got an error.

SQL> select xmltype(A.TXN_PAYLOAD).extract('/Transaction/UnitID/text()').getstringVal() from TALEND_ODS.POS_POSLOGU_PAYLOAD A;
select xmltype(A.TXN_PAYLOAD).extract('/Transaction/UnitID/text()').getstringVal() from TALEND_ODS.POS_POSLOGU_PAYLOAD A
                                                                                                   *
ERROR at line 1:
ORA-00942: table or view does not exist


which would make sense given that I don't have that table.

How to filter data in the XML?

MUKUNTHAN L, May 06, 2019 - 1:27 pm UTC

Hi Tom,

Thanks much for your support here. I have an extended scenario here. Let's say, my xml data is like this:

<?xml version="1.0" encoding="UTF-8"?>
<REQUEST_DETAIL>
<GROUP_ID>1</GROUP_ID>
<GROUP_TYPE>PR</GROUP_TYPE>
<GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>
<BUS_UNIT_ACRN>SCS-FCAT</BUS_UNIT_ACRN>
<PROJ_MGR_ID />
<PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>
<GROUP_ID>2</GROUP_ID>
<GROUP_TYPE>PZ</GROUP_TYPE>
<GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>
<BUS_UNIT_ACRN>FCAT-XYZ</BUS_UNIT_ACRN>
<PROJ_MGR_ID />
<PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>
</REQUEST_DETAIL>

Here, I have repeated the same XML data twice - for two rows of data. I have included a <GROUP_ID> to distinguish the rows. And for the 2nd row, I have updated the <GROUP_TYPE> value with PZ and <BUS_UNIT_ACRN> value with FCAT-XYZ.

Now, I need to fetch the <BUS_UNIT_ACRN> value wherever the <GROUP_TYPE> value is 'PZ'. Can I do a where condition to this extent? Please advise. Thanks.
Chris Saxon
May 10, 2019 - 8:39 am UTC

Both the GROUP_ID elements have the same parent. This makes it tricky to identify the corresponding BUS_UNIT_ACRN for a given GROUP_ID.

Nest each set of elements in their own GROUP (or whatever) element. You can then use XMLTable to extract the attributes as traditional rows and columns. And query them like a normal table:

with rws as (
  select xmltype ( '<?xml version="1.0" encoding="UTF-8"?>
<REQUEST_DETAIL>
  <GROUP>
    <GROUP_ID>1</GROUP_ID>
    <GROUP_TYPE>PR</GROUP_TYPE>
    <GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>
    <BUS_UNIT_ACRN>SCS-FCAT</BUS_UNIT_ACRN>
    <PROJ_MGR_ID />
    <PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>
  </GROUP>
  <GROUP>
    <GROUP_ID>2</GROUP_ID>
    <GROUP_TYPE>PZ</GROUP_TYPE>
    <GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>
    <BUS_UNIT_ACRN>FCAT-XYZ</BUS_UNIT_ACRN>
    <PROJ_MGR_ID />
    <PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>
  </GROUP>
</REQUEST_DETAIL>') doc
  from dual
)
  select x.*
  from   rws r, xmltable (
    '/REQUEST_DETAIL/GROUP' passing r.doc
    columns 
      GROUP_TYPE path 'GROUP_TYPE',
      BUS_UNIT_ACRN path 'BUS_UNIT_ACRN'
  ) x
  where group_type = 'PZ';

GROUP_TYPE   BUS_UNIT_ACRN   
PZ           FCAT-XYZ    

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here