Extended Events Changes in SQL Server 2012 – Event XML for XML data elements

While working on validating my demos for the 24 Hours of PASS and my PASS Summit 2011 Precon – Extended Events Deep Dive, I noticed a significant, and breaking change to the Event XML output for the raw event data in the ring_buffer and file_target in SQL Server Denali.  In SQL Server 2008 and 2008R2, the Event XML represented the output of XML data elements differently than in SQL Server Denali CTP3.  A good example of this is the xml_deadlock_report output, which I previously discussed in my SQL Server Central article,

In SQL Server 2008 and 2008R2, the query to retrieve the deadlock graph from the system_health session was (excluding the work around that was included in the original article since the xml_deadlock_report was fixed in a later Cumulative Update and the latest Service Pack for SQL Server 2008 and 2008 R2).

SELECT 
    CAST(event_data.value('(data/value)[1]', 'varchar(max)')) AS XML) AS DeadlockGraph 
FROM 
(   SELECT XEvent.query('.') AS event_data 
    FROM 
    (   -- Cast the target_data to XML 
        SELECT CAST(target_data AS XML) AS TargetData 
        FROM sys.dm_xe_session_targets AS st 
        INNER JOIN sys.dm_xe_sessions AS s 
            ON s.address = st.event_session_address 
        WHERE name = N'system_health' 
          AND target_name = N'ring_buffer' 
    ) AS Data 
    -- Split out the Event Nodes 
    CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)   
) AS tab (event_data);

If you run this same code in SQL Server Denali CTP3, the output will not be the xml_deadlock_report but instead the textual data that was included in the sub-nodes of the value node for the xml_deadlock_report events in the target.  This unfortunately has broken a number of my scripts that were initially written for SQL Server 2008 and 2008R2 that expect the XML output as text in the value element as follows:

<event name="xml_deadlock_report" package="sqlserver" id="123" version="1" timestamp="2011-09-15T23:29:02.851Z"> 
  <data name="xml_report"> 
    <type name="unicode_string" package="package0" /> 
    <value>&lt;deadlock-list&gt; 
&lt;victim-list&gt; 
  &lt;victimProcess id="process806e2088"/&gt; 
  &lt;process-list&gt; 
   &lt;process id="process806e2088" taskpriority="0" logused="10000" waitresource="DATABASE: 15 " waittime="1477" schedulerid="2" kpid="3720" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2011-09-15T19:29:01.370" lastbatchcompleted="2011-09-15T19:27:21.193" clientapp="Microsoft SQL Server Management Studio – Query" hostname="SQL2K8R2-IE2" hostpid="4464" loginname="SQLSKILLSDEMOS\administrator" isolationlevel="read committed (2)" xactid="68641" currentdb="15" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"&gt; 
    &lt;executionStack&gt; 
     &lt;frame procname="" line="1" sqlhandle="0x01000100721ac42240ff1285000000000000000000000000"&gt; 
     &lt;/frame&gt; 
    &lt;/executionStack&gt; 
    &lt;inputbuf&gt; 
ALTER DATABASE DemoNCIndex SET MULTI_USER 
    &lt;/inputbuf&gt; 
   &lt;/process&gt; 
   &lt;process id="process469b88" taskpriority="0" logused="10000" waitresource="DATABASE: 15 " waittime="1892" schedulerid="2" kpid="4188" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-09-15T19:29:00.957" lastbatchcompleted="2011-09-15T19:29:00.947" clientapp="Microsoft SQL Server Management Studio – Transact-SQL IntelliSense" hostname="SQL2K8R2-IE2" hostpid="4464" loginname="SQLSKILLSDEMOS\administrator" isolationlevel="read committed (2)" xactid="68638" currentdb="15" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"&gt; 
    &lt;executionStack&gt; 
     &lt;frame procname="" line="1" sqlhandle="0x010001008af5b714605a1f85000000000000000000000000"&gt; 
     &lt;/frame&gt; 
    &lt;/executionStack&gt; 
    &lt;inputbuf&gt; 
use [DemoNCIndex]    &lt;/inputbuf&gt; 
   &lt;/process&gt; 
  &lt;/process-list&gt; 
  &lt;resource-list&gt; 
   &lt;databaselock subresource="FULL" dbid="15" dbname="" id="lock83168d80" mode="S"&gt; 
    &lt;owner-list&gt; 
     &lt;owner id="process469b88" mode="S"/&gt; 
    &lt;/owner-list&gt; 
    &lt;waiter-list&gt; 
     &lt;waiter id="process806e2088" mode="X" requestType="wait"/&gt; 
    &lt;/waiter-list&gt; 
   &lt;/databaselock&gt; 
   &lt;databaselock subresource="FULL" dbid="15" dbname="" id="lock83168d80" mode="S"&gt; 
    &lt;owner-list&gt; 
     &lt;owner id="process806e2088" mode="S"/&gt; 
     &lt;owner id="process806e2088" mode="S"/&gt; 
    &lt;/owner-list&gt; 
    &lt;waiter-list&gt; 
     &lt;waiter id="process469b88" mode="X" requestType="wait"/&gt; 
    &lt;/waiter-list&gt; 
   &lt;/databaselock&gt; 
  &lt;/resource-list&gt; 
&lt;/deadlock&gt; 
&lt;/deadlock-list&gt; 
</value> 
    <text /> 
  </data> 
</event>

Instead in SQL Server Denali CTP3, the event output is as follows:

<event name="xml_deadlock_report" package="sqlserver" timestamp="2011-09-17T18:49:03.654Z"> 
  <data name="xml_report"> 
    <type name="xml" package="package0" /> 
    <value> 
      <deadlock> 
        <victim-list> 
          <victimProcess id="processf7034a18" /> 
        </victim-list> 
        <process-list> 
          <process id="processf7034a18" taskpriority="0" logused="144" waitresource="RID: 2:1:281:0" waittime="2394" ownerId="162349" transactionname="user_transaction" lasttranstarted="2011-09-17T11:48:48.410" XDES="0xff047120" lockMode="S" schedulerid="2" kpid="692" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2011-09-17T11:49:01.247" lastbatchcompleted="2011-09-17T11:48:48.410" lastattention="2011-09-17T11:39:47.393" clientapp="Microsoft SQL Server Management Studio – Query" hostname="WIN-QSTGAPD63IN" hostpid="3004" loginname="WIN-QSTGAPD63IN\Administrator" isolationlevel="read committed (2)" xactid="162349" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"> 
            <executionStack> 
              <frame procname="adhoc" line="1" sqlhandle="0x02000000303b01237c6994b4eab30fb77cbb5a8e46f2b2540000000000000000000000000000000000000000"> 
SELECT Column2 
FROM TableB    </frame> 
            </executionStack> 
            <inputbuf> 
SELECT Column2 
FROM TableB   </inputbuf> 
          </process> 
          <process id="processf7035168" taskpriority="0" logused="144" waitresource="RID: 2:1:271:0" waittime="7494" ownerId="162369" transactionname="user_transaction" lasttranstarted="2011-09-17T11:48:53.693" XDES="0xf7044dd0" lockMode="S" schedulerid="2" kpid="3244" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2011-09-17T11:48:56.150" lastbatchcompleted="2011-09-17T11:48:53.693" lastattention="1900-01-01T00:00:00.693" clientapp="Microsoft SQL Server Management Studio – Query" hostname="WIN-QSTGAPD63IN" hostpid="3004" loginname="WIN-QSTGAPD63IN\Administrator" isolationlevel="read committed (2)" xactid="162369" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"> 
            <executionStack> 
              <frame procname="adhoc" line="2" stmtstart="4" sqlhandle="0x020000002e8952007a6c36a78a2aa436877a27f57a0725c80000000000000000000000000000000000000000"> 
SELECT Column1 
FROM TableA    </frame> 
            </executionStack> 
            <inputbuf>

SELECT Column1 
FROM TableA   </inputbuf> 
          </process> 
        </process-list> 
        <resource-list> 
          <ridlock fileid="1" pageid="281" dbid="2" objectname="tempdb.dbo.TABLEB" id="lockf7d4ff80" mode="X" associatedObjectId="2161727822326792192"> 
            <owner-list> 
              <owner id="processf7035168" mode="X" /> 
            </owner-list> 
            <waiter-list> 
              <waiter id="processf7034a18" mode="S" requestType="wait" /> 
            </waiter-list> 
          </ridlock> 
          <ridlock fileid="1" pageid="271" dbid="2" objectname="tempdb.dbo.TABLEA" id="lockf7d51380" mode="X" associatedObjectId="2089670228247904256"> 
            <owner-list> 
              <owner id="processf7034a18" mode="X" /> 
            </owner-list> 
            <waiter-list> 
              <waiter id="processf7035168" mode="S" requestType="wait" /> 
            </waiter-list> 
          </ridlock> 
        </resource-list> 
      </deadlock> 
    </value> 
  </data> 
</event>

If you compare the two bold sections to each other you will notice the difference.  In SQL Server 2008 and 2008R2, the value element is XML escaped entirely as text, but in SQL Server Denali CTP3, the value attribute contains a valid XML document as a child node in the XML itself.  This has a significant impact to how you actually access the XML data in Denali CTP3.  To read the XML Document, you have to switch from using the .value() XML function along with a CAST() operation to using a .query() operation on the Event XML specifying the deadlock node as a part of the .query() XPATH for it as shown in the following code example:

SELECT 
    event_data.query('(event/data/value/deadlock)[1]') AS DeadlockGraph 
FROM 
(   SELECT XEvent.query('.') AS event_data 
    FROM 
    (   -- Cast the target_data to XML 
        SELECT CAST(target_data AS XML) AS TargetData 
        FROM sys.dm_xe_session_targets AS st 
        INNER JOIN sys.dm_xe_sessions AS s 
            ON s.address = st.event_session_address 
        WHERE name = N'system_health' 
          AND target_name = N'ring_buffer' 
    ) AS Data 
    -- Split out the Event Nodes 
    CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)   
) AS tab (event_data);

This same thing applies to all of the XML data elements including the sqlserver.tsql_stack and sqlserver.tsql_frame Actions.  In addition other actions such as the sqlserver.plan_handle have similar changes that require changing the code to process the Event XML to capture the values being output.