Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Can I covert XML data type to varchar

Posted on 2011-09-05
3
Medium Priority
?
1,230 Views
Last Modified: 2012-05-12
Hi All,
Can I convert XML data type from table to varchar, by recreating table.
I tried with following commands,
EXPORT TO ABC.ixf' OF ixf select * from ABC
drop table ABC;
Create table ABC with Varchar column.
LOAD CLIENT FROM ABC.ixf' of ixf insert into ABC;

But While loading I m getting error as
SQL3088N  The source column specified to be loaded into database column "1" is not compatible with the database column, but the database column is not nullable.

Please advise me about Is it possible to convert XML to varchar.
0
Comment
Question by:harsha_james
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 36483527
how about trying

create table abc2 (col varchar(...))

insert into abc2
select cast(xml_column as varchar)
from abc

drop abc
renambe abc2 to abc
0
 

Author Comment

by:harsha_james
ID: 36486726
Thanks Momi,
This trick solved my problem.
But is it safe to do this because on Production system, If XML data exceeds varchar limit i.e. 32672  chars then My insert will fail.
Thanks for your support.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36487812
you are correct, but you wanted to convert it to varchar
run a test with an xml longer than 32672 and see what happens
you can either try to cast it to clob, or break it to several varchar values or just truncate after the first 32672 bytes
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question