.NET:如何将 XML 文档插入 SQL Server

2022-01-23 00:00:00 xml sql-server-2008 sql-server ado.net xml-serialization

我想将任意 XML 插入 SQL Server.XML 包含在 XmlDocument 对象中.

我要插入的列是 nvarchar、ntext 或 xml 列(如果它使您的生活更轻松，那么您可以选择它是哪种类型.实际上它是一个 xml 列.)

原型

void SaveXmlToDatabase(DbConnection 连接，XmlDocument xmlToSave,字符串表名，字符串列名)；{}

我问的原因是因为我试图找到将 XmlDocument 转换为数据库可以采用的正确方法 - 确保编码正确:

我必须确保插入期间使用的编码与数据库使用的编码相匹配
我必须同步 <?xml version="1.0" encoding="windows-1252"?> 元素

我知道 ntext、nvarchar 或 xml 在 SQL Server 中存储为 UTF-16.所以我必须确保将数据以 UTF-16 格式提供给 SQL Server.这对于 .NET 中的 String 来说不是问题，因为它们是 unicode UTF-16.

第二个问题，同步编码属性，是一个更难破解的难题.我必须弄清楚如何通过 XmlDocument 对象找到声明元素:

<?xml version="1.0" encoding="windows-1252"?>(或任何编码可能)

并将其调整为 UTF-16

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

<小时>

我天真的尝试(失败了)

忽略 XML 声明中的编码，只是弄清楚如何将任何内容保存到 SQL Server 中:

void SaveXmlToDatabase(DbConnection 连接，XmlDocument xmlToSave,字符串表名，字符串列名)；{String sql = "INSERT INTO "+tableName+" ("+columnName+")值 ('"+xmlToSave.ToString()+"')";使用 (DbCommand 命令 = connection.CreateCommand()){command.CommandText = sql;DbTransaction trans = connection.BeginTransaction();尝试{command.ExecuteNonQuery();trans.Commit();}捕捉(例外){反式回滚()；扔;}}}

这失败了，因为我尝试运行的 sql 是:

插入 LiveData (RawXML)值('System.Xml.XmlDocument')

这是因为 XmlDocument.ToString() 返回System.Xml.XmlDocument".查看实现，它看到它确实在调用:

this.GetType().ToString();

<块引用>

旁白:微软似乎已经竭尽全力阻止你从获取 Xml 作为字符串 -大概是因为它会导致错误(但他们没有告诉我们什么错误，为什么它们是错误，或者是正确的方法将 XmlDocument 转换为字符串！)

另见

C#/SQL - SqlDbType.Xml 中有什么问题程序?
如何将 XmlDocument 转换为 SqlXml 数据类型的 XmlReader.

解决方案

你必须使用 SqlParameter.我建议这样做:

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

SQL 应该是这样的:

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

而且由于第一个子节点始终是 xml 节点，因此可以将编码替换为以下语句.

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

总而言之，它看起来像这样:

void SaveXmlToDatabase(DbConnection 连接，XmlDocument xmlToSave,字符串表名，字符串列名)；{String sql = "INSERT INTO "+tableName+" ("+columnName+") VALUES (@xml)";使用 (DbCommand 命令 = connection.CreateCommand()){xmlToSave.FirstChild.InnerText = "版本="1.0" 编码="UTF-16"";command.CommandText = sql;命令.参数.添加(新的 SqlParameter("@xml", SqlDbType.Xml){值 = 新 SqlXml(新 XmlTextReader(xmlToSave.InnerXml, XmlNodeType.Document, null)) });DbTransaction trans = connection.BeginTransaction();尝试{command.ExecuteNonQuery();trans.Commit();}捕捉(例外){反式回滚()；扔;}}}

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 naive 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 calling:

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!)

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

原型

我天真的尝试(失败了)

另见

Prototype

My naive attempt (that fails)

See also