Unable to cast TEXT to XML in SQL Server

Question

Basically I have a column named XML that is of type TEXT; this cannot be changed for other reason, but I was wondering how I could cast it to XML.

It gives me an error

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

when trying to do this. Is there anyways around it to still get it formatted to XML? I'm really stuck at this point.

Data within column:

<?xml version="1.0" encoding="utf-16"?>
<Record>
     <UserGuid>c624a356-9f18-403c-b404-790e79034c7d</UserGuid>
</Record>

Here is the cast SQL code:

SELECT CAST(XML AS XML).value('(/Record/UserGuid)[1]', 'NVARCHAR(max)')
FROM tbl_Module_RequestForms_Items

Answer 1

Your problem is: you have XML with an encoding="utf-16", but your column is a non-Unicode column......

Assuming that you cannot change it to NTEXT either, you have to do two nested CAST to achieve what you're looking for:

SELECT 
    CAST(CAST(XML AS NTEXT) AS XML).value('(/Record/UserGuid)[1]', 'NVARCHAR(max)')
FROM 
    tbl_Module_RequestForms_Items

First, you need to cast to NTEXT (or NVARCHAR(MAX)), and then you have to cast that result to XML, before you can use it.

Tip: remove those "other reasons" and convert this to XML datatype if you really need to use it as XML .....

Answer 2

You should replace encoding="utf-16" to encoding="utf-8" or ''(blank) and then perform your operation.

a. Converting encoding="utf-16" to encoding="utf-8"

SELECT 
  CAST(
    REPLACE(CAST([xml] AS VARCHAR(MAX)), 'encoding="utf-16"', 'encoding="utf-8"')
  AS XML).value('(/Record//UserGuid/node())[1]', 'NVARCHAR(max)') as UserGuid
from tbl_Module_RequestForms_Items

b. Replacing encoding="utf-16" to ''(blank)

SELECT 
  CAST(
    REPLACE(CAST([xml] AS VARCHAR(MAX)), 'encoding="utf-16"', '')
  AS XML).value('(/Record//UserGuid/node())[1]', 'NVARCHAR(max)') as UserGuid
from tbl_Module_RequestForms_Items