SQL Server : error converting data type nvarchar to numeric

Question

I am new to this and I am trying to learn more about SQL. I have this example and still not able to figure it out why I am getting this error:

DECLARE @xmlprice XML = '<price>1234567</price>'
DECLARE @price DECIMAL(19,4);

SET @price = @xmlprice.value('sum(price)', 'DECIMAL(19,4)');

I get the following error:

Error converting data type nvarchar to numeric.

Can you share with us what you found when you Google'd that error message and attempted to troubleshoot it? — dfundako
– dfundako, Commented Aug 22, 2018 at 21:14
Yes i did, but the funny thing is that when i reduce it to 6 digits it works when i try using 7 digits it failed. I did research on google but nothing relate it to this specific problem. The precision it suppose to work with 19 digits. — Jayvi03
– Jayvi03, Commented Aug 22, 2018 at 21:15
Possible duplicate of SQL Server: Error converting data type nvarchar to numeric — dfundako
– dfundako, Commented Aug 22, 2018 at 21:16
I'm not an xml guy, but decimal(19,4) has a scale of 15 (digits to the left of the decimal). select len('price123456price') = 16. — S3S
– S3S, Commented Aug 22, 2018 at 21:23
Hi @Jayvi03 , Please convert @xmlprice.value('sum(price)') to decimal using SQL Cast or Convert function — Abdul Azeez
– Abdul Azeez, Commented Aug 22, 2018 at 21:23

David Browne - Microsoft · Accepted Answer · 2018-08-23 00:04:07Z

1

I think that's a bug, but it's such a strange thing to do I'm not sure. Normally you would project the XML values out first, and then aggregate them.

eg

select @xmlprice.value('(/price)[1]','decimal(19,4)');

This expression says to start at the root '/' and then find all the 'price' elements, pick the first one, and extract its value.

You may want to post this on the SQL Server feedback site for the product team to evaluate.

answered Aug 23, 2018 at 0:04

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jayvi03 · Accepted Answer · 2018-08-23 20:00:52Z

1

Based on your recommendation, I cast my price value to decimal with a FLWOR Statement and Iteration (XQuery) before the sum.

DECLARE @xmlprice XML = '<price>1234567</price>'
DECLARE @price DECIMAL(19,4);

SET @price = @xmlprice.value('sum(for $price in //price return $price cast as xs:decimal?)', 'DECIMAL(19,4)');

edited Aug 23, 2018 at 20:00

answered Aug 23, 2018 at 19:54

Jayvi03

393 bronze badges

Collectives™ on Stack Overflow

SQL Server : error converting data type nvarchar to numeric

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related