How to tally up xml attribute values in T-SQL?-CodePudding

I've got the following XML data in a data column in a SQL Server table. I would like to sum up the total value of all the RecordCount attribute as I select each table row. How do I go about doing it?

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

CodePudding user response：

Please try the following solution.

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

Output

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