extract data from xml in sql server-CodePudding

how do i extract the lastbatchstarted from this xml?

THX

<event name="xml_deadlock_report" package="sqlserver" timestamp="2021-11-08T13:16:53.648Z">
  <data name="xml_report">
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process2bac10daca8" />
        </victim-list>
        <process-list>
          <process id="process2bac10daca8" taskpriority="0" logused="1772" waitresource="PAGE: 7:1:817276 " waittime="185" ownerId="115165741" transactionname="INSERT" lasttranstarted="2021-11-08T15:16:53.440" XDES="0x2bad31fa040" lockMode="U" schedulerid="1" kpid="14480" status="suspended" spid="64" sbid="0" ecid="7" priority="0" trancount="0" lastbatchstarted="2021-11-08T15:16:53.440" lastbatchcompleted="2021-11-08T15:16:53.440" lastattention="1900-01-01T00:00:00.440" clientapp="dfdf" hostname="dfdf" hostpid="3692" isolationlevel="read uncommitted (1)" xactid="115165741" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
            <executionStack>

i try with this query and it's not working

[XML Data].value('(/event/data[@name=''xml_report'']/value/process-list/process/@lastbatchstarted)[1]','datetime')

CodePudding user response：

A minimal reproducible example was not provided.

Shooting from the hip.

SQL

DECLARE @xml XML = 
N'<event name="xml_deadlock_report" package="sqlserver"
       timestamp="2021-11-08T13:16:53.648Z">
    <data name="xml_report">
        <value>
            <deadlock>
                <victim-list>
                    <victimProcess id="process2bac10daca8"/>
                </victim-list>
                <process-list>
                    <process id="process2bac10daca8" taskpriority="0"
                             logused="1772" waitresource="PAGE: 7:1:817276 "
                             waittime="185" ownerId="115165741"
                             transactionname="INSERT"
                             lasttranstarted="2021-11-08T15:16:53.440"
                             XDES="0x2bad31fa040" lockMode="U" schedulerid="1"
                             kpid="14480" status="suspended" spid="64" sbid="0"
                             ecid="7" priority="0" trancount="0"
                             lastbatchstarted="2021-11-08T15:16:53.440"
                             lastbatchcompleted="2021-11-08T15:16:53.440"
                             lastattention="1900-01-01T00:00:00.440"
                             clientapp="dfdf" hostname="dfdf" hostpid="3692"
                             isolationlevel="read uncommitted (1)"
                             xactid="115165741" currentdb="7"
                             lockTimeout="4294967295" clientoption1="673316896"
                             clientoption2="128056">
                    </process>
                </process-list>
            </deadlock>
        </value>
    </data>
</event>';

SELECT @xml.value('(/event/data[@name="xml_report"]/value/deadlock/process-list/process/@lastbatchstarted)[1]','DATETIME') AS lastbatchstarted;

Output

 ------------------------- 
|    lastbatchstarted     |
 ------------------------- 
| 2021-11-08 15:16:53.440 |
 -------------------------

CodePudding user response：

Given that you can have multiple process nodes in a deadlock XML, you probably want something like this

You need to shred the nodes into rows using .nodes

DECLARE @xml XML = 
N'<event name="xml_deadlock_report" package="sqlserver"
       timestamp="2021-11-08T13:16:53.648Z">
    <data name="xml_report">
        <value>
            <deadlock>
                <victim-list>
                    <victimProcess id="process2bac10daca8"/>
                </victim-list>
                <process-list>
                    <process id="process2bac10daca8" taskpriority="0" logused="1772" waitresource="PAGE: 7:1:817276 "
                             waittime="185" ownerId="115165741" transactionname="INSERT"
                             lasttranstarted="2021-11-08T15:16:53.440" XDES="0x2bad31fa040" lockMode="U" schedulerid="1"
                             kpid="14480" status="suspended" spid="64" sbid="0" ecid="7" priority="0" trancount="0"
                             lastbatchstarted="2021-11-08T15:16:53.440" lastbatchcompleted="2021-11-08T15:16:53.440"
                             lastattention="1900-01-01T00:00:00.440" clientapp="dfdf" hostname="dfdf" hostpid="3692"
                             isolationlevel="read uncommitted (1)" xactid="115165741" currentdb="7"
                             lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
                    </process>
                </process-list>
            </deadlock>
        </value>
    </data>
</event>';

SELECT
  x.process.value('@lastbatchstarted', 'DATETIME') AS lastbatchstarted,
  x.process.value('@id', 'sysname') AS id,
  x.process.value('@hostname', 'sysname') AS hostname
FROM @xml.nodes('/event/data[@name="xml_report"]/value/deadlock/process-list/process') x(process);