将XML文件导入SQL Server速度过慢

Question

我想要完成的任务是将XML文件导入SQL Server。一旦准备好空表，我将加载整个XML文件并填充该表。通过搜索，我发现SQL Bulk Insert非常适合此任务，所以我测试了以下代码并成功运行：

INSERT INTO Products (sku, product_desc)
SELECT X.product.query('SKU').value('.', 'INT'),
       X.product.query('Desc').value('.', 'VARCHAR(30)')
FROM (
SELECT CAST(x AS XML)
FROM OPENROWSET(
    BULK 'C:\Products.xml',
    SINGLE_BLOB) AS T(x)
    ) AS T(x)
CROSS APPLY x.nodes('Products/Product') AS X(product);

我的XML文件大约有1860个节点（30kb），非常小，但上述过程需要5分钟以上才能导入整个文件。有没有任何方法可以加快此过程？ 我还阅读到SQL Server 2008存在一个影响批量对象的错误。 有什么提示吗？

- Ras

---

最好在C#或其他语言中解析XML，并通过应用程序导入它。 - Oded

是的，你说得对。第一次尝试使用C＃导入XML，但我认为SQL Server的批量处理程序更可靠。 - Ras

嗯，XML和关系型数据库...通常不是很匹配。 - Oded

Answer 1

试试这个 - 我在普通的桌面电脑上从磁盘导入了一个包含4,096条记录的文件，仅用了53秒钟：

-- declare XML variable
DECLARE @InputXML XML

-- import file from disk
SELECT @InputXML = CAST(x AS XML)
FROM OPENROWSET(BULK 'D:\temp\Products.xml', SINGLE_BLOB) AS T(x)

-- parse XML using XQuery and insert into the table    
INSERT INTO dbo.Products (sku, product_desc)
    SELECT 
        product.value('(SKU)[1]', 'int'),
        product.value('(Desc)[1]', 'varchar(30)')
    FROM @InputXML.nodes('Products/Product') AS X(product)

- marc_s

---

从8小时缩短到2秒，对于一个5MB的XML文件。谢谢您！ - krobbens

非常感谢 - 对我来说也是一个多小时的查询....从未让它完成...在几秒钟内添加了30k行！ - gbrooksman

不到一秒钟就能处理18k行数据，谢谢！ - Juan Pedro Goicochea

Answer 2

declare @xml table (x xml)
--
insert @xml
select x
FROM OPENROWSET(BULK 'D:\temp\Products.xml', SINGLE_BLOB) AS T(x)

INSERT INTO dbo.Products (sku, product_desc)
SELECT
    RESULT.sku, RESULT.product_desc
FROM @xml
cross apply (
   select sku = z.value('SKU[1]', 'int'), 
          product_desc = z.value('Desc[1]','varchar(30)') 
   from x.nodes('Products/Product') Z1(z)
) RESULT 

2秒内处理了13000条记录，使用的是SQL Server 2008 R2。数据库兼容性为100。

另外，也可以尝试使用数据库兼容性为90。

- DimaSUN