Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OPENXML error converting nvarchar to numeric. Expand / Collapse
Author
Message
Posted Wednesday, June 11, 2003 4:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, June 11, 2003 3:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, June 12, 2003 2:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Monday, March 04, 2013 2:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase

Permissions Expand / Collapse