Insert a XML with Chineese strings in Table

• M-Hol

  SSC Eights!

  Points: 923

  More actions

  September 7, 2015 at 10:57 am

  #311413

  Hi, i have a little problem when i try to insert XML with chineese strings

  this is My Table

  CREATE TABLE [dbo].[TABLE1](

  [XXXX] [varchar](255) NULL,

  [Data] [xml] NULL,

  [Last_modified] [datetime] NULL

  ) ON [PRIMARY]

  GO

  this is Extract of the XML

  <IDC>亮块(DS3或DS4)-清理/维修显像滚筒</IDC>

  When i try to insert the XML in the Table, the information change like this

  <IDC>??(DS3?DS4)-??/??????</IDC>

  any idea ? how can i resolve my problem ?

• Eirikur Eiriksson

  SSC Guru

  Points: 182916

  More actions

  September 7, 2015 at 11:37 am

  #1825011

  Quick suggestion, use N'' prefix, then the SQL Server automatically uses UTF-16

  😎

  USE tempdb;

  GO

  SET NOCOUNT ON;

  IF OBJECT_ID(N'dbo.TABLE1') IS NOT NULL DROP TABLE dbo.TABLE1;

  CREATE TABLE dbo.TABLE1

  (

  XXXX varchar(255) NULL

  ,Data xml NULL

  ,Last_modified datetime NULL

  );

  INSERT INTO dbo.TABLE1(XXXX,Data,Last_modified)

  VALUES ('INSERT THIS AS UTF-16',N'<IDC>??(DS3?DS4)-??/??????</IDC>',GETDATE());

  SELECT

  T1.XXXX

  ,T1.Data

  ,T1.Last_modified

  FROM dbo.TABLE1 T1;

  Results

  XXXX Data Last_modified

  ----------------------- ------------------------------------------ -----------------------

  INSERT THIS AS UTF-16 <IDC>??(DS3?DS4)-??/??????</IDC> 2015-09-07 18:42:00.393

• M-Hol

  SSC Eights!

  Points: 923

  More actions

  September 7, 2015 at 11:41 am

  #1825012

  your right, thanks ...