从 XML RAW 数据批量插入 SQL Server 2008 R2

Question

我有一个如下所示的 XML 结构：

<tables>
  <table name="tableName1">
    <row ID="34" col1="data" col2="dom" />
    <row ID="35" col1="data2" col2="dom2" />
  </table>
  <table name="tableName2">
    <row ID="1" col1="data" col2="dom" col3="item1" />
    <row ID="3" col1="data2" col2="dom2" col3="item2" />
    <row ID="7" col1="data4" col3="item3" />
  </table>
  ...
<tables>

基本上，表节点包含通过选择 FOR XML RAW 创建的 RAW 数据。

现在我希望执行相反的操作：读取 XML 并将数据插入到 SQL Server 2008 R2 数据库的相应表中。然而，我希望加载过程是健壮的，这意味着如果列名和表名将来发生变化，我不想弄乱它们。我需要从表节点的 @name 属性读取表名称并将数据插入到 节点中的属性指定的列中的过程。我想到了一个存储过程，它获取 XML 作为输入并完成其余的工作。

数据量约为。 70 个表，行数从 10 到 30 000 行不等，总共不超过 100 000 行。我需要尽可能高效地完成，批量加载是最好的。

该过程不应该处理外键，因为 XML 内的表顺序是这样构建的，以便可以通过依次加载一个表来保持 FK 约束就位。

但是，每个表中都有标识列，因此我必须

SET Identity_Insert ON and SET Identity_Insert OFF

在处理每个表之前和之后进行处理。我还需要在插入所有行后重新设定每个表的种子。哦，我需要在事务中完成整个工作，以便在出现问题时可以回滚。

您建议我走哪条路：我应该继续使用 T-SQL 还是尝试用 CLR SQL 编写 SP？我应该使用 XQuery 还是可以使用一些批量插入方法？

感谢您的帮助！

Answer 1

基本上，您必须循环遍历 XML 并根据结果集编写查询。

尝试这样开始：

declare @i int;
declare @x xml;

------
SELECT @x = N'
<tables>
  <table name="tableName1">
    <row ID="34" col1="data" col2="dom" />
    <row ID="35" col1="data2" col2="dom2" />
  </table>
  <table name="tableName2">
    <row ID="1" col1="data" col2="dom" col3="item1" />
    <row ID="3" col1="data2" col2="dom2" col3="item2" />
    <row ID="7" col1="data4" col3="item3" />
  </table>
</tables>';


exec sp_xml_preparedocument @i output, @x


select ID, col1, col2
from OpenXml(@i, '/tables/table/row')
with (ID int, col1 nvarchar(30), col2 nvarchar(30))

exec sp_xml_removedocument @i

它将为您提供需要插入数据的列的列表（您可以获取前一级的表名称，只需更改 SQL）

34  data    dom
35  data2   dom2
1   data    dom
3   data2   dom2
7   data4   NULL

您接下来需要做的是编写在此结果集上循环的语句。

仅供参考，您不需要编写 XML，您可以从如下文件中读取：

SELECT @x = xCol.BulkColumn FROM OPENROWSET (BULK 'c:\Update.xml', SINGLE_BLOB) AS xCol;

Answer 2

当您处理相当大的 XML 文档时，我建议此时使用 .net 粉碎机。您可以在 CLR 过程或外部工具中执行此操作。您还可以使用 SQL Server 内置的 xquery，但这会很慢。

但是，看看这个和您之前的问题（将数据从 MS SQL Server 2008 R2 转储到单个 XML 文件），我认为您最好使用 BCP 实用程序甚至复制之类的东西。您的具体要求是什么？