Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

More on XML and encoding

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)

Print | posted on Tuesday, July 06, 2010 9:40 AM | Filed Under [ SQL Server 2008 SQL Server 2005 ]

Feedback

Gravatar

# re: More on XML and encoding

Dude! Thank you sooooooooo very much for this. This was exactly what I needed. I'm creating an XML string in .NET, and inserting it into a SQL Server 2005 database, and it kept failing with the error you mentioned above. I added the N, and it works great!

Many thanks.
2/15/2011 4:37 PM | Troy
Gravatar

# re: More on XML and encoding

Brilliant...I was having an issue where I was dealing with mixed UTF-8 and UTF-16 records that I was trying to import in the same batch, so I was using:

REPLACE(SKU.SKUOptionList,'<?xml version="1.0" encoding="utf-8"?>','<?xml version="1.0" encoding="utf-16"?>')

which was then throwing an error. Changing it to this fixed every issue I was having:

REPLACE(SKU.SKUOptionList,N'<?xml version="1.0" encoding="utf-8"?>',N'<?xml version="1.0" encoding="utf-16"?>')

Thank you very much for pointing me in the right direction on this. Solved hours of frustration.
1/19/2012 5:49 PM | Derrick
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET