sql server 2005 xml update query chinese characters

Question

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).