Conversion failed when converting the nvarchar value '3.218E7' to data type int.

Question

 declare @s nvarchar(max)='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 non-integers :) } </root>').query('sum(/root/r/text())').value('.','int') as result 

Answer 1

@Decompressor ,

Amazing, the sum() converts its calculation result to a scientific notation trying to make it more compact.

Useful link: type-casting-rules-in-xquery

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 non-integers :)   
 }  
 </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 |  
+----------+