March 22, 2011 at 1:47 am
Given the code below, can someone please explain why the first select statement gives the desired result, but the second select statement results in an error "Msg 8114, Level 16, State 5, Line .. Error converting data type nvarchar to numeric"?
The only difference is the datatype. Notice that in the XML in the second TRANSACTIONS element the value for the price attribute is empty.
DECLARE @XML XML
SELECT @XML = N'
<TRANSACTIONS price="0.06" quantity="2" />
<TRANSACTIONS price="" quantity="3" />'
SELECT
Trans.data.query('data(@quantity)').value('.', 'INT' ) quantity,
Trans.data.query('data(@price)').value('.', 'REAL' ) price
FROM @XML.nodes('/TRANSACTIONS') AS Trans(Data) ;
SELECT
Trans.data.query('data(@quantity)').value('.', 'INT' ) quantity,
Trans.data.query('data(@price)').value('.', 'NUMERIC(12,3)' ) price
FROM @XML.nodes('/TRANSACTIONS') AS Trans(Data) ;
SQL Server 2008 R2
March 22, 2011 at 10:50 am
It must have to do with how SQL Server handles data-type conversions internally. The rule surrounding converting character data to the NUMERIC/DECIMAL data type requires that there be either a whole number -or- a decimal point with at least one number after the decimal.
From http://msdn.microsoft.com/en-us/library/ms191530.aspx#_character:
Character expressions that are being converted to an exact numeric data type must consist of digits, a decimal point, and an optional plus (+) or minus (-). Leading blanks are ignored. Comma separators, such as the thousands separator in 123,456.00, are not allowed in the string.
Unfortunately the article does not say anything explicit about your specific case when converting from character data to an approximate data type as to why it would accept an empty string but I suspect it something to do with the fact that you can submit character representations for exponential notations like e or E and they'll work. This may also play into why the ISNUMERIC function may not always behave as one might expect.
DECLARE @n NUMERIC(12,3) ;
SET @n = '' ; -- fails
SET @n = '.' ; -- fails
SET @n = '.0' ; -- OK
SET @n = '0' ; -- OK
GO
DECLARE @n FLOAT ;
SET @n = '' ; -- OK
GO
DECLARE @n REAL ;
SET @n = '' ; -- OK
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2011 at 2:16 am
Thank you for your reply, opc.three.
Still confusing stuff, but what you say makes kind of sense.
Using the REAL datatype instead of a NUMERIC(12,3) solved a tricky problem for a customer, so I am content at the moment. 😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply