如何在t-sql中汇总xml属性值?

请尝试以下解决方案。

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)', 'INT') AS Result
FROM @tbl AS t
    CROSS APPLY xmldata.nodes('/BatchSummary') AS t1(c);

输出

+----+--------+
| ID | Result |
+----+--------+
|  1 |   5022 |
|  2 |     14 |
+----+--------+