sql server 2005 xml update query chinese characters

Question

Hi I am trying to update the value of a tag with Chinese characters. However it does not add the chinese characters. Instead it adds "???" . e.g. update table set col.modify('replace value of (/tag/text())[1] with "我"') where .. Any help is much appreciated

thanks Ben

Answer 1

For international characters like this you usually want to use N'this is my data' to signify it is unicode/nchar. Otherwise it is treated as char, and I'm assuming the db collation can't support the characters you are submitting. Try to just do

select 'my chars'

and see if you still get question marks, I would assume so.

EDIT - here is an example that confirms my suggestion works:

declare @x xml
set @x = N'<tag>abc</tag>'
set @x.modify (N'replace value of (/tag/text())[1] with "我"') 
select @x

I see the symbol when I select out the xml, and I verified that before and after the character is 0x1162 (proves the data is intact).

Answer 2

Are you using unicode strings? Those start with a capital N, like:

update yourtable 
set yourvalue = N'your chinese characters' 
where id = yourid

Answer 3

i want to change 150 col plz mail i want to change 150 ip no ip-1100023456 i want to change 2200023456

Answer 4

I am trying to search comments contains Chinese characters. When I pass keyword = 我的服装 into below stored procedure from C# coding. It return 0 record.

I find that @keyword ="????".

Please help me how to retrieve data from stored procedure in multiple language. Thank you

ALTER PROCEDURE [dbo].[_UserInfo_GetBykeyword] ( @keyword nvarchar(1024)
) AS

            SELECT
                [comment]

            FROM
                [dbo].[UserInfo]
            WHERE
              [comment]= @keyword

Answer 5

I have the same problem.. I want to save the chinese, and arabic caracteres, but when i want to inpu only add blocks , and when it's show only have ????? I add N' before. like the moderatos said, but dont works. And, My field is nvarchar, but nothing