Importing XML file into SQL Server is too slow

Siedler

New Member
the task I want to achieve is import XML file into SQL Server. Once prepare the empty table I would load the entire xml file and fill that table. Googling I found that SQL Bulk Insert is suitable so I tested the following code which runs fine:\[code\]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);\[/code\]My XML file count around 1860 nodes, (30kb), quite small but the above procedure takes over 5 minutes to import the whole file. Is there any chance to spee up this process?I've also read that SQL Server 2008 have a bug which affect the bulk object.Any hints?
 
Back
Top