asked on

How to use a specific XML tag value in where condition from Oracle CLOB

HI EXPERTS,

XMLS:

<?xml version="1.0" encoding="UTF-8"?>
<parameters>
<flag>update</flag>
</parameters>

<?xml version="1.0" encoding="UTF-8"?>
<parameters>
<flag>update2</flag>
</parameters>

<?xml version="1.0" encoding="UTF-8"?>
<parameters>
<flag>update3</flag>
</parameters>

select viewid, pfxml,extract(xmltype(pfxml),'/parameters/flag/text()') as flag from inf_views where VIEWDESC like '%Report%';

o/p:
id and flag value i am getting for all 3 mentioned xmls
i

now i want to get only xml that containse update2 value
<flag>update2</flag>

Pawan Kumar

Pls try this -

select viewid, pfxml ,flag from 
(
	select viewid, pfxml,extract(xmltype(pfxml),'/parameters/flag/text()') as flag from inf_views 
	where VIEWDESC like '%Report%'
) as u
WHERE flag = 'update2'

Open in new window

Pawan Kumar

Try this also-

select 
            viewid, pfxml,extract(xmltype(pfxml),'/parameters/flag/text()') as flag from inf_views 
Where 
            VIEWDESC like '%Report%' AND extract(xmltype(pfxml),'/parameters/flag/text()') = 'update2'

Open in new window

Pawan Kumar

edited my first comment..

select viewid, pfxml ,flag from 
(
	select viewid, pfxml,extract(xmltype(pfxml),'/parameters/flag/text()') as flag from inf_views 
	where VIEWDESC like '%Report%'
) 
WHERE flag = 'update2'

Open in new window

srikotesh

ASKER

i am getting inconsistent data
only middle/subquery is executing

Pawan Kumar

is this done? if not pls post some sample example?

srikotesh

ASKER

I AM GETTING ERROR
ERROR:
ORA-00932 inconsistent datatypes

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership