I have never done this before and do not know where to start. Do you know how
to parse out this TextData column into a readable string showing only the
element and value for "waitresource" and "duration" column?

This is the xml that was generated per row from a sql profiler test.

The is one record from TextData column...
<blocked-process-report monitorLoop="732"> <blocked-process> <process
id="process84b798" taskpriority="0" logused="10000" waitresource="FILE: 5:0"
waittime="46796" schedulerid="1" kpid="4100" status="suspended" spid="141"
sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2010-08-19T17:
08:19.707" lastbatchcompleted="2010-08-19T17:08:19.707" clientapp="SQLAgent -
TSQL JobStep (Job 0x9E71065CF90DAE4B8DFFF1B805EA7951 : Step 1)"
hostname="QTKSRV5" hostpid="3840" loginname="NT AUTHORITY\SYSTEM"
isolationlevel="read committed (2)" xactid="0" currentdb="5"
lockTimeout="4294967295" clientoption1="536873056" clientoption2="128056">
<executionStack> <frame line="1"
sqlhandle="0x01000500b0b75d01f80ee3060000000000000 00000000000"/>
</executionStack> <inputbuf> BACKUP LOG claims TO Claims_Log WITH SKIP,
INIT </inputbuf> </process> </blocked-process> <blocking-process>
<process status="background" waittime="93" spid="11" sbid="0" ecid="0"
priority="0" transcount="0"> <executionStack> <frame line="1"
sqlhandle="0x0000000000000000000000000000000000000 00000000000"/>
</executionStack> <inputbuf> </inputbuf> </process> </blocking-
process> </blocked-process-report>
Thanks
MC

--
Misty :-)

Message posted via http://www.sqlmonster.com

Mitchell_Collen via SQLMonster.com wrote:
Assuming you have a variable or column of type "xml" in MS SQL Server
2005 or later you can use XQuery methods like the 'nodes' method, the
'value' method:
http://msdn.microsoft.com/en-US/libr...SQL.90%29.aspx

--

Martin Honnen --- MVP Data Platform Development
http://msmvps.com/blogs/martin_honnen/