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.
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.
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>');
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);