Processing XML Showplans Using SQLCLR in SQL Server 2005 - Make Use of the New XML Showplans

SQL 2005 now provides Showplan (Query plan) output in XML - why? To make it easier for a dba/developer to write TSQL, or other code, that can determine the cost of a query and thus dynamically determine if the query should be prevented from running based on the load it will create on the server.

To make it easy to capitalise on this new feature this article https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/xmlshowplans.asp provides all the supporting code for two example solutions.  The difference between the two methods is due to the fact that XQuery queries can not be used for in-process data access - so one solution uses purely XPath and the other with a mix of both XPath and XQuery.