Unable to convert a long datatype to xml in oracle

Question

I have an oracle column with a long data type that stores an xml. Now I want to perform the EXTRACT and EXTRACTVALUE functions on this column. For this purpose, I need to convert the long to xmltype. But when I do : xmltype.createxml(long_col_name), I get :illegal use of long datatype
I know long is deprecated, but it is legacy db.So...

EDIT: Upon the suggestion, i tried:

SELECT 
EXTRACTVALUE( XMLTYPE (to_lob(long_col_name)), xpath_str) as value_date
FROM table_1;

I get:

[Error] Execution (2: 24): ORA-00932: inconsistent datatypes: expected - got LONG

maybe first convert to CLOB - then to XML

Randy
– Randy

2012-08-17 16:08:52 +00:00
Commented Aug 17, 2012 at 16:08 — Randy
– Randy, Commented Aug 17, 2012 at 16:08

Fahd Hamad Al-Abdulqader · Accepted Answer · 2016-09-08 11:05:05Z

1

Check this link good example

in short you need to do the following:

with xml as 
    (select dbms_xmlgen.getxmltype('select long_col_name from table_1') as xml from dual)

    select extractValue(xs.object_value,'/ROW/long_col_name') as value_date
    from xml x, table(xmlsequence(extract(x.xml,'/ROWSET/ROW'))) xs
    where extractValue(xs.object_value,'/ROW/long_col_name') like '%xxxx%';

Hope this helps.

answered Sep 8, 2016 at 11:05

Fahd Hamad Al-Abdulqader

712 bronze badges

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

Comments

burnDB · Accepted Answer · 2012-08-17 17:21:59Z

0

you'd better to migrate the LONG column type to CLOB. It will also benefit you in future. then you can use xmltype on the CLOB column.

answered Aug 17, 2012 at 17:21

burnDB

542 bronze badges

1 Comment

Victor Over a year ago

Welcome to SO. Feel bad about downvoting...but your answer is not helpful to me.

Collectives™ on Stack Overflow

Unable to convert a long datatype to xml in oracle

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related