SQL Server 2005 XML - Msg 9402 (unable to switch the encoding)

Had a problem converting XML from a column of type text to type xml. Someone else luckily has had the same problem:
http://devio.wordpress.com/2008/03/04/retrieving-xml-data-in-sql-server-2005/.

The solution was to convert the column from type TEXT to NVARCHAR(max), and get rid of the "encoding" part of the root tag, something like this (slightly altered from the example mentioned above):

SELECT ID, CAST(
REPLACE(CAST(XmlTextColumn AS NVARCHAR(MAX)), 'encoding="UTF-16"', '')
AS XML).query('xpath to nodes') AS Node
FROM TableName

Another way to solve this problem is by adding a computed XML column:

ALTER TABLE [MyTable]
    ADD MyXMLColumn AS CAST(REPLACE(CAST(MyXMLStoredAsText AS NVARCHAR(MAX)), 'encoding="UTF-16"', '') AS XML)
    PERSISTED

If you add the persisted option, the value will be persisted with the table data, which will give better performance for read operations (slightly worse performance for insert/update operations).