Need XQuery help

  • 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>

  • 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>

    <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>'

    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);

  • 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]

  • Thank you Mister!

  • Viewing 3 posts - 1 through 2 (of 2 total)

    You must be logged in to reply to this topic. Login to reply