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