Here is an alternative which extracts all the FieldName/FieldValue attributes from the execution plan as an EAV type output, quite handy for quick analysis or as a source for Pivot/PowerBI etc.
😎
-- Using namespaces
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as p)
SELECT xt.RowNum
,STU.DATA.value('local-name(../../.)','NVARCHAR(128)') AS PARENT
,STU.DATA.value('local-name(../.)' ,'NVARCHAR(128)') AS ELEMENT
,STU.DATA.value('@FieldName' ,'NVARCHAR(128)') AS FieldName
,STU.DATA.value('@FieldValue' ,'NVARCHAR(128)') AS FieldVAlue
FROM #XMLTest xt
CROSS APPLY TheXML.nodes('//p:Recompile') RECOMP(DATA)
CROSS APPLY RECOMP.DATA.nodes('//p:Field') STU(DATA);
-- Ignoring namespaces
SELECT xt.RowNum
,STU.DATA.value('local-name(../../.)','NVARCHAR(128)') AS PARENT
,STU.DATA.value('local-name(../.)' ,'NVARCHAR(128)') AS ELEMENT
,STU.DATA.value('@FieldName' ,'NVARCHAR(128)') AS FieldName
,STU.DATA.value('@FieldValue' ,'NVARCHAR(128)') AS FieldVAlue
FROM #XMLTest xt
CROSS APPLY TheXML.nodes('//*:Recompile') RECOMP(DATA)
CROSS APPLY RECOMP.DATA.nodes('//*:Field') STU(DATA);
Sample outputRowNum PARENT ELEMENT FieldName FieldVAlue
----------- -------------- ----------------- --------------------------------------------- ---------------------
1 InternalInfo OptmInfo m_iOptStage 1
1 InternalInfo OptmInfo m_cOptTask 1098
1 InternalInfo OptmInfo m_ullAlgPmoSize 640
1 InternalInfo OptmInfo m_ullOptPmoSize 1064
1 InternalInfo OptmInfo m_ullAlgTotalTime 5328
1 InternalInfo OptmInfo m_ullAlgNetTime 2600
1 InternalInfo OptmInfo m_ullOptTotalTime 14232
1 InternalInfo OptmInfo m_ullOptNetTime 13869
1 InternalInfo OptmInfo m_fRemoteExchangePlanWouldHaveMultipleZones 0
1 EnvColl Recompile wszDb CRM_Prod
1 EnvColl Recompile wszSchema Util
1 EnvColl Recompile wszTable AreaCodeTimezone
1 EnvColl Recompile m_cRowCount 237767
1 EnvColl Recompile ullThreshold 48053
1 EnvColl Recompile wszReason heuristic
1 EnvColl Recompile m_fMissingStatsRecompile 0
1 EnvColl Recompile m_fVisibleOutsideXact 18446744073709551615
1 EnvColl Recompile m_dbId.DbIdLocal_TEMP() 14
1 EnvColl Recompile m_mdObjectId 921770341
1 EnvColl Recompile m_mdBaseIndexId 1
1 EnvColl Recompile m_cRowsetId 1
1 EnvColl Recompile m_verStats 4611686018434802649
1 EnvColl Recompile m_fAfterTriggerDelta 0
1 EnvColl Recompile m_fInsteadOfDeltaPopulate 0
1 EnvColl Recompile m_fInsteadOfDeltaInsideTrg 0
1 EnvColl Recompile m_fIsSbQueue 0
1 EnvColl Recompile m_cBricks 0
1 Recompile ModTrackingInfo wszStatName AK_AreaCodeTimezone
1 Recompile ModTrackingInfo wszColName AreaCodeExchange
1 Recompile ModTrackingInfo m_cCols 1
1 Recompile ModTrackingInfo m_idIS 1
1 Recompile ModTrackingInfo m_ullSnapShotModCtr 237767
1 Recompile ModTrackingInfo m_ullRowCount 237767
1 Recompile ModTrackingInfo ullThreshold 48053