Unable to cast TEXT to XML in SQL Server
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)', 'NVARCHAR(max)') FROM tbl_Module_RequestForms_Items
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)', '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 .....