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)