如何在T中总结xml属性值？

Question

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

Answer 1

请尝试以下解决方案。

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