INSERT XML into SQL Server 2008 database

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

  • How can you cancel a SQL Server execution process programmatically
  • What are some ways of accessing Microsoft SQL Server from Linux?
  • How do I find a default constraint using INFORMATION_SCHEMA?
  • SQL SERVER xml with CDATA
  • Suggestion on database design - multiple tables involved into a relation
  • Select the last row in a SQL table
  • 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.

    3 Solutions collect form web for “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 = '<?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);
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.