float when fetching the value from the XML.
select @XML.value('(//Balance)', 'float');
To change the value in a XML column with decimals included you need to first extract the value as a
float and write it back as an
numeric with the appropriate precision and scale.
Something like this.
set XMLCol.modify('replace value of (//Balance/text())
from YourTable as T
cross apply (select convert(numeric(18,4), T.XMLCol.value('(//Balance/text())', 'float'))) as X(Balance)
The reason for the scientific notation is because it is the way SQL Server handles xs:float when using untyped XML.
From Type Casting Rules in XQuery
When casting a value of type xs:float or xs:double, or any one of
their subtypes, to a string or untypedAtomic type, the value is
represented in scientific notation. This is done only when the value's
absolute value is less than 1.0E-6, or greater than or equal to 1.0E6.
This means that 0 is serialized in scientific notation to 0.0E0.