asked on

extract a specific XML tag value from Oracle CLOB field of a column

Hi Experts,

I want to get xml tag value from clob column.
this tag value contains alpha numeric example-abc1234

i have tried like this and it is not working for me

select (xmltype(requestxml).extract('/parameters/filters_multitext/text()')).getStringVal() as test from views

can some one suggest how to get the value

sample xml:
<?xml version="1.0" encoding="UTF-8"?><request><filters_multitext>abc - 098382</filters_multitext></request>

Thanks,

Sean Stuber

You have the right idea, but your path references tags that don't exist.

select (xmltype(requestxml).extract('/request/filters_multitext/text()')).getStringVal() as test from views

srikotesh

ASKER

Hi

select (xmltype(requestxml).extract('/request/filters_multitext/text()')).getStringVal() as test from views

while executing the script getting the below error
ORA-06502:PL/SQL:numeric or value error
ORA-06512:at SYS.XML type

Sean Stuber

I tested with the sample you posted and it worked.

Please post the clob that failed.

Looks like you're getting an error converting the text into xmltype.

srikotesh

ASKER

Hi sdstuber,

select (xmltype(requestxml).extract('/request/filters_multitext/text()')).getStringVal() as test from views where viewid=472;

when i add where condition it is working. i am getting the expected result.

but i want to fetch filters_multitext tag value from the entire table.

few of the requestxml doesnt have this tag -->filters_multitext

how to handle null check here

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

what version of database are you using? 12.1.0.2? 12.2.0.1? 18.3.0.0? etc.

srikotesh

ASKER

thanjs