How to filter XML execution plan data in a WHERE clause using TSQL

Question

I've seen a couple of responses here on SO where a TSQL statement filters on XML data using .value or .exist methods of an XML data type (here and here). Though I am not well-versed in the construction/syntax of these methods, I have a deeper issue.

I am attempting to query some cached execution plan data and search within the data for references to particular indexes, and the issue is that I don't know where in the XML to look. Right now I CAST the XML to an NVARCHAR and just use LIKE (see last line of query):

  DECLARE @IndexName NVARCHAR(100) = 'MyIndex'

    SELECT  OBJECT_NAME(objectid) AS procname, usecounts, query_plan
    FROM    sys.dm_exec_cached_plans cp
    CROSS   APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
    WHERE   objtype = 'Proc'
    AND     CAST(query_plan AS NVARCHAR(MAX)) LIKE '%' + @IndexName + '%'

So this works, but I get superfluous results. Ultimately, I'd like to obtain results for index seeks and scans, but right now I also see rows where indexes get modified (e.g. execution plans for general table updates, since the indexes are also getting updated).

1. How can I rephrase the last line of my query to use some sort of wildcard XML syntax (and avoid CASTing)?
2. How can I refine that query to return only those items who have a parent node of "IndexScan"
3. How can I refine that query to return only those items whose 1st found ancestor of "RelOp" has an attribute named "PhysicialOp" with a value of "Index Seek"?

Here's some simplified sample XML:

<RelOp NodeId="13" PhysicalOp="Index Seek" LogicalOp="Index Seek">
    <OutputList>
        <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Employees]" Column="EmployeeID" />
    </OutputList>
    <IndexScan Ordered="1" ScanDirection="FORWARD">
        <DefinedValues>
            <DefinedValue>
                <ColumnReference Database="[MyDB]" Table="[Employees]" Column="EmployeeID" />
            </DefinedValue>
        </DefinedValues>
        <Object Database="[MyDB]" Schema="[dbo]" Table="[Employees]" Index="[MyIndex]" />
    </IndexScan>
</RelOp>

Answer 1

declare @IndexName nvarchar(100) = '[MyIndex]';

with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select object_name(qp.objectid), 
       cp.usecounts,
       qp.query_plan
from sys.dm_exec_cached_plans as cp
  cross apply sys.dm_exec_query_plan(cp.plan_handle) as qp
where cp.objtype = 'Proc' and
      qp.query_plan.exist('//RelOp[
                                  @PhysicalOp = "Index Seek" and 
                                  IndexScan/Object/@Index = sql:variable("@IndexName")
                                  ]') = 1;

The index in the query plan has square brackets so you need to have that in your variable.

//RelOp does a deep search for all RelOp nodes picking the ones where @PhysicalOp is Index Seek and there exists a child node IndexScan/Object where @Index is what you have stored in your variable.