• Matt Miller (4) - Saturday, May 27, 2017 5:33 PM

    Sorry one more update after scrolling up to your final request.  If you want to subset the modInfoTracking fields to only what you want, you need that last cross apply to stop at ModInfoTracking.
    Try this one.

    SELECT xt.RowNum
    --,recomp.data.query('*')
    ,Recomp.data.value('(*:Field[@FieldName="wszDb"])[1]/@FieldValue','NVARCHAR(128)') wszDb
    ,Recomp.data.value('(*:Field[@FieldName="wszSchema"])[1]/@FieldValue','NVARCHAR(128)') wszSchema
    ,Recomp.data.value('(*:Field[@FieldName="wszTable"])[1]/@FieldValue','NVARCHAR(128)') wszTable
    ,STU.data.value('(*:Field[@FieldName="wszColName"])[1]/@FieldValue','NVARCHAR(128)') wszColName
    ,STU.data.value('(*:Field[@FieldName="wszStatName"])[1]/@FieldValue','NVARCHAR(128)') wszColName
    ,STU.DATA.value('local-name(../.)','NVARCHAR(128)')  AS PARENT
    ,STU.DATA.value('local-name(.)' ,'NVARCHAR(128)')  AS ELEMENT
      --,STU.DATA.value('(*:Field)[1]/@FieldName' ,'NVARCHAR(128)') AS FieldName
      --,STU.DATA.value('(*:Field)[1]/@FieldValue'  ,'NVARCHAR(128)') AS FieldVAlue
    FROM #XMLTest xt
    CROSS APPLY TheXML.nodes('//*:Recompile') RECOMP(DATA)
    CROSS APPLY RECOMP.DATA.nodes('*:ModTrackingInfo') STU(DATA);

    That's absolutely perfect.  I was just going to post what I just came up with and I must have done it correctly because it's almost identical to what you posted above.  To top it off, it's a Saturday on a holiday weekend (in the USA, anyway) and the very same fellow that started me up on XML a couple of years ago provides instant verification/gratification. 

    I originally ran Mark's good code last night against one of my production servers.  It did exactly what was needed but it was slow.  It took more than 4 hours to go through just 61,000 entries.  I figured that the "bottom up" approach got a little expensive and so was looking for (pestering everyone for :blush: ) the "top down" approach where the database information is rendered only once instead of once for every occurrence of the ModTrackingInfo.

    Not having seen Matt's post above yete, I thought, "Damn.  If I have someone show me the final answer, then I've learned nothing" and got busy.

    I went back and looked at Johan's and Eirikur's posts because I remembered they had both used APPLY and felt that might be the way to go.  That's when I remembered the post (from years ago) that Matt and I were on for "flattening XML"  Matt had taught me much about how to use OUTER APPLY for things like this and I had forgotten most of it because I've not had to work with XML since then.  It's got a good example but I thought the number of OUTER APPLYs would get expensive and went back to Mark's code for the code in the SELECT list.  It also dawned on me that Mark's code actually does the filtering at the FIELD level I needed, as well.

    One of the problems with the data that I'm having to "shrefen" (heh... a "Modenism" for "shred and flatten" XML... just rolls off the tongue, doesn't it? 😉 ) is that some of the entries don't contain the "Recompile" element at all.  Others contain the "Recompile" element but not the "ModTrackingInfo" element.  In fact, about half of all the elements are in one of those conditions or the other.  It's a waste of time even looking at them.  That's when I remembered the WHERE clause that Johan had posted.

    Going back to Eirikur's eample, I picked out his example on how to avoid the name space.  Rumor has it that MS has a file with SQL server for that name space that could improve performance on a necessarily non-indexed XML column but now I have two slightly different bits of code to test against the system.

    Stitching it all together, here's what I came up with before looking at Matt's code above.  Like I said, instant gratification that I finally figured this one out.  I say "finally" because, as simple as the end result is, it took me a long time to figure out the simple method.  What I really like about it is that there are no dot-dot notations or backslashes in the SELECT list.


    --===== Namespace version
       WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
     SELECT  tbl.RowNum
            ,DbName     = db.DbInfo.value     ('(p:Field[@FieldName="wszDb"])       [1] /@FieldValue','SYSNAME')
            ,SchemaName = db.DbInfo.value     ('(p:Field[@FieldName="wszSchema"])   [1] /@FieldValue','SYSNAME')
            ,TableName  = db.DbInfo.value     ('(p:Field[@FieldName="wszTable"])    [1] /@FieldValue','SYSNAME')
            ,StatName   = si.SchemaInfo.value ('(p:Field[@FieldName="wszStatName"]) [1] /@FieldValue','SYSNAME')
            ,ColumnName = si.SchemaInfo.value ('(p:Field[@FieldName="wszColName"])  [1] /@FieldValue','SYSNAME')
       FROM       #XMLTest tbl
      OUTER APPLY tbl.TheXML.nodes ('//p:Recompile')      db (DbInfo)
      OUTER APPLY DbInfo.nodes     ('p:ModTrackingInfo')  si (SchemaInfo)
      WHERE db.DBInfo.exist        (N'p:ModTrackingInfo') = 1
    ;

    --===== Non-namespace version
     SELECT  tbl.RowNum
            ,DbName     = db.DbInfo.value     ('(*:Field[@FieldName="wszDb"])       [1] /@FieldValue','SYSNAME')
            ,SchemaName = db.DbInfo.value     ('(*:Field[@FieldName="wszSchema"])   [1] /@FieldValue','SYSNAME')
            ,TableName  = db.DbInfo.value     ('(*:Field[@FieldName="wszTable"])    [1] /@FieldValue','SYSNAME')
            ,StatName   = si.SchemaInfo.value ('(*:Field[@FieldName="wszStatName"]) [1] /@FieldValue','SYSNAME')
            ,ColumnName = si.SchemaInfo.value ('(*:Field[@FieldName="wszColName"])  [1] /@FieldValue','SYSNAME')
       FROM       #XMLTest tbl
      OUTER APPLY tbl.TheXML.nodes ('//*:Recompile')      db (DbInfo)
      OUTER APPLY DbInfo.nodes     ('*:ModTrackingInfo')  si (SchemaInfo)
      WHERE db.DBInfo.exist        (N'*:ModTrackingInfo') = 1
    ;

    My hat's off to you good folks for helping me learn this stuff, especially since I have an immediate need for it to solve a nasty production problem.  You've just gotta love this community!

    I've off to the races to see what the performance of these methods are.  Oddly enough, the original column of data doesn't actually exist at run time so a direct XML index isn't possible.  I've been playing with a fix for that, as well.  Once I'm done, "I'll be back" with an update.

    Thank all of you again.  There's a piece of what everyone of you posted in the code and I couldn't have figured it out without you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)