INSERT XML into SQL Server 2008 database

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