CODEWITHSUNDEEP
sqlxmloracle-databaseoracle11g
Akshay Sapra
Akshay Sapra

Reputation: 496

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

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 ???

Upvotes: 0

Views: 144

Answers (2)

Ishaan S
Ishaan S

Reputation: 61

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.

Upvotes: 1

Rusty
Rusty

Reputation: 2138

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;

Upvotes: 1

Related Questions