OPENXML error converting nvarchar to numeric.

Question

OPENXML error converting nvarchar to numeric.

neilswart

Mr or Mrs. 500

Points: 505
More actions
June 11, 2003 at 4:57 am

#64859

Please take a look a the following.I do a select for OPENXML but it returns with
Server: Msg 8114, Level 16, State 5, Line 24
Error converting data type nvarchar to numeric.
because the value for sfarbe is empty or null.
DECLARE @idoc INT
DECLARE @doc VARCHAR(3000)
SET @doc =
'<?xml version="1.0"?>
<FARBE>
<FARBE.INFR>
<SFARBE></SFARBE>
<SFIRMA></SFIRMA>
<SGUELTIGVON></SGUELTIGVON>
<DGUELTIGBIS></DGUELTIGBIS>
<RREFERENZ>\*blue\*</RREFERENZ>
<XUFNUMMER></XUFNUMMER>
<XRALNUMMER></XRALNUMMER>
<XSPRACHE></XSPRACHE>
<XTEXT></XTEXT>
<XKURZTEXT></XKURZTEXT>
<XLANGTEXT></XLANGTEXT>
<XABKUERZUNG></XABKUERZUNG>
</FARBE.INFR>
</FARBE>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT SFARBE,RREFERENZ FROM OPENXML (@idoc, 'FARBE' ,1)
WITH ( SFARBE NUMERIC(21,0) '/FARBE/FARBE.INFR/SFARBE',
RREFERENZ CHAR(20) '/FARBE/FARBE.INFR/RREFERENZ' )
EXEC sp_xml_removedocument @idoc
Is there a workaround for this ??

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

oabusa Old Hand Points: 351 More actions · Answer 1

Empty or null elements within an xml fragment fail to convert when numeric or decimal format is used, eventhough such conversion goes without a problem In a normal select statment. However, I noticed that float works just fine. Why? I don't know. I used the following approach to this problem:

a) if precision and scale components of the number ( e.g. Decimal(19,10) ) are important then I make sure default of 0 is used in the xml fragment instead of empty or null.

b)If percision and scale arenot important then I use float in the select statemnt. In your case you used Numeric(20,0) to report just the integer part. Float will work fine.

Hope this helps.

neilswart Mr or Mrs. 500 Points: 505 More actions · Answer 2

Thanks for the info.That sounds like a greate idea.

I have used the edgetable as a workaround and then just cast the value which now also works.

Graham O'Daniel Old Hand Points: 371 More actions · Answer 3

Hello all,

Please see the filter I have provided on the SFARBE column in the WITH block. This avoids including blank strings.

SELECT SFARBE,RREFERENZ FROM OPENXML (@idoc, 'FARBE' ,1)

WITH ( SFARBE NUMERIC(21,0) '/FARBE/FARBE.INFR/SFARBE[.!=""]',

RREFERENZ CHAR(20) '/FARBE/FARBE.INFR/RREFERENZ' )