CODEWITHSUNDEEP
sql-serverfor-xmlfor-xml-explicit
Mark Elder
Mark Elder

Reputation: 4127

Output of "for XML Explicit" Query changes trailing zeros for money columns depending on connection

I have a Microsoft SQL Server query that builds up a temp table and then outputs XML using the "for XML Explicit" syntax like this:

select * from @XMLOutput order by 
  [UaarSale!1!NodeOrder!hide], [UaarSale!1!NestingId!hide], Tag  
for XML Explicit

This is all contained inside a stored procedure. We simply run the stored procedure passing in our Id to get the XML document out.

The query itself is working great and has been for several years. However we are now finding situations where the XML output is changing depending on who is calling the stored procedure. Our output contains a large number of money data types. In some cases trailing zeros are not included on the money types. In other cases the types always show 4 significant digits.

In my test environment I have duplicated this on a SQL Server 2008 R2 database.

For example my main program (connecting with the old SQLOLEDB provider) gets XML that does not contain trailing zeros. 

<node value="12.34" />

However when I run the same stored procedure from SQL Server Management studio I always get 4 places.

<node value="12.3400" />

I also have a client connecting with my application using the same SQLOLEDB provider who is getting the extra zeros.

I process the XML files to generate reports. Those are not expecting the extra zeros. Since I am seeing different output between the two different programs I am guessing (hoping) there is a database option, connection string option or extra command parameter that controls how the money types are converted to a string for the XML output.

Does such a thing exist and if so how can I set the value to keep my output consistent? Preferably without the extra zeros.

Upvotes: 2

Views: 509

Answers (1)

tobbiefox
tobbiefox

Reputation: 85

What I do is to use CONVERT(VARCHAR, xxx) to get around this.

Here's an example tested in SQL Server 2008 R2:

DECLARE @foo MONEY
SELECT @foo = 15.95

SELECT @foo AS 'foo' FOR XML PATH('')
SELECT CONVERT(varchar, @foo) AS 'fee' FOR XML PATH('')

Upvotes: 1

Related Questions