Hi Jeff,
This should give you what you want
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as p)
SELECT xt.RowNum
,wszDb = StatsUsed.XMLCol.value('(../p:Field[@FieldName="wszDb"])[1]/@FieldValue','NVARCHAR(128)')
,wszSchema = StatsUsed.XMLCol.value('(../p:Field[@FieldName="wszSchema"])[1]/@FieldValue','NVARCHAR(128)')
,wszTable = StatsUsed.XMLCol.value('(../p:Field[@FieldName="wszTable"])[1]/@FieldValue','NVARCHAR(128)')
,wszColName = StatsUsed.XMLCol.value('(p:Field[@FieldName="wszColName"])[1]/@FieldValue','NVARCHAR(128)')
,StatsName = StatsUsed.XMLCol.value('(p:Field[@FieldName="wszStatName"])[1]/@FieldValue','NVARCHAR(128)')
FROM #XMLTest xt
CROSS APPLY TheXML.nodes('//p:ModTrackingInfo') StatsUsed (XMLCol);
OMG! Something's wrong! I actually understand what you did and it's XML! 😉 What a great example!
And I just ran your code... TOTALLY AWESOME! Thank you so much for the help. I actually understand what you did and I'm completely embarrassed at how simple you made it. It definitely shows what can be done if you know what you're doing.
Shifting gears to that very subject, I do understand everything that you're done and I recognize all of the pieces that you used to do it. I've struggled many times over many days trying to figure this out on my own. I've watched videos and have read a lot of posts and articles and still couldn't put this (apparently) simple bit of XML shredding together. I do realize that it takes practice but what on this good Green Earth did you use to train yourself in all things XML?.
I can't thank you enough for the help you've provided. You've absolutely made my day. Thanks, Mark.
Heh - Perhaps even more amazing than you understanding it, I think I may understand it. 😛 Every once in a while during a struggle with XML, I have this mind-bending epiphany and I think I understand it. Then I try to use it again and realize I didn't understand what I thought I understood. I don't know why it's so difficult for me to get - probably that I don't use it very much and never get the practice necessary to let it fully sink in.
Thanks, Mark.