CODEWITHSUNDEEP
sql-serverxmlutf-8
Konrad
Konrad

Reputation: 161

Converting UTF8 to uTF16 in SQL Server

I have an application that receives XML from some web service written in PHP and inserts it to SQL Server database. When I try to insert received XML that contains Polish diacritical characters, I get an error like this:

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 a XML type column.

I googled to find some solution and I 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 again to normal varchar, and then to XML, and insert it to my table, but looks like it does not changes any characters inside of this XML, it just changes type of variable and it didn't solve my problem.

I also found some guy's advice that it's possible to solve similar problem by writing a procedure that goes through loop for every character in variable (XML in my case) and manually change it's encoding, but this guy also said that it may work slow. Is this really the only option to solve my problem?

Upvotes: 4

Views: 11290

Answers (2)

Shiv Kumat
Shiv Kumat

Reputation: 11

for XML file , UTF-16 is not supported by SQL server 2008 R2 ,so for the xml file,which is starts with

when you parse this xml gives error

Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1 The error description is 'Switch from current encoding to specified encoding not supported.'.

to resolve the above error the easy step is to use the SQL replace function

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

I have worked on 3 procedures using an xml file,whenever i tried to use utf-16 XML parser gives error.

Always use utf-8 for SQL server 2008 R2

Upvotes: 1

Devart
Devart

Reputation: 122042

Try cast 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

Upvotes: 3

Related Questions