Using SQL2008, when shredding XML using the nodes query, the performance is fine when just SELECTing. However, when you want to INSERT the data to a table/temptable/tablevariable the performance becomes very poor.
Changing the compatibility to 90 from 100 resolves the issue.
This behaviour seems to be similar to that described in the following articles:
Connect cases: 472723 & 250407
Microsoft SQL Server 2008 (SP1) - 10.0.2734.0 (X64)
Sep 11 2009 14:30:58
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)