Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

XML Query on a CLOB Column

326809Dec 21 2010 — edited Dec 21 2010
I am trying to query a column which is of CLOB data type,in Oracle, and My Data is stored something like this

<Result><Institution>XXX</Institution><Application>XXX</Application><ID>123123123</ID><SequenceID>MSGID123123</SequenceID><DateTime></DateTime></Result>

I am trying to run the xquery on this like this

SELECT Message FROM LOGS
('for $i in ora:view("LOGS")
where $i/Result/Institution/ = 'XXX'
return $i/Result/Institution/
PASSING :Institution AS 'XXX').

I either get exception that "missing right parenthesis" or doesnt execute at all.

My Table "LOGS" as the Columns Message as CLOB.

Any Ideas how to query on this kind of columns in a table in Oracle using XQuery?
Cheers
Sridhar

Comments

326809
I tried using this query

SELECT *
FROM logs
WHERE EXTRACT(message, '/Result/Institution/text()') = 'XXX'; and I get the following error "ORA-00932:inconsistent datatypes: expected - got - "

Cheers
Sridhar
odie_63
Hi,

First of all, please always give your database version (all digits from "select * from v$version").

Oracle XML support is achieved through the XMLType datatype, so you must first convert any string variable (CLOB, VARCHAR2) holding XML data to that type in order to use XML-related functions.

Now, to the point, if you want to check the existence of a particular node, you can try the following : 
-- if Institution may occur multiple times : 
select message
from logs
where existsnode(xmltype(message), '/Result[Institution="'||:p_institution||'"= 1
;

-- if only one Institution element is expected : 
select message
from logs
where extractvalue(xmltype(message), '/Result/Institution') = :p_institution
;
And the 11g equivalent : 
select message
from logs
where xmlexists('$d/Result[Institution=$p]' passing xmltype(message) as "d", :p_institution as "p")
;

select message
from logs
where xmlcast(
        xmlquery('$d/Result/Institution' passing xmltype(message) as "d" returning content)
        as varchar2(30)
      ) = :p_institution
;
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 18 2011
Added on Dec 21 2010
#xml, #xquery
2 comments
3,320 views