InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 23:35:52 GMT20http://www.sqlservercentral.com/Forums/Topic13069-21-1.aspxHello all,Please see the filter I have provided on the SFARBE column in the WITH block. This avoids including blank strings.[code="plain"]SELECT SFARBE,RREFERENZ FROM OPENXML (@idoc, 'FARBE' ,1) WITH ( SFARBE NUMERIC(21,0) '/FARBE/FARBE.INFR/SFARBE[.!=""]',RREFERENZ CHAR(20) '/FARBE/FARBE.INFR/RREFERENZ' )[/code]Mon, 04 Mar 2013 14:47:44 GMTGraham O'Danielhttp://www.sqlservercentral.com/Forums/Topic13069-21-1.aspxThanks 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. Thu, 12 Jun 2003 02:10:00 GMTneilswarthttp://www.sqlservercentral.com/Forums/Topic13069-21-1.aspxEmpty 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. Wed, 11 Jun 2003 15:01:00 GMToabusahttp://www.sqlservercentral.com/Forums/Topic13069-21-1.aspxPlease take a look a the following.I do a select for OPENXML but it returns withServer: Msg 8114, Level 16, State 5, Line 24Error converting data type nvarchar to numeric.because the value for sfarbe is empty or null.DECLARE @idoc INTDECLARE @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, @docSELECT 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 @idocIs there a workaround for this ?? Wed, 11 Jun 2003 04:57:00 GMTneilswart