Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

XQuery Lab 53 – Reading information from SQL Server Extended Event Information XML

Mar 23 2010 6:42PM by Jacob Sebastian   

One of my friends recently sent me the following XML fragment and asked me if I can help writing a query to pull the information out of the XML document.

<event name="exception_ring_buffer_recorded" package="sqlos">
  <data name="error">
    <value>8134</value>
    <text />
  </data>
  <data name="severity">
    <value>16</value>
    <text />
  </data>
  <action name="tsql_stack" package="sqlserver">
    <value>
      &lt;frame level='1' 
      handle='0x010007007845CB27D81B4308000000000000000000000000' 
      line='1' offsetStart='0' offsetEnd='0'/&gt;
    </value>
    <text />
  </action>
  <action name="sql_text" package="sqlserver">
    <value>SELECT 1/0 AS DevideByZeroError;</value>
    <text />
  </action>
</event>

Here is the expected output from the above XML fragment:

Error  sql_text           tsql_stack
------ ------------------ ----------------------------
8134   SELECT 1/0 AS      0x010007007845CB27D81B430800
       DevideByZeroError; 0000000000000000000000        

At first glance it will look like any other XQuery example we have seen in the previous labs, but there are two challenge in this task.

First of all, we need to read values from multiple nodes and present them as columns. Secondly once of the elements (“tsql_stack”) contains an XML encoded string and we need to extract an attribute value (“handle”) from the XML encoded string.

Once way to achieve this is by retrieving the XML string and casting it to an XML data type value and then running another XQuery on the result. The following example demonstrates it.

DECLARE @x XML
SELECT @x = '
<event name="exception_ring_buffer_recorded" package="sqlos" >
  <data name="error">
    <value>8134</value>
    <text />
  </data>
  <data name="severity">
    <value>16</value>
    <text />
  </data>
  <action name="tsql_stack" package="sqlserver">
    <value>
		&lt;frame level=''1'' 
		handle=''0x010007007845CB27D81B4308000000000000000000000000'' 
		line=''1'' offsetStart=''0'' offsetEnd=''0''/&gt;
	</value>
    <text />
  </action>
  <action name="sql_text" package="sqlserver">
    <value>SELECT 1/0 AS DevideByZeroError;</value>
    <text />
  </action>
</event>'

SELECT
    Error,
    ttext,
    stack.value('(frame/@handle)[1]','VARCHAR(50)') AS tstack
FROM @x.nodes('/event') a(x)
CROSS APPLY (
	SELECT CAST(
		x.value('(action[@name="tsql_stack"]/value)[1]','VARCHAR(1000)')
        AS XML
       ) AS stack,
       x.value('(data[@name="error"]/value)[1]','INT') AS Error,
       x.value('(action[@name="sql_text"]/value)[1]','VARCHAR(40)') 
		AS ttext
) ts

/*
Error  ttext              tstack
------ ------------------ ----------------------------
8134   SELECT 1/0 AS      0x010007007845CB27D81B430800
       DevideByZeroError; 0000000000000000000000        
*/

View All Labs: XQuery Labs - A Collection of XQuery Sample Scripts and Tutorials

Visit the XML Resource Center for more XML articles and tutorials.

Tags: XQuery-Functions, XQuery-Labs, XML, XQuery, TSQL, SQL SERVER, CROSS-APPLY, XQuery Functions, SQL Server XQuery, XQuery in TSQL, XQuery Training, XQuery Tutorial,


Jacob Sebastian
1 · 100% · 32235
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]