XML data in ntext to xml data type

Question

I'm a rookie when it comes to XML data.

I currently have a database with xml data being stored in a ntext column and this database appears to be taking up too much space. I'm trying to improve the way the data is stored, to reduce the overall size of the DB.

The way I see it, I have two options:

• nvarchar(max)
• xml

I will need to test the two options above by importing some data into those columns.

The problem I am having is, the XML data in the ntext column is currently stored as utf-8. In order for me to import it into the XML data type column, I will need to CAST/CONVERT the data to UTF-16?

Is this right?

Solution

Storing you data as datatype XML has two major benefits:

1. since it's stored as a native XML, you can do things like XQuery on top of it
2. since it's stored as XML it's stored in an optimized (tokenized) way, taking up less space than what the equivalent nvarchar(max) column would use up.

To convert your existing NTEXT column: just do a CAST on it.

What results do you get frmo this:

SELECT 
   id, ntextColumn, CAST(ntextColumn AS XML)
FROM 
   dbo.YourTable

I am almost sure this will work - just like that. SQL Server doesn't support UTF-8 - so your data even in the ntext column is most likely not really stored as UTF-8 (it's already been converted to SQL Server's Unicode - UCS-2/UTF-16) so I don't see any issue with converting this to datatype XML, really.