November 1, 2016 at 12:26 pm
I have been working on a solution on my local pc running SQLEXPRESS and BULK INSERTing an xml document of 1gb into a table has taken around 5 mins.
Today I moved everything to the server where the code should run, a SQL 2008 server, and now the stored procedure doing the BULK INSERT just takes forever, and I haven't seen it complete yet. I tried to restrict the code to only insert 10 rows, but even with that it seems to just keep executing.
I have inserted some debug info and I know that it finishes loading the file into a variable, and it is when it starts the insert the problem starts.
Here is my code.
-- declare XML variable
DECLARE @InputXML XML
-- import file from disk
SELECT @InputXML = CAST(x AS XML)
FROM OPENROWSET(BULK 'F:\iceimport.xml', SINGLE_BLOB) AS T(x)
RAISERROR('
xml data has been loaded
',0,1) WITH NOWAIT
-- parse XML using XQuery and insert into the table
INSERT INTO dbo.import_tmp (
products_mpn
,products_gtin
,distributor_product_number
,distributor_code
,brand
,products_name
,products_short_description
,products_long_description
,category
,category_id
,sales_price
,purchase_price
,special_price
,stock
,tax_stibat
,tax_thuiskopie
,vat
,packing_unit
,image_1
,weight_gross
,weight_net
,active
)
SELECT
product.value('(products_mpn)[1]', 'NVARCHAR(256)'),
product.value('(products_gtin)[1]', 'NVARCHAR(256)'),
product.value('(distributor_product_number)[1]', 'NVARCHAR(256)'),
product.value('(distributor_code)[1]', 'NVARCHAR(256)'),
product.value('(brand)[1]', 'NVARCHAR(256)'),
product.value('(products_name)[1]', 'NVARCHAR(256)'),
product.value('(products_short_description)[1]', 'NVARCHAR(256)'),
product.value('(products_long_description)[1]', 'NVARCHAR(256)'),
product.value('(category)[1]', 'NVARCHAR(256)'),
NULL,
product.value('(sales_price)[1]', 'NVARCHAR(256)'),
product.value('(purchase_price)[1]', 'NVARCHAR(256)'),
product.value('(special_price)[1]', 'NVARCHAR(256)'),
product.value('(stock)[1]', 'NVARCHAR(256)'),
product.value('(tax_stibat)[1]', 'NVARCHAR(256)'),
product.value('(tax_thuiskopie)[1]', 'NVARCHAR(256)'),
product.value('(vat)[1]', 'NVARCHAR(256)'),
product.value('(packing_unit)[1]', 'NVARCHAR(256)'),
product.value('(image_1)[1]', 'NVARCHAR(256)'),
product.value('(weight_gross)[1]', 'NVARCHAR(256)'),
product.value('(weight_net)[1]', 'NVARCHAR(256)'),
1
FROM @InputXML.nodes('import/products/product') AS X(product)
November 1, 2016 at 12:51 pm
After some debugging trying to remove the insert part and only using select to see the result it seems to be the INSERT being the problem, because without it I get 10 rows within 30 seconds.
What could be the problem with the INSERT part?
November 1, 2016 at 1:05 pm
Problem solved, the select needed /text() for all values:
product.value('(products_mpn/text())[1]', 'NVARCHAR(256)'),
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply