Search code examples
sql-serversql-server-2005data-migration

SQL Server - trying to convert column to XML fails

I'm in the process of importing data from a legacy MySQL database into SQL Server 2005.

I have one table in particular that's causing me grief. I've imported it from MySQL using a linked server and the MySQL ODBC driver, and I end up with this:

Col Name          Datatype  MaxLen
OrderItem_ID       bigint      8
PDM_Structure_ID   int         4
LastModifiedDate   datetime    8
LastModifiedUser   varchar    20
CreationDate       datetime    8
CreationUser       varchar    20
XMLData            text       -1
OrderHeader_ID     bigint      8
Contract_Action    varchar    10
ContractItem       int         4

My main focus is on the XMLData column - I need to clean it up and make it so that I can convert it to an XML datatype to use XQuery on it.

So I set the table option "large data out of row" to 1:

EXEC sp_tableoption 'OrderItem', 'large value types out of row', 1

and then I go ahead and convert XMLData to VARCHAR(MAX) and do some cleanup of the XML stored in that field. All fine so far.

But when I now try to convert that column to XML datatype:

ALTER TABLE dbo.OrderItem
   ALTER COLUMN XMLData XML

I get this message here:

Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8077 which is greater than the allowable maximum row size of 8060. The statement has been terminated.

which is rather surprising, seeing that the columns besides the XMLData only make up roughly 90 bytes, and I specifically instructed SQL Server to store all "large data" off-row....

So why on earth does SQL Server refuse to convert that column to XML data??? Any ideas?? Thoughts?? Things I can check / change in my approach??

Update: I don't know what changed, but on a second attempt to import the raw data from MySQL into SQL Server, I was successfully able to convert that NTEXT -> VARCHAR(MAX) column to XML in the end..... odd..... anyhoo - works now - thanks guys for all your input and recommendations! Highly appreciated !

Solution

  • If you have sufficient storage space, you could try selecting from the VARCHAR(MAX) version of the table into a new table with the same schema but with XMLData set up as XML - either using SELECT INTO or by explicitly creating the table before you begin.

    PS - it's a side issue unrelated to your problem, but you might want to check that you're not losing Unicode characters in the original MySQL XMLData field by this conversion since the text/varchar data types won't support them.