How to parse query plans to get a count of nested loop operators with unordered prefetch?

Question

My boss wants me to parse a set of query plans stored in a table and to determine how many nested loop operators with unordered prefetching are present in each plan. I only have around 100 query plans so performance isn't very important. I tried doing it myself but quickly got confused and couldn't make progress.

The structure of the table:

DROP TABLE IF EXISTS dbo.query_plans;

CREATE TABLE dbo.query_plans (
plan_name VARCHAR(100),
query_xml XML
);

I uploaded T-SQL to add three example query plans to the table on pastebin. This is the output that I'm looking for:

╔═════════════╦════════════════╗
║  plan_name  ║ OPERATOR_COUNT ║
╠═════════════╬════════════════╣
║ NO_PREFETCH ║              0 ║
║ 1_PREFETCH  ║              1 ║
║ 2_PREFETCH  ║              2 ║
╚═════════════╩════════════════╝

I can't answer any questions as to why I need to do this. Thanks!

Answer 1

This will Work Perfectly®

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
SELECT qp.plan_name, 
       qp.query_xml.value('count(//p:RelOp/p:NestedLoops/@WithUnorderedPrefetch)', 'int') AS operator_count
FROM dbo.query_plans AS qp;

If you prefer, you could use

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

...and omit the namespace prefix p: from the XQuery expression.