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)[1]', 'NVARCHAR(max)')
FROM tbl_Module_RequestForms_Items

Answers


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 .....


Need Your Help

Web Server Setup giving strange outputs

objective-c web-services http

Im trying to connect to a server from an apple app. The company I'm using for the server is awful and and promise they have set it up as required (but I'm not 100% convinced).

VSTO Word 2007/2010 Addin: positioning items in File menu

c# ms-word vsto office-addins

I have a VSTO Word addin created for Word 2007, using .NET 4.0. The addin works perfectly with both Word 2007 and 2010 versions.