Unable to cast TEXT to XML in SQL Server

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

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 UserGuidfrom 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 UserGuidfrom tbl_Module_RequestForms_Items

Casting XML variable as NTEXT solves the problem CAST(CAST (XML AS NTEXT) AS XML).