Convert UTF8 to uTF16 in SQL Server

Question

I have an application that retrieves XML from some web service written in PHP and inserts it into a SQL Server database. When I try to paste the resulting XML, which contains Polish diacritical characters, I get this error:

XML parsing: line 2, character 703, illegal xml character

I tried to do something like this:

DECLARE @xml XML; SET @xml = '(here I paste some sample XML that contains diacritical characters)'; SELECT @xml = CAST(@xmlstr AS XML); INSERT INTO vos_DirectXML_ut(ValidXML,synchronization_time,synchronization_type,MethodName) VALUES(@xml,GETDATE(),@SynchroType,@method); 

ValidXML is an XML type column.

I found googled to find some solution, and found Utf8String: http://msdn.microsoft.com/en-us/library/ms160893(v=sql.90).aspx

I installed it and tried to convert XML to Utf8String, and then convert it to regular varchar again, and then to XML and paste into my table, but it looks like it does not change any characters inside this XML, it just changes the type of the variable and does not solve my problem.

I also found a couple of tips on how to solve a similar problem by writing a procedure that goes through a loop for each character in a variable (XML in my case) and manually changes its encoding, but this guy also said that it can work slow. Is this really the only way to solve my problem?

Answer 1

Try applying to UNICODE:

 DECLARE @xmlstr NVARCHAR(MAX) --<-- SELECT @xmlstr = N'(some sample XML that contains diacritical characters)'; --<-- N'' DECLARE @xml XML SELECT @xml = CAST(@xmlstr AS XML) INSERT INTO dbo.vos_DirectXML_ut ( ValidXML , synchronization_time , synchronization_type , MethodName ) SELECT @xml , GETDATE() , @SynchroType , @method 

Answer 2

for an XML file, UTF-16 is not supported by SQL Server 2008 R2, so for an xml file that starts with

when parsing this xml gives an error

Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1 Description of the error: "Switching from the current encoding to the specified encoding is not supported."

to fix the above error, a simple step is to use the SQL replace function

REPLACE ('@xmldata', 'utf-16', '') or REPLACE ('@xmldata', 'utf-16', 'utf-8')

I worked on 3 procedures using an xml file, whenever I tried to use the PHP utf-16 parser, you get an error.

Always use utf-8 for SQL Server 2008 R2