How to fetch xml data (enclosed within some tags) from a table in oracle 11g

Question

i have a table A

id -------- name -------------------C (xml data)

1 -----------a---------------------<note> <to>AKI</to> <from>Jani</from> <heading>Reminder</heading> <body>Don't forget me this weekend!</body> </note>

2------------ b--------------------<note> <to>Tove</to> <from>Jani</from> <heading>Reminder</heading> <body>Don't forget me this weekend!</body> </note>

3------------ c--------------------<note> <to>Joe</to> <from>MARY</from> <heading>Reminder</heading> <body>Don't forget me this weekend!</body> </note>"

now i want to fetch value of <to> and <from> tags and enter them to other temporary table

I am working on oracle 11g

i tried using

insert into <your_temp_table>
select a.id, a.name, 
   extract(a.c, '/note/to').getStringVal() as to, 
   extract(a.c, '/note/from')getStringVal() as from 
 from A a;

but im getting error

ORA-00932: inconsistent datatypes: expected - got -

Any Suggestions ???

Rusty · Accepted Answer · 2014-10-01 12:21:14Z

1

insert into <your_temp_table>
select a.id, a.name, 
       extract(a.c, '/note/to').getStringVal() as to_value, 
       extract(a.c, '/note/from').getStringVal() as from_value 
  from A a;

edited Oct 1, 2014 at 12:21

answered Sep 30, 2014 at 13:47

Rusty

2,13812 silver badges13 bronze badges

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

3 Comments

Akshay Sapra Over a year ago

Ussing above querry im getting ORA-00932: inconsistent datatypes: expected - got - Can you suggest anything ?

Rusty Over a year ago

getStringVal() is for string values. If you have other type stored inside you need to use corresponding function. Please read reference: docs.oracle.com/cd/E11882_01/appdev.112/e40758/…

Rusty Over a year ago

I've just noticed that there was a typo - missing dot for from column. Btw from is reserved keyword, so it might be better to use another alias.

Ishaan S · Accepted Answer · 2019-09-19 01:15:17Z

THE PROBLEM The most common cause of this Oracle error occurs when you attempt to execute an operation of two different data types that are incompatible. Remediating these mismatched data types should be the first step taken. In addition, it may have occurred because the user tried to perform an operation on a database object that is not intended for normal use. Otherwise, an attempt may have been made to use an undocumented view.

THE SOLUTION Correct the error by changing the data types to ones that are compatible. You can use a function such as TO_NUMBER or TO_CHAR to correct the compatibility error. (You can find the full list of Oracle functions here.). To avoid getting this error in the future, make sure that data types are compatible against one another.

Collectives™ on Stack Overflow

How to fetch xml data (enclosed within some tags) from a table in oracle 11g

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related