Query Plan read using T-SQL

If plan from a saved file

DECLARE @plancontents VARCHAR(MAX),@xml XML
SET @plancontents=(SELECT * FROM OPENROWSET(BULK 'C:\TEMP\MyPlan.XML', SINGLE_CLOB) x)

SELECT TOP 100 execquery.last_execution_time AS [Date Time]
	,execsql.TEXT AS [Script]
	,plan_handle
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
WHERE execsql.TEXT LIKE '%WITH Temp_Accounts (%'
ORDER BY execquery.last_execution_time DESC

-- go through all the execution plan nodes, get the attributes and sort on them
;with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	SELECT c.value('.[1]/@PhysicalOp', 'nvarchar(max)') as PhysicalOp,
	c.value('.[1]/@LogicalOp', 'nvarchar(max)') as LogicalOp,
	c.value('.[1]/@AvgRowSize', 'nvarchar(max)') as AvgRowSize,
	c.value('.[1]/@TableCardinality', 'nvarchar(max)') as TableCardinality,
	c.value('.[1]/@Parallel', 'nvarchar(max)') as Parallel,
	c.value('.[1]/@EstimateRebinds', 'nvarchar(max)') as EstimateRebinds,
	c.value('.[1]/@EstimateRewinds', 'nvarchar(max)') as EstimateRewinds,
	c.value('.[1]/@NodeId', 'nvarchar(max)') as NodeId,
	c.value('.[1]/@EstimatedTotalSubtreeCost', 'nvarchar(max)') as EstimatedTotalSubtreeCost,
    c.value('.[1]/@EstimateRows', 'nvarchar(max)') as EstimateRows,
    c.value('.[1]/@EstimateIO', 'nvarchar(max)') as EstimateIO,
    c.value('.[1]/@EstimateCPU', 'nvarchar(max)') as EstimateCPU,
    -- this returns just the node xml for easier inspection
    c.query('.') as ExecPlanNode        
FROM   -- this returns only nodes with the name RelOp even if they are children of children
       @xml.nodes('//child::RelOp') T(c)
ORDER BY EstimatedTotalSubtreeCost DESC

Escape XML special characters in SQL Server Query

Xml with null Column

How to work with xml type using Query

CPU utilization trends