.NET: How to insert XML document into SQL Server

Question

i want to insert arbitrary xml into SQL Server. The xml is contained in an XmlDocument object.

The column i want to insert into is either nvarchar, ntext, or xml column (If it makes your life easier then you can pick which type it is. Really it's an xml column).

Prototype

void SaveXmlToDatabase(DbConnection connection,
      XmlDocument xmlToSave,
      String tableName, String columnName);
{

}

The reason i ask is because i'm trying to find the proper way to turn the XmlDocument into something the database can take - being sure to keep the encodings right:

• i have to make sure the encoding using during insert matches what the database takes
• i have to synchronize the <?xml version="1.0" encoding="windows-1252"?> element

i know ntext, nvarchar, or xml are stored as UTF-16 inside SQL Server. So i have to be sure to give the data to SQL Server as UTF-16. This isn't a problem for Strings in .NET, since they are unicode UTF-16.

The 2nd problem, synchronizing the encoding attribute, is a tougher nut to crack. i have to figure out how to find the declaration element through the XmlDocument object:

<?xml version="1.0" encoding="windows-1252"?>   (or whatever the encoding may be)

and adjust it to `UTF-16

<?xml version="1.0" encoding="UTF-16"?>

---

My nieve attempt (that fails)

Ignoring the encoding in the xml declaration, and just figuring out how to save anything into SQL Server:

void SaveXmlToDatabase(DbConnection connection,
      XmlDocument xmlToSave,
      String tableName, String columnName);
{
   String sql = "INSERT INTO "+tableName+" ("+columnName+") 
          VALUES ('"+xmlToSave.ToString()+"')";

   using (DbCommand command = connection.CreateCommand())
   {
      command.CommandText = sql;

      DbTransaction trans = connection.BeginTransaction();
      try
      {
         command.ExecuteNonQuery();
         trans.Commit();
      }
      catch (Exception)
      {
         trans.Rollback();
         throw;
      }
   }
}

This fails because the sql i try to run is:

INSERT INTO LiveData (RawXML) 
VALUES ('System.Xml.XmlDocument')

This is because XmlDocument.ToString() returns "System.Xml.XmlDocument". Peeking at the implementation, it see that it literally is call:

this.GetType().ToString();

Aside: Microsoft seems to have gone out of their way to prevent you from getting the Xml as a string - presumably because it leads to bugs (But they don't tell us what bugs, why they're bugs, or the right way to convert an XmlDocument into a String!)

See also

• C#/SQL - What’s wrong with SqlDbType.Xml in procedures ?
• How to convert XmlDocument to XmlReader for SqlXml data type.

Answer 1

You have to use an SqlParameter. I would recommend doing it like that:

command.Parameters.Add(
   new SqlParameter("@xml", SqlDbType.Xml) 
       {Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml
                       , XmlNodeType.Document, null)) })

and the SQL should look like:

String sql = "INSERT INTO "+tableName+" ("+columnName+") VALUES (@xml)";

And since the first child is always the xml node, you can replace the encoding with the following statement.

xmlToSave.FirstChild.InnerText = "version=\"1.0\" encoding=\"UTF-16\"";

All in all it would look like that:

void SaveXmlToDatabase(DbConnection connection,
      XmlDocument xmlToSave,
      String tableName, String columnName);
{
   String sql = "INSERT INTO "+tableName+" ("+columnName+") VALUES (@xml)";

   using (DbCommand command = connection.CreateCommand())
   {
      xmlToSave.FirstChild.InnerText = "version=\"1.0\" encoding=\"UTF-16\"";             
      command.CommandText = sql;
      command.Parameters.Add(
        new SqlParameter("@xml", SqlDbType.Xml) 
           {Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml
                       , XmlNodeType.Document, null)) });


      DbTransaction trans = connection.BeginTransaction();
      try
      {
         command.ExecuteNonQuery();
         trans.Commit();
      }
      catch (Exception)
      {
         trans.Rollback();
         throw;
      }
   }
}