SQL Server - trying to convert column to XML fails....

Question

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 !

Answer 1

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.

Answer 2

Can you ADD a new column of type xml?

If so, add the new xml column, update the table to set the new column equal to the XmlData column and then drop the XmlData column.

Edit
I have a table "TestTable" with a "nvarchar(max)" column.

 select * from sys.tables where name = 'TestTable'

This gives a result containing:

 [lob_data_space_id] [text_in_row_limit] [large_value_types_out_of_row]
 1                   0                   0

yet I can happily save 500k characters in my nvarchar(max) field.

What do you get if you query sys.tables for your OrderItems table?

If your [text_in_row_limit] is not zero, try this, which should convert any existing in-row strings into BLOBs:

exec sp_tableoption 'OrderItems', 'text in row', 0

and then try to switch from nvarchar(max) to xml.

From BOL,

Disabling the text in row option or reducing the limit of the option will require the conversion of all BLOBs; therefore, the process can be long, depending on the number of BLOB strings that must be converted. The table is locked during the conversion process.