從sqlserver中的xml中提取資料

我如何從這個 xml 中提取 lastbatchstarted？

謝謝

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

我嘗試使用此查詢，但它不起作用

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

uj5u.com熱心網友回復：

沒有提供最小的可重現示例。

從臀部射擊。

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;

輸出

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

uj5u.com熱心網友回復：

鑒于您可以process在死鎖 XML 中擁有多個節點，您可能想要這樣的東西

您需要使用 .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);

標籤：sql sql-server xml 查询

上一篇：使用Xpath選擇特定元素的第一個子元素

下一篇：根據內部元素的ID將平面xml轉換為樹狀層次結構