Store Unicode data in SQL Server 2008 XML datatype

Question

I have XML data type as a table column. I have stored some XML text inside it. The XML text contains some Hebrew characters, but after I store it in the table, all the Hebrew characters are turned to “?”. What can I do to fix it?

This is my UPDATE statement:

update db_owner.Screen_Template set template_xml = 
'
    
        
            
                
                    
                
                
                    
                        
                            
                                
                                    
                                        
                                            
                                        
                                        
                                            
                                        
                                        
                                            
                                        
                                        
                                            
                                        
                                        
                                            
                                        
                                        
                                            
                                        
                                        
                                            

                                            
                                        
                                    
                                
                            
                            
                                
                                
                                    
                                    
                                    
                                
                                
                                
                                    
                                    
                                
                                
                            
                        
                    
                
                
                    
                        
                            
                                
                                
                                    
                                    
                                    
                                    
                                
                                
                                    
                                    
                                
                                
                                
                                
                                    
                                    
                                    
                                    
                                    
                                
                                
                                
                                
                                
                                
                                
                                    
                                    
                                    
                                
                            
                        
                    
                
            
            
                
                    
                    
                    
                    
                    
                    
                    
                    
                    
                
            
        
        
    '
where UID like '4'

and the SELECT statement:

SELECT * FROM Screen_Template WHERE env LIKE '" + env + "' AND OpCo LIKE '" + opco + "' AND UID LIKE '" + uid + "'"

Anthony Faull · Accepted Answer

The xml data type has full support for Unicode and will not convert letters to question marks. Something else is going on. It could be one of the following reasons.

Your OS does not have the correct fonts for Hebrew.
The INSERT part of your client app is corrupting the source data into a single-byte character set (e.g using varchar instead of nvarchar).
The SELECT part of your client app is corrupting the table data into a single-byte character set (e.g using varchar instead of nvarchar).

Update based on poster's new code sample:

Your INSERT statement is corrupting Unicode text by using single-byte string constants e.g. 'text' instead of Unicode constants e.g. N'some text'. You need to prefix all string literals with a capital N to ensure uNicode is used.

UPDATE db_owner.Screen_Template
SET template_xml = 
N' ... '

Store Unicode data in SQL Server 2008 XML datatype

Answers (1)

Related Questions