Convert XML To Table In SQL Server 2005

If I pass in an xml parameter to a stored proc which looks like this: 3052 3051 3050 &

Solution 1:

Just because your source code does not contain any looping syntax does not mean there is no iteration happening. If your code is inserting 10 rows into a table, it will always take 10 times longer than inserting 1 row.

Solution 2:

If you can move the code from the stored procedure to C#, you could use XMLBulkLoad, which was written to handle large files fast.

You can also try to run the query without the INSERT and measure the difference in performance. There's a good chance that the XML parsing is not the limiting factor.

Solution 3:

since I don't have data two compare, I can only suggest additional bulk upload methods that you can test on your environment with your data

One of them is to upload XML using SQL OpenRowSet command

DECLARE @X XML
SELECT 
   @X = ck
FROM OPENROWSET (BULK 'C:\kodyaz.com\sql\ClientKeys.xml', SINGLE_BLOB) AS Import(ck)

Select
   [ClientKey].value('.','varchar(100)') AS ClientKey
From @X.nodes('/ClientKeys/ck') keys([ClientKey])

On the same resource there is an other option of using OPENXML command.