32

Hello I'm trying to insert some XML data into a table on SQL Server 2008. However I keep getting thrown this error;

XML parsing: line 1, character 39, unable to switch the encoding

The database column filemeta uses the XML datatype, and I've switch the encoding to UTF-16 which I believe is necessary for adding XML data.

INSERT INTO testfiles
  (filename, filemeta) 
VALUES 
  ('test.mp3', '<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--This is a test XML file--><filemeta filetype="Audio"><Comments /><AlbumTitle /><TrackNumber /><ArtistName /><Year /><Genre /><TrackTitle /></filemeta>');

Help, I'm stuck.

NB: I created the XML with XMLTextWriter.

wonea
  • 4,783
  • 17
  • 86
  • 139

3 Answers3

37

Yes, there are issues when you try to insert XML into SQL Server 2008 and the XML contains an encoding instruction line.

I typically get around using the CONVERT function which allows me to instruct SQL Server to skip those instructions - use something like this:

INSERT INTO testfiles
  (filename, filemeta) 
VALUES 
  ('test.mp3', CONVERT(XML, N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>......', 2));

It has definitely helped me get various encoded XML stuff into SQL Server.

See the MSDN docs on CAST and CONVERT - a bit down the page there's a number of styles you can use for CONVERT with XML and some explanations about them.

John Woo
  • 258,903
  • 69
  • 498
  • 492
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 5
    +1 this is probably the trickiest error in whole SQL XML story: mixing explicit encoding declarations with implicit encoding derived form the string type (ASCII or Unicode). No mortal stands any chance against this one... – Remus Rusanu Sep 02 '10 at 16:03
21

You just need to include N in front of your XML string to make it unicode.

INSERT INTO testfiles
  (filename, filemeta) 
VALUES 
  ('test.mp3', N'<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--This is a test XML file--><filemeta filetype="Audio"><Comments /><AlbumTitle /><TrackNumber /><ArtistName /><Year /><Genre /><TrackTitle /></filemeta>');
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
1

This worked for me without any errors:

DECLARE @input XML 
SET @input = N'<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--This is a test XML file--><filemeta filetype="Audio"><Comments /><AlbumTitle /><TrackNumber /><ArtistName /><Year /><Genre /><TrackTitle /></filemeta>'

INSERT INTO testfiles (filename, filemeta)
VALUES ('test.mp3', @input);
SimplyInk
  • 5,832
  • 1
  • 18
  • 27
Talha Imam
  • 1,046
  • 1
  • 20
  • 22