Convert XML to table in SQL Server 2005

Question

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

<ClientKeys>
   <ck>3052</ck>
   <ck>3051</ck>
   <ck>3050</ck>
   <ck>3049</ck>
   ...
 </ClientKeys>

...and then convert the XML to a temp table like this:

CREATE TABLE #ClientKeys ( ClientKey varchar(36) )  
INSERT INTO #ClientKeys (ClientKey) 
     SELECT ParamValues.ck.value('.','VARCHAR(36)')  
    FROM @ClientKeys.nodes('/ClientKeys/ck') as ParamValues(ck)   

...the temp tbl is populated and everything is good. However the time taken to populate said table is strictly proportionate to the number of 'ck' elements in the xml - which I wasn't expecting as there is no iterative step. And thus the time taken to populate the tbl soon becomes 'too long'.

Is there a quicker way to achieve the above?

Answer 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.

Answer 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.

Answer 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.