T-sql – How to remove xmlns from child elements with FOR XML

Not sure what you are seeing and not seeing in the event log but it works as expected for me (version 11.0.3000).

The mistake you did with your extra path expressions is that you forgot Stuff in the path.

Testing this with the extended events and looking at the execution plan for operator Table Valued Function XML Reader ... (there should be none), I found that one extra path is enough for your queries to make it use only the index to fetch the data [4] = '/Root/Stuff/Forms' as SQL nvarchar(25). You can have [3] = '/Root/Stuff/Forms' as XQUERY 'node()' in there as well and perhaps it will make a difference or not depending on what your XML look like.

Note 1: You have mixed up the names of the columns in the index and your query. That might contribute to the problems you have with extended event.

Note 2: The events for missing selective XML indexes is generated when the query is compiled. If the query plan is used from the cache you will not see any events.

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.