Accepted answer

Yitzhak Khabinsky 21,511 Reputation points
20210222T14:55:36.66+00:00 Amazing, the
sum()
converts its calculation result to a scientific notation trying to make it more compact.Useful link: typecastingrulesinxquery
Please try the following solution.
SQL
DECLARE @s NVARCHAR(MAX) = N'1. Some data  32180000'; DECLARE @separator CHAR(1) = SPACE(1); SELECT CAST('<root><r>' + REPLACE(@s, @separator, '</r><r>') + '</r></root>' AS XML) .query('<root> { for $x in /root/r return if (xs:int($x) instance of xs:int) then $x else () (: filter out nonintegers :) } </root>') .query('sum(/root/r/text()) cast as xs:int?').value('.','INT') as result;
If you have really gigantic numbers you can handle them as follows:
.query('sum(/root/r/text()) cast as xs:unsignedLong?').value('.','BIGINT') as result;
Output
++  result  ++  32180000  ++