Migrating a column from ntext to XML

After SQL Server 2005 migration, I'm trying to change my ntext column to XML type.

The "alter table TableName alter column XMLCol xml" command throw this error :

XML parsing: line 1, character 39, unable to switch the encoding

1. Do I have to reformat my nText column before the migration to XML type ?

2. Is there a way to create an XML data type with UFT-8 in SQL Server 2005 ?

Thank you for your help

Thank you for your help

the ntext column must contain well-formed xml to be converted. My guess is the the data in the ntext column looks something like:

<?xml version="1.0" encoding="utf-8"?><root/>

which specifies the wrong encoding for the the xml in an ntext column. If this is the case convert the column to varchar(max) first to fix the encoding, then to xml. For example:

alter table TableName alter column XMLCol varchar(max)
alter table TableName alter column XMLCol xml

If your ntest columns contain a mixture of

<?xml version="1.0" encoding="utf-8"?><root/>

and

<?xml version="1.0" encoding="utf-16"?><root/>

after alter the columns to varchar(max), update them to replace all the "utf-16" with "utf-8" before doing the alter to xml.

Dan AT pluralsight DOT com

|||

hi,

if you would be converting the column from ntext

and you have several hundereds of record in it already

it would be difficult to pinpoint the problem.

here's my suggestion

1. add a new column with XML data type. leave the old ntext column untouched

2. insert into the new column bby batch of 10, 20 or by hundred depending on the size of your table

3. what would be left are problematic not well formed xml

4. deal with the remaining data and insert to the new column

5. when everything is done. drop the old column

6. rename the new XML column with the previous column

regards,

joey

|||

Converting it to varchar(max) is still having the potential to lead to encoding issues or data corruption. Instead the column should be converted to varbinary(max) first and then to XML.

Best regards

Michael