我在 SQL Server 表的資料列中有以下 XML 資料。當我選擇每個表格行時，我想總結所有RecordCount屬性的總值。我該怎么做呢？
<BatchSummary BatchNumber="7" BatchType="SecOwnerTransfer">
<InterfaceTable TableName="ASR.aps_transferevents" RecordCount="1438" />
<InterfaceTable TableName="ASR.aps_transferowners" RecordCount="3462" />
<InterfaceTable TableName="ASR.APS_DeleteTransferEvents" RecordCount="122" />
</BatchSummary>
uj5u.com熱心網友回復：
請嘗試以下解決方案。
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<BatchSummary BatchNumber="7" BatchType="SecOwnerTransfer">
<InterfaceTable TableName="ASR.aps_transferevents" RecordCount="1438" />
<InterfaceTable TableName="ASR.aps_transferowners" RecordCount="3462" />
<InterfaceTable TableName="ASR.APS_DeleteTransferEvents" RecordCount="122" />
</BatchSummary>'),
(N'<BatchSummary BatchNumber="7" BatchType="SecOwnerTransfer">
<InterfaceTable TableName="ASR.aps_transferevents" RecordCount="1" />
<InterfaceTable TableName="ASR.aps_transferowners" RecordCount="3" />
<InterfaceTable TableName="ASR.APS_DeleteTransferEvents" RecordCount="10" />
</BatchSummary>');
-- DDL and sample data population, end
SELECT t.*
, c.value('sum(InterfaceTable/@RecordCount)', 'DECIMAL(12,2)') AS Result
FROM @tbl AS t
CROSS APPLY xmldata.nodes('/BatchSummary') AS t1(c);
-- to check data type of the RecordCount attribute
SELECT ID
, TRY_CAST(c.value('.', 'VARCHAR(30)') AS INT) AS Result
FROM @tbl AS t
CROSS APPLY xmldata.nodes('/BatchSummary/InterfaceTable/@RecordCount') AS t1(c);
輸出
---- --------
| ID | Result |
---- --------
| 1 | 5022 |
| 2 | 14 |
---- --------
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/417658.html
標籤：