在SQL Server 2008中，当从xml数据类型转换为nvarchar时，Unicode文本会被破坏吗?

In our database the information about translations are stored in a XML column and we extract it using .value() function of xml datatype. We use nvarchar(100) as target data type, but the values get corrupted after conversion.

在我们的数据库中，关于翻译的信息存储在一个XML列中，我们使用XML数据类型的.value()函数提取它。我们使用nvarchar(100)作为目标数据类型，但是值在转换后会被破坏。

Here is the sample code:

下面是示例代码:

DECLARE @x Xml
SET @x =
'<TRANSLATIONS>
    <TRANSLATION Lang="de">Probenname</TRANSLATION>
    <TRANSLATION Lang="pt">Tipo da Amostra</TRANSLATION>
    <TRANSLATION Lang="ru">Вид пробы</TRANSLATION>
    <TRANSLATION Lang="eo">Testaĵnomo</TRANSLATION>
</TRANSLATIONS>'

SELECT TR.lentry.value('@Lang','varchar(2)') AS Lang,
       TR.lentry.value('.','nvarchar(100)') AS Text    
FROM @x.nodes('/TRANSLATIONS/TRANSLATION') AS TR(lentry)

Here is the output we get:

这是我们得到的输出:

Lang Text
---- ----------
de   Probenname
pt   Tipo da Amostra
ru   ??? ?????
eo   Testajnomo

As one might see, the Cyrillic entry is completely mingled whereas the Esperanto entry has lost its diacritic sign.

正如你可能看到的，西里尔语入口是完全混合的，而世界语入口已经失去了它的变音符号。

Any idea how one can repair it?

你知道怎么修理吗?

1 个解决方案

#1

Your xml constant is varchar so the data is lost before the parse. Add the N prefix to make it varchar

xml常量是varchar，因此数据在解析之前会丢失。添加N前缀使其varchar

DECLARE @x Xml

-- broke
SET @x =
'<TRANSLATIONS>
    <TRANSLATION Lang="de">Probenname</TRANSLATION>
    <TRANSLATION Lang="pt">Tipo da Amostra</TRANSLATION>
    <TRANSLATION Lang="ru">Вид пробы</TRANSLATION>
    <TRANSLATION Lang="eo">Testaĵnomo</TRANSLATION>
</TRANSLATIONS>';
SELECT @x;

-- fixed
SET @x =
N'<TRANSLATIONS>
    <TRANSLATION Lang="de">Probenname</TRANSLATION>
    <TRANSLATION Lang="pt">Tipo da Amostra</TRANSLATION>
    <TRANSLATION Lang="ru">Вид пробы</TRANSLATION>
    <TRANSLATION Lang="eo">Testaĵnomo</TRANSLATION>
</TRANSLATIONS>';
SELECT @x;

SELECT TR.lentry.value('@Lang','varchar(2)') AS Lang,
       TR.lentry.value('.','nvarchar(100)') AS Text    
FROM @x.nodes('/TRANSLATIONS/TRANSLATION') AS TR(lentry);