Unable to cast TEXT to XML in SQL Server

sql-server xml sql-server-2005

82,692

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

Solution 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

Solution 3

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

Solution 4

Replacing encoding="utf-8" to encoding="utf-16" worked for me :)

Solution 5

You need to change the encoding before casting to XML.

CAST (REPLACE(MyTextToCastToXML, 'utf-8', 'utf-16') AS XML)

View more solutions

82,692

Author by

the sandman

Updated on July 09, 2022

Comments

the sandman almost 2 years
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
```
marc_s over 12 years

Doesn't work either .... none of the four styles available works, because the input is basically Unicode, but the datatype it's stored in is non-Unicode
the sandman over 12 years

I wish i could mark both right, you and Marc_s both had methods that worked great. I upvoted yours though. Thanks again!
Elias Hossain over 12 years

Hello @thesandman, thank you for supporting, I've up voted you too. I tried by best to answer you properly in several ways. Thanks for your time.
Azimuth about 11 years

Getting ´illegal xml character´ error if I try this method. SQL Server 2008 R2. Any ideas?
marc_s about 11 years

@Azimuth: sounds like a new question - and be sure to post the sample XML !
marc_s over 10 years

@Azimuth: make that a question on SO! I'm sure some of the XML gurus will see it and respond to it ....
Azimuth over 10 years

@marc_s, I did: stackoverflow.com/questions/20116205/…
dakab almost 10 years

Casting back and forth didn’t work for me, but removing the explicit encoding="UTF-8" as suggested here did the trick.
Sateesh Pagolu about 7 years

This is an underrated answer.
Geoff Griswald about 3 years

Wish I could upvote this answer twice thank you so much