Need XQuery help

Question

Post reply

Need XQuery help

shahgols

SSCrazy Eights

Points: 9874
More actions
January 28, 2014 at 4:11 pm

#73560

Hi all, I am trying to get the values for spid and inputbuf under the blocked-process node as well as the spid and inputbuf under the blocking-process node. Can you please help me with the xquery? Thanks in advance.
<event name="blocked_process_report" package="sqlserver" timestamp="2014-01-28T22:13:03.814Z">
<data name="duration">
<value>18655000</value>
</data>
<data name="database_id">
<value>2</value>
</data>
<data name="object_id">
<value>2209392</value>
</data>
<data name="index_id">
<value>20480</value>
</data>
<data name="lock_mode">
<value>3</value>
<text>S</text>
</data>
<data name="transaction_id">
<value>124085777</value>
</data>
<data name="resource_owner_type">
<value>0x00000001</value>
<text>LOCK</text>
</data>
<data name="blocked_process">
<value>
<blocked-process-report monitorLoop="221377">
<blocked-process>
<process id="process1f4c17498" taskpriority="0" logused="0" waitresource="KEY: 2:5764607667828948992 (61a06abd401c)" waittime="18655" ownerId="124085777" transactionname="SELECT" lasttranstarted="2014-01-28T14:12:45.157" XDES="0x2c3a8cd00" lockMode="S" schedulerid="1" kpid="5716" status="suspended" spid="87" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2014-01-28T14:12:45.157" lastbatchcompleted="2014-01-28T14:12:45.150" lastattention="1900-01-01T00:00:00.150" clientapp="Microsoft SQL Server Management Studio - Query" hostname="ABC490" hostpid="14568" loginname="mydomain\username" isolationlevel="read committed (2)" xactid="124085777" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame line="1" sqlhandle="0x02000000d9de7b2f4f3a78e40f100bc02a84efbb9f01a84d0000000000000000000000000000000000000000" />
</executionStack>
<inputbuf>
SELECT * FROM t1 </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="suspended" waittime="20966" spid="92" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-01-28T14:12:42.847" lastbatchcompleted="2014-01-28T14:12:42.833" lastattention="1900-01-01T00:00:00.833" clientapp="Microsoft SQL Server Management Studio - Query" hostname="ABC490" hostpid="14568" loginname="domain\username" isolationlevel="read committed (2)" xactid="124085736" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame line="3" stmtstart="100" stmtend="150" sqlhandle="0x020000005a74b3030117e049389a93b2ce5bfb48e272f9380000000000000000000000000000000000000000" />
</executionStack>
<inputbuf>
BEGIN TRANSACTION
INSERT INTO t1 DEFAULT VALUES
WAITFOR DELAY '00:00:30'
COMMIT </inputbuf>
</process>
</blocking-process>
</blocked-process-report>
</value>
</data>
<data name="database_name">
<value>tempdb</value>
</data>
</event>
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

declare @x xml = '<event name="blocked_process_report" package="sqlserver" timestamp="2014-01-28T22:13:03.814Z">

<data name="duration">

<value>18655000</value>

</data>

<data name="database_id">

<value>2</value>

</data>

<data name="object_id">

<value>2209392</value>

</data>

<data name="index_id">

<value>20480</value>

</data>

<data name="lock_mode">

<value>3</value>

<text>S</text>

</data>

<data name="transaction_id">

<value>124085777</value>

</data>

<data name="resource_owner_type">

<value>0x00000001</value>

<text>LOCK</text>

</data>

<data name="blocked_process">

<value>

<blocked-process-report monitorLoop="221377">

<blocked-process>

<executionStack>

<frame line="1" sqlhandle="0x02000000d9de7b2f4f3a78e40f100bc02a84efbb9f01a84d0000000000000000000000000000000000000000" />

</executionStack>

<inputbuf>

SELECT * FROM t1 </inputbuf>

</process>

</blocked-process>

<blocking-process>

<process status="suspended" waittime="20966" spid="92" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-01-28T14:12:42.847" lastbatchcompleted="2014-01-28T14:12:42.833" lastattention="1900-01-01T00:00:00.833" clientapp="Microsoft SQL Server Management Studio - Query" hostname="ABC490" hostpid="14568" loginname="domain\username" isolationlevel="read committed (2)" xactid="124085736" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">

<executionStack>

<frame line="3" stmtstart="100" stmtend="150" sqlhandle="0x020000005a74b3030117e049389a93b2ce5bfb48e272f9380000000000000000000000000000000000000000" />

</executionStack>

<inputbuf>

BEGIN TRANSACTION

INSERT INTO t1 DEFAULT VALUES

WAITFOR DELAY ''00:00:30''

COMMIT </inputbuf>

</process>

</blocking-process>

</blocked-process-report>

</value>

</data>

<data name="database_name">

<value>tempdb</value>

</data>

</event>'

select

@x.value('(//blocked-process/process/@spid)[1]','integer') as blocked_spid

,@x.value('(//blocked-process/process/inputbuf/text())[1]','varchar(max)') as blocked_inputbuf

,@x.value('(//blocking-process/process/@spid)[1]','integer') as blocking_spid

,@x.value('(//blocking-process/process/inputbuf/text())[1]','varchar(max)') as blocking_inputbuf

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

shahgols SSCrazy Eights Points: 9874 More actions · Answer 2

shahgols

SSCrazy Eights

Points: 9874

January 29, 2014 at 1:37 pm

#1684477

Thank you Mister!