I have this weird issue with Sql server,
select CAST('<root><e>999999.99</e></root>' AS XML).value('sum(/root/e)','money')
works fine and returns "999999.99"
select CAST('<root><e>1000000.00</e></root>' AS XML).value('sum(/root/e)','money')
returns error
Cannot convert a char value to money. The char value has incorrect syntax.
If I change money to float
select CAST('<root><e>1000000.00</e></root>' AS XML).value('sum(/root/e)','float')
this works and returns "1000000"
How can I continue processing it as money and not have to use float?
Your XML is untyped (as is usual) so sum will take xdt:untypedAtomic values and convert them to xs:double, see the docs.
The outer float or money specification just tells SQL Server how to convert the XML result to an SQL Server type, it does not change the actual XQuery expression internally.
You can force a conversion to xs:decimal by using a FLWOR expression with cast as. Do not convert on the outside of the sum as someone mentioned in comments, that will still give rounding issues.
select CAST('<root><e>1000000.00</e></root>' AS XML).value('
sum(for $t in /root/e/text() return $t cast as xs:decimal?)
','money')
Note that money should be avoided as it has rounding issues. Use decimal(19,4) or similar instead.