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

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 2

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.