Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1241
  • Last Modified:

Can I covert XML data type to varchar

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
harsha_james
Asked:
harsha_james
  • 2
1 Solution
 
momi_sabagCommented:
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
 
harsha_jamesAuthor Commented:
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
 
momi_sabagCommented:
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now