As part of a SQL stored procedure (producing XML output) I have the following line:
FORMAT(Quantity * ld.UnitPrice, 'N2') AS '@value'
This has produced perfectly acceptable results for several months now. Last week however the service into which the XML that this procedure produces failed because the value was over a thousand pounds (sterling), producing the following output
It would have been nice had the service documentation mentioned the fact that it didn't like commas in the values passed back to it as attributes but that argument is for another day.
I'm after some advice as to the best way to reformat this one line so that it does not produce anything that separates thousands, in other words I need it to produce the following
or something similar whenever a value over one thousand pounds is arrived at. It still needs to retain the two decimal places.
Although I have mentioned specifically Pounds sterling in truth this format needs to be culture neutral, so that all figures that rise above a thousand do not have a separator.