SQL Server – Select Repeating Number X Times and Then Increment

You can use ROW_NUMBER();

WITH CTE AS
(   SELECT  ID, 
            Prioroty, 
            Type, 
            Name, 
            RowNum = ROW_NUMBER() OVER(PARTITION BY Type ORDER BY Prioroty DESC)
    FROM    T
)
SELECT  ID, Prioroty, Type, Name
FROM    CTE
WHERE   RowNum = 1;

Example on SQL Fiddle

Here is a rewrite of what you already have.

1. No shredding necessary on root node.
2. Specify the text() node for the parameter name and parameter value.

In my limited tests that made it significantly faster. I look forward to see what if any performance gain you see on your side.

select T.TestId,
       T.TestData.value('(/root/@name)[1]', 'varchar(max)') as [root],
       C.X.value('@name', 'varchar(max)') as child,
       GC.X.value('@name', 'varchar(max)') as grandchild,
       null as greatgrandchild,
       P.X.value('(name/text())[1]', 'varchar(max)') as parameter
from dbo.TestTable as T
  cross apply T.TestData.nodes('/root/child') as C(X)
  cross apply C.X.nodes('grandchild') as GC(X)
  cross apply GC.X.nodes('parameter') as P(X)
where P.X.value('(value/text())[1]', 'varchar(max)') like '%[^Aa]BC%'
union all
select T.TestId,
       T.TestData.value('(/root/@name)[1]', 'varchar(max)') as [root],
       C.X.value('@name', 'varchar(max)') as child,
       GC.X.value('@name', 'varchar(max)') as grandchild,
       GGC.X.value('@name', 'varchar(max)') as greatgrandchild,
       P.X.value('(name/text())[1]', 'varchar(max)') as parameter
from dbo.TestTable as T
  cross apply T.TestData.nodes('/root/child') as C(X)
  cross apply C.X.nodes('grandchild') as GC(X)
  cross apply GC.X.nodes('greatgrandchild') as GGC(X)
  cross apply GGC.X.nodes('parameter') as P(X)
where P.X.value('(value/text())[1]', 'varchar(max)') like '%[^Aa]BC%'

Update:

I took the liberty to execute the test rig provided by wBob on SQL Server 2014 with Compatibility level 110 (SQL Server 2012) and 120 (SQL Server 2014)

Result:

Compatibility level  OP's query  My query  wBob query using XML indexes
-------------------  ----------  --------  ----------------------------
110                  64 sec      37 sec    1 sec
120                  8 sec       4 sec     5 sec

The difference you see in execution time is there because SQL Server 2014 uses a new cardinality estimator. When compatibility level is 110 SQL Server will use the old estimator. Turning the new cardinality estimator on or off using trace flags yields the exact same result.

The difference in times for the queries that does not use the XML indexes is because in Compatibility level 120 the plans are parallel with DOP 16 in my case.

It is also really interesting to notice that the execution time for using XML indexes is five times slower when using the new cardinality estimator. The reason for that is the same as above only the other way around. There is a parallel plan only when using the old cardinality estimator.