SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OPENXML error converting nvarchar to numeric.


OPENXML error converting nvarchar to numeric.

Author
Message
neilswart
neilswart
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 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 ??



oabusa
oabusa
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 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.



neilswart
neilswart
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 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.



Graham O'Daniel
Graham O'Daniel
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 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' )

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search