将XML数据分解为各种数据库列的最佳方法是什么？到目前为止，我主要使用像这样的节点和值函数：

INSERT INTO some_table (column1, column2, column3)
SELECT
Rows.n.value('(@column1)[1]', 'varchar(20)'),
Rows.n.value('(@column2)[1]', 'nvarchar(100)'),
Rows.n.value('(@column3)[1]', 'int'),
FROM @xml.nodes('//Rows') Rows(n)

但是我发现对于中等大小的xml数据来说，这变得非常慢。

解决方案

回答

我不确定什么是最好的方法。我使用了OPENXML构造：

INSERT INTO Test
SELECT Id, Data 
FROM OPENXML (@XmlDocument, '/Root/blah',2)
WITH (Id   int         '@ID',
      Data varchar(10) '@DATA')

为了加快速度，我们可以创建XML索引。我们可以专门为值函数性能优化设置索引。另外，我们可以使用类型更好的xml列。

回答

这不是答案，更多的是我刚刚遇到了同样的问题，我可以按照edg在评论中的要求给出数字。

我的测试有xml，导致244条记录被插入，因此有244个节点。

我正在重写的代码平均需要0.4秒才能运行。(运行10个测试，从0.56秒扩展到.344秒)性能不是重写代码的主要原因，但是新代码也需要执行或者更好。这个旧代码循环xml节点，并调用sp在每个循环中插入一次

新代码几乎只是一个sp。传递xml;切碎。

输入新代码后进行的测试表明，新sp的平均运行时间为3.7秒，几乎慢了10倍。

我的查询是此问题中发布的表格；

INSERT INTO some_table (column1, column2, column3)
SELECT
Rows.n.value('(@column1)[1]', 'varchar(20)'),
Rows.n.value('(@column2)[1]', 'nvarchar(100)'),
Rows.n.value('(@column3)[1]', 'int'),
FROM @xml.nodes('//Rows') Rows(n)

执行计划似乎表明，对于每列，sql server都在执行单独的"表值函数[XMLReader]"，返回所有244行，并使用嵌套循环(内部联接)将所有备份合并在一起。因此，在我要从大约30列中插入或者插入的情况下，这似乎分别发生了30次。

我将不得不转储此代码，我认为任何优化都不会克服这种固有速度缓慢的方法。我将尝试使用sp_xml_preparedocument / OPENXML方法，并查看性能是否更好。如果有人从网络搜索中遇到这个问题(就像我一样)，我强烈建议我们在SQL Server中使用这种类型的粉碎之前进行一些性能测试。

回答

有一个XML批量加载COM对象(.NET示例)

从MSDN：

You can insert XML data into a SQL
  Server database by using an INSERT
  statement and the OPENXML function;
  however, the Bulk Load utility
  provides better performance when you
  need to insert large amounts of XML
  data.

回答

我当前针对大型XML集(> 500个节点)的解决方案是使用SQL大容量复制(System.Data.SqlClient.SqlBulkCopy)，方法是使用DataSet将XML加载到内存中，然后将表传递给SqlBulkCopy(定义XML模式有助于)。

显然存在一个陷阱，例如不必要地使用DataSet并首先将整个文档加载到内存中。我想在将来做得更好，并实现自己的IDataReader绕过DataSet方法，但当前DataSet对于这项工作"足够好"。

基本上，对于这种类型的XML切碎性能低下的问题，我从来没有找到解决方案。由于类型化的xml查询本质上很慢，或者与事务和SQL Server日志有关，所以它可能很慢。我猜想类型化的xml函数从未设计用于非平凡的节点大小。

XML批量加载：我尝试这样做并且速度很快，但是我无法使COM dll在64位环境下正常工作，而且我通常会尝试避免不再受支持的COM dll。

sp_xml_preparedocument / OPENXML：我从没走过这条路，所以有兴趣了解它的表现。