Query xml using extract over CLOB column in Oracle

Question

We are storing the xml in oracle database. The column type is CLOB. I could fetch data from the xml tag with below query. When i execute the query i am getting null.

XML in the column:

<input xmlns="http://google.com/testsystem" 
       xmlns:testsystem="http://test.com/testSystem"          
       xmlns:tns="http://google.com/testService/">
      <ProcessData>      
        <reviewYear>2014-2015</reviewYear>
      </ProcessData>
    </input>

Table name : test_table
Column name : input_xml
Column type : CLOB

Query:

select extract(xmltype(input_xml),'//reviewYear/text()').getStringVal() as data 
from test_table 
where id = 1;

Result:

DATA                  
------------------------------------

score 1 · Accepted Answer · 2014-05-29 09:46:03Z

1

you need to specify namespace, please, try next query

select extract(xmltype('<input xmlns="http://google.com/testsystem" 
       xmlns:testsystem="http://test.com/testSystem"          
       xmlns:tns="http://google.com/testService/">
      <ProcessData>      
        <reviewYear>2014-2015</reviewYear>
      </ProcessData>
    </input>'),'/input/ProcessData/reviewYear/text()', 'xmlns="http://google.com/testsystem" xmlns:testsystem="http://test.com/testSystem" xmlns:tns="http://google.com/testService/"').getStringVal() as data 
from dual

UPD.

for update try

select updatexml(xmltype('<input xmlns="http://google.com/testsystem" 
       xmlns:testsystem="http://test.com/testSystem"          
       xmlns:tns="http://google.com/testService/">
       <ProcessData>      
         <reviewYear>2014-2015</reviewYear>
       </ProcessData>
    </input>'), '/input/ProcessData/reviewYear/text()', '2013-2014', 
    'xmlns="http://google.com/testsystem" xmlns:testsystem="http://test.com/testSystem" xmlns:tns="http://google.com/testService/"').getclobval() as data 
from dual

edited May 29, 2014 at 9:46

answered May 29, 2014 at 9:11

user3278460

Sign up to request clarification or add additional context in comments.

3 Comments

prabu Over a year ago

How to update the tag if we have namespace? can you help with a sampl query? thanks in advance.

user3278460 Over a year ago

what tag do you want to change? tag or its value?

prabu Over a year ago

For example, i need to update <reviewYear>2014-2015</reviewYear> to <reviewYear>2013-2014</reviewYear>.

Collectives™ on Stack Overflow

Query xml using extract over CLOB column in Oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related