Convert XML to table in SQL Server 2005.
- by Tamim Sadikali
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?
