This is a bug in SQL Server 2008. Use

insert @columns 
select ColumnNames.value('.', 'nvarchar(300)') name
from @xColumns.nodes('/columns/name') T1(ColumnNames)
OPTION (OPTIMIZE FOR ( @xColumns = NULL ))

This workaround is from an item on the Microsoft Connect Site which also mentions a hotfix for this Eager Spool / XML Reader issue.

This is a bug in SQL Server 2008. Use

insert @columns 
select ColumnNames.value('.', 'nvarchar(300)') name
from @xColumns.nodes('/columns/name') T1(ColumnNames)
OPTION (OPTIMIZE FOR ( @xColumns = NULL ))

This workaround is from an item on the Microsoft Connect Site which also mentions a hotfix for this Eager Spool / XML Reader issue is available (under traceflag 4130).

The reason for the performance regression is explained in a different connect item

The spool was introduced due to a general halloween protection logic (that is not needed for the XQuery expressions).

This is a bug in SQL Server 2008. Use

insert @columns 
select ColumnNames.value('.', 'nvarchar(300)') name
from @xColumns.nodes('/columns/name') T1(ColumnNames)
OPTION (OPTIMIZE FOR ( @xColumns = NULL ))

This workaround is from an item on the Microsoft Connect Site which also mentions a hotfix.

This is a bug in SQL Server 2008. Use

insert @columns 
select ColumnNames.value('.', 'nvarchar(300)') name
from @xColumns.nodes('/columns/name') T1(ColumnNames)
OPTION (OPTIMIZE FOR ( @xColumns = NULL ))

This workaround is from an item on the Microsoft Connect Site which also mentions a hotfix for this Eager Spool / XML Reader issue.