Problem selecting chinese characters from an XML

Question

Problem selecting chinese characters from an XML

drae

Grasshopper

Points: 16
More actions
June 5, 2009 at 8:18 am

#205654

I'm trying to select information from an XML document that contains Chinese characters in it, the XML is passed into the stored proc fine, but it seems that selecting it out causes the characters to get converted to ????s
Here's my query:
SELECT Commands.Command.value('@N', 'NVARCHAR(400)') [Name]
FROM @sampleXml.nodes('/Cs/CP') AS Commands(Command)
Here's a snippet of the XML:
exec dbo.procBracketedConceptExtractionBatchUpdateFromXml
'
...
Am I missing something? My DB Collation is set to Latin1_General_BIN2.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

Am I missing something? My DB Collation is set to Latin1_General_BIN2.

You are getting character conversion because Latin BIN is binary sort it is the fastest sort but if that is what you need then you have to use Chinese BIN because the Chinese alphabet is more than 2000 character while the Latin alphabet is 26 characters. So you data needs to be in Nvarchar with the version of Chinese you are using collation either on the column level or table level.

http://msdn.microsoft.com/en-us/library/ms143508.aspx

Kind regards,
Gift Peddie

drae Grasshopper Points: 16 More actions · Answer 2

Thanks for the reply... I have a bit of an update, it looks like if I run the query with an 'N' in front of the XML data it works fine. Now how do I specify that N from within C#?

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

In ADO.NET use a Command object to either consume .sql or stored proc and you also have the alternative of adding the Chinese collation in DML code in 2005/8.

Kind regards,
Gift Peddie