.NET: как вставить XML-документ в SQL Server

Ответ 1

Вы должны использовать SqlParameter. Я бы рекомендовал сделать это так:

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

и SQL должен выглядеть так:

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

А так как первым ребенком всегда является xml node, вы можете заменить кодировку следующим выражением.

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

В целом это будет выглядеть так:

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

Ответ 2

Самое простое решение - использовать атрибут OuterXml документа. Я столкнулся с вашим вопросом, пытаясь решить проблему при условии, что я не смог выполнить хранимую процедуру. OuterXml возвращает строку текста, которую вы ожидали получить от .Value или .ToString()

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

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

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

Ответ 3

Лучше поздно, чем никогда... Я думаю, вы ищете что-то вроде этого:

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

   using (DbCommand command = connection.CreateCommand())
   {
      command.CommandText = sql;
      command.Parameters.AddWithValue("XmlVal", new SqlXml(new XmlNodeReader(xmlToSave)));

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

Объект XmlNodeReader обходит и правильно кодирует XmlDocument (или любой другой XmlNode), а объект SqlXml инкапсулирует это в SqlDbType, подходящий для использования с параметром. Это безопаснее и, вероятно, более эффективно, чем использование строкового посредника.

Ответ 4

Просто хотел добавить эквивалент SQLConnection для решения Джонатана Оверхолта:

private void StoreXMLInDatabase(XmlDocument doc)
{
    // xmlvalue is the name of the database column you want to insert into
    String sql = "INSERT INTO [tablename] (xmlvalue) VALUES (@xmlvalue)";
    // In order to read out you connection string from app.config, remember first to add a reference to System.Configuration in references,
    // and set using System.Configuration; in the top of the file
    string connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;  
    using (SqlConnection conn = new SqlConnection(connString))
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        SqlTransaction transaction = conn.BeginTransaction();
        try
        {
            // Remember to specify the SqlTransaction *name* (transaction)
            SqlCommand cobj = new SqlCommand(sql, conn, transaction);
            cobj.CommandText = sql;
            cobj.Parameters.AddWithValue("xmlvalue", new SqlXml(new XmlNodeReader(doc)));
            cobj.ExecuteNonQuery();
            transaction.Commit();
        }
        catch (SqlException ex)
        {
            //2627 = inserting duplicate key fail. Lets the procedure continue although an identity insert fail occurs
            if (ex.Number == 2627)
            {
                transaction.Rollback();
            }
        }
    } // end using
}

Проверено нормально в Visual Studio 2017 и 2013 с .net до версии 4.6.2 и с MS SQL Server 2008 R2, 2012 и 2016

Если вставка идентификатора завершилась неудачно, и вы хотите остановить процедуру, просто вставьте строку после транзакции .Rollback() следующим образом:

throw;

Или вы можете остановить процедуру при любых обстоятельствах, установив транзакцию. Rollback(); и бросить; линии только за пределами условного (если)