Convert XML to table in SQL Server 2005.

Posted by Tamim Sadikali on Stack Overflow See other posts from Stack Overflow or by Tamim Sadikali
Published on 2009-11-05T10:55:59Z Indexed on 2010/06/16 16:02 UTC
Read the original article Hit count: 261

Filed under:
|
|
|

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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server