OPENXML error converting nvarchar to numeric.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » XML » OPENXML error converting nvarchar to numeric....

OPENXML error converting nvarchar to numeric.

Rate Topic

Display Mode

Topic Options

Author

Message

neilswart

Posted Wednesday, June 11, 2003 4:57 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 24, 2010 6:25 AM
Points: 25, Visits: 7

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 ??

Post #13069

oabusa

Posted Wednesday, June 11, 2003 3:01 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 27, 2012 8:36 AM
Points: 111, Visits: 19

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.

Post #65999

neilswart

Posted Thursday, June 12, 2003 2:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 24, 2010 6:25 AM
Points: 25, Visits: 7

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.

Post #66000

Graham O'Daniel

Posted Monday, March 04, 2013 2:47 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 22, 2013 2:54 PM
Points: 27, Visits: 58

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' )

Post #1426470

« Prev Topic | Next Topic »

Permissions