Unable to cast TEXT to XML in SQL Server
Replacing encoding="utf-8" to encoding="utf-16" worked for me :)
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 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
Casting XML variable as NTEXT solves the problem
CAST(CAST (XML AS NTEXT) AS XML)
.