Today, let's examine encoding with SQL Server and XML datatype.


DECLARE @Inf XML

SET     @Inf =  '<?xml version="1.0" encoding="utf-16"?>
                 <root>
                    <names>
                         <name>test</name>
                     </names>
                     <names>
                         <name>test1</name>
                     </names>
                 </root>
                '

SELECT  x.value('name[1]', 'VARCHAR(10)') AS Name
FROM    @Inf.nodes('/root/names') AS t(x)

If you try to run the code above, you will get an error message like this


Msg 9402, Level 16, State 1, Line 3
XML parsing: line 1, character 39, unable to switch the encoding

Why is that? If you change the encoding to UTF-8, the code works.
The solution is to know that UTF-16 works like UNICODE, and how do we denote UNICODE strings in SQL Server? Yes, by prefixing the string with N.

So this code works with UTF-16 encoding and you can happily continue to work.

DECLARE @Inf XML

SET     @Inf = N'<?xml version="1.0" encoding="utf-16"?>
                 <root>
                    <names>
                         <name>test</name>
                     </names>
                     <names>
                         <name>test1</name>
                     </names>
                 </root>
                '

SELECT  x.value('name[1]', 'VARCHAR(10)') AS Name
FROM    @Inf.nodes('/root/names') AS t(x)