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.
What's really cool is that if Eirikur can show me how to do the filtering I asked about, I might be able to figure out the necessary "pivoting" (if he doesn't go that far but would love to see it) to get to my final product, several things are going to happen.
1. I'm going to be able to solve a particularly nasty problem on my production systems and write code to keep it all up to snuff over time.
2. I'm going to understand XML a whole lot better because of the way these good folks are breaking things down. The examples they've given are incredible.
3. Since I know I'm not the only one with this nasty problem I'm having in production, I'm going to be able to help others with the problem AND, combining the info on this thread with an old thread that Matt Miller was on, I also be explain some XML in the process.
What a great thread. This is more and better than I could have possibly imagined when I first posted my question and, hopefully, we're not done with it yet.