OPENXML error converting nvarchar to numeric.

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

  • 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.

  • 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.

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

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

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