.NET：如何将XML文档到SQL Server [英] .NET: How to insert XML document into SQL Server

问题描述

我想插入任意XML到SQL Server。 XML是包含在的XmlDocument 对象。

我要插入到该字段是 nvarchar的， NTEXT 或 XML 列（如果它使您的生活更轻松，那么你可以选择哪些类型，你真的是一个 XML 列）。

原型

 无效SaveXmlToDatabase（的DbConnection连接，
      XmlDocument的xmlToSave，
      字符串tableName值，字符串COLUMNNAME）;
{

}
 

我想问的原因是因为我试图找到合适的方法来打开的XmlDocument 弄成数据库可以采取 - 是一定要保持正确的编码：

• 我要确保插入过程中使用的编码相匹配的数据库需要什么
• 我要同步＆LT; XML版本=1.0编码=窗口1252＆GT; 元素

我知道 NTEXT ， nvarchar的或 XML 存储为 UTF-16 SQL Server内部。所以我必须确保给数据到SQL Server为UTF-16。这不是字符串 S在.NET中，一个问题，因为它们的是 UNI code UTF-16。

第二个问题，同步的编码属性，是一个难啃的骨头。我必须弄清楚如何找到通过的XmlDocument 对象的声明元素：

 ＆LT; XML版本=1.0编码=窗口1252＆GT？; （或任何的编码而定）
 

和它调整到'UTF-16

 ＆LT; XML版本=1.0编码=UTF-16＆GT？;
 

---

我的Nieve酒店尝试（失败）

忽略XML声明中的编码，只是搞清楚如何保存任何东西到SQL Server：

 无效SaveXmlToDatabase（的DbConnection连接，
      XmlDocument的xmlToSave，
      字符串tableName值，字符串COLUMNNAME）;
{
   字符串SQL =INSERT INTO+ tableName值+（+ COLUMNNAME +）
          VALUES（'+ xmlToSave.ToString（）+'）;

   使用（的DbCommand命令= connection.CreateCommand（））
   {
      command.CommandText = SQL;

      DbTransaction反式= connection.BeginTransaction（）;
      尝试
      {
         command.ExecuteNonQuery（）;
         trans.Commit（）;
      }
      赶上（例外）
      {
         trans.Rollback（）;
         扔;
      }
   }
}
 

这将失败，因为 SQL 我尝试运行是：

  INSERT INTO LiveData（RawXML）
VALUES（'System.Xml.XmlDocument）
 

这是因为 XmlDocument.ToString（）返回 System.Xml.XmlDocument 。偷看的实施，它看到，它确实是调用：

  this.GetType（）的ToString（）;
 

  

旁白：微软似乎已经走出自己的方式prevent您   从得到的XML作为字符串 -   presumably因为它导致的错误   （但他们不告诉我们什么错误，为什么   他们的错误，或右键的办法   转换的的XmlDocument 成   字符串！）

另请参见

• <一个href="http://stackoverflow.com/questions/574928/c-sql-whats-wrong-with-sqldbtype-xml-in-procedures">C#/SQL - 有什么不对SqlDbType.Xml手续
？
• <一个href="http://blogs.msdn.com/b/jongallant/archive/2007/01/30/how-to-convert-xmldocument-to-xmlreader-for-sqlxml-data-type.aspx"相对=nofollow>如何的XmlDocument转换的XmlReader的SQLXML数据类型。

解决方案

您必须使用的SqlParameter。 我建议做这样的：

  command.Parameters.Add（
   新的SqlParameter（@ XML，SqlDbType.Xml）
       {值=新SQLXML（新的XmlTextReader（xmlToSave.InnerXml
                       ，XmlNodeType.Document，NULL））}）
 

和SQL应该是这样的：

 字符串SQL =INSERT INTO+ tableName值+（+ COLUMNNAME +）VALUES（@xml）;
 

此外，由于第一个孩子始终是XML节点，你可以替换为以下语句编码。

  xmlToSave.FirstChild.InnerText =版本= \1.0 \编码= \UTF-16 \;
 

总之它看起来像：

 无效SaveXmlToDatabase（的DbConnection连接，
      XmlDocument的xmlToSave，
      字符串tableName值，字符串COLUMNNAME）;
{
   字符串SQL =INSERT INTO+ tableName值+（+ COLUMNNAME +）VALUES（@xml）;

   使用（的DbCommand命令= connection.CreateCommand（））
   {
      xmlToSave.FirstChild.InnerText =版本= \1.0 \编码= \UTF-16 \;
      command.CommandText = SQL;
      command.Parameters.Add（
        新的SqlParameter（@ XML，SqlDbType.Xml）
           {值=新SQLXML（新的XmlTextReader（xmlToSave.InnerXml
                       ，XmlNodeType.Document，NULL））}）;


      DbTransaction反式= connection.BeginTransaction（）;
      尝试
      {
         command.ExecuteNonQuery（）;
         trans.Commit（）;
      }
      赶上（例外）
      {
         trans.Rollback（）;
         扔;
      }
   }
}
 

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.

解决方案

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

这篇关于.NET：如何将XML文档到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！