Issue with Chinese Unicode

• archana.mandiriappan

  SSC Veteran

  Points: 256

  More actions

  May 15, 2018 at 11:44 am

  #334701

  I tried many collations still the below sql returns ??????  , Please help.

  declare @titleEntryAggregateXML nvarchar(200),
@index1 int = 0

  Create table #element_value
(
   element_value nvarchar(200)
)

  --insert into #element_value
--select N'エミー賞を受賞'

  set @titleEntryAggregateXML='<MetadataTitleAggregateDTO>
<Details>
<MetadataTitleElementDTO element_value="エミー賞を受賞" />
</Details>
</MetadataTitleAggregateDTO>'

  EXEC sp_xml_preparedocument @index1 OUTPUT, @titleEntryAggregateXML

  insert into #element_value(element_value)
select element_value
FROM OPENXML(@index1, '/MetadataTitleAggregateDTO/Details/MetadataTitleElementDTO')
   WITH (element_value nvarchar(200))

  select element_value from #element_value order by element_value --collate Chinese_PRC_BIN2

  drop table #element_value


• sgmunson

  SSC Guru

  Points: 110640

  More actions

  May 15, 2018 at 12:04 pm

  #1990581

  archana.mandiriappan - Tuesday, May 15, 2018 11:44 AM

  I tried many collations still the below sql returns ??????  , Please help.

  declare @titleEntryAggregateXML nvarchar(200),
@index1 int = 0

  Create table #element_value
(
   element_value nvarchar(200)
)

  --insert into #element_value
--select N'エミー賞をå—賞'

  set @titleEntryAggregateXML='<MetadataTitleAggregateDTO>
<Details>
<MetadataTitleElementDTO element_value="エミー賞をå—賞" />
</Details>
</MetadataTitleAggregateDTO>'

  EXEC sp_xml_preparedocument @index1 OUTPUT, @titleEntryAggregateXML

  insert into #element_value(element_value)
select element_value
FROM OPENXML(@index1, '/MetadataTitleAggregateDTO/Details/MetadataTitleElementDTO')
   WITH (element_value nvarchar(200))

  select element_value from #element_value order by element_value --collate Chinese_PRC_BIN2

  drop table #element_value


  Have you tried adding that collation to the table column?   Without it, the data may be getting mashed by the collation.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Evgeny Garaev

  SSCertifiable

  Points: 7428

  More actions

  May 15, 2018 at 5:12 pm

  #1990614

  archana.mandiriappan - Tuesday, May 15, 2018 11:44 AM

  I tried many collations still the below sql returns ??????  , Please help.

  The client software you use may not support the language. Also it may appear that the font for that language is not installed.

  What would I recommend is to install localized Chinese version of the Management Studio - https://go.microsoft.com/fwlink/?linkid=873126&clcid=0x804 on a localized Chinese operating system - https://www.microsoft.com/en-us/evalcenter/evaluate-windows-10-enterprise and try if that does work.

• HanShi

  SSC-Dedicated

  Points: 33507

  More actions

  May 16, 2018 at 4:16 am

  #1990629

  archana.mandiriappan - Tuesday, May 15, 2018 11:44 AM

  I tried many collations still the below sql returns ??????  , Please help.

  declare @titleEntryAggregateXML nvarchar(200),
@index1 int = 0

  Create table #element_value
(
   element_value nvarchar(200)
)

  --insert into #element_value
--select N'エミー賞をå—賞'

  set @titleEntryAggregateXML='<MetadataTitleAggregateDTO>
<Details>
<MetadataTitleElementDTO element_value="エミー賞をå—賞" />
</Details>
</MetadataTitleAggregateDTO>'

  EXEC sp_xml_preparedocument @index1 OUTPUT, @titleEntryAggregateXML

  insert into #element_value(element_value)
select element_value
FROM OPENXML(@index1, '/MetadataTitleAggregateDTO/Details/MetadataTitleElementDTO')
   WITH (element_value nvarchar(200))

  select element_value from #element_value order by element_value --collate Chinese_PRC_BIN2

  drop table #element_value


  You need to set the value of variable @titleEntryAggregateXML as unicode using the N'' notation. You can check the value of the variable if you execute a "SELECT @titleEntryAggregateXML" immediately after the SET statement. Without the N'' notation this value displays already the questionmarks.

  set @titleEntryAggregateXML=N'<MetadataTitleAggregateDTO>
<Details>
<MetadataTitleElementDTO element_value="エミー賞をå—賞" />
</Details>
</MetadataTitleAggregateDTO>'

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **