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

Я хочу вставить произвольный XML в SQL Server. XML содержится в объекте XmlDocument.

Столбец, в который я хочу вставить, - это либо столбец nvarchar, ntext, либо xml (Если это облегчает вашу жизнь, вы можете выбрать, какой это тип. Действительно, это столбец xml.)

Прототип

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

}

Причина, по которой я спрашиваю, состоит в том, что я пытаюсь найти правильный способ превратить XmlDocument во что-то, что может принять база данных - обязательно сохраняя правильное кодирование:

Я должен убедиться, что кодировка, используемая во время вставки, совпадает с той, что использует база данных
Я должен синхронизировать элемент <?xml version="1.0" encoding="windows-1252"?>

Я знаю, что ntext, nvarchar или xml хранятся как UTF-16 внутри SQL Server. Поэтому я должен быть уверен, что данные SQL Server будут передаваться как UTF-16. Это не проблема для String в .NET, поскольку они являются юникодом UTF-16.

Вторая проблема - синхронизация атрибута кодирования - это более сложный способ взломать. Я должен выяснить, как найти элемент объявления через объект XmlDocument:

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

и настройте его на UTF-16

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

Моя наивная попытка (которая не удалась)

Игнорирование кодировки в декларации XML и просто выяснение, как сохранить что-либо в 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;
      }
   }
}

Это не получается, потому что sql, который я пытаюсь запустить, это:

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

Это потому, что XmlDocument.ToString() возвращает "System.Xml.XmlDocument". Взглянув на реализацию, он видит, что буквально вызывает:

this.GetType().ToString();

Помимо: Microsoft, похоже, старается изо всех сил, чтобы помешать вам от получения XML в виде строки - предположительно потому что это приводит к ошибкам (Но они не говорят нам, какие ошибки, почему это ошибки, или правильный путь преобразовать XmlDocument в String!)

Смотрите также

Ответ 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(); и бросить; линии только за пределами условного (если)