FIX: OPENXML WITH TableName Statement May Return Incorrect Result if Datatype is DECIMAL (300480)

MORE INFORMATION

Steps to Reproduce Behavior

1. Create the tables to be used with OPENXML:

   drop table openxmltbl_dec
   go
   drop table openxmltbl_num
   go
   create table openxmltbl_dec
   ( 
       ContactName varchar(20), 
       Rating decimal (18,10)   -- Uses DECIMAL data type
   )
   go
   create table openxmltbl_num
   ( 
       ContactName varchar(20), 
       Rating numeric (18,10)   -- Uses NUMERIC data type
   )
   go
   

2. Create an internal representation of the XML document to be used by OPENXML:

   set nocount on
   DECLARE @idoc int
   DECLARE @doc varchar(1000)
   SET @doc ='
   <ROOT>
   <Customer ContactName="Paul Henriot" Rating="1.0123456789">   </Customer>
   </ROOT>'
   
   EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

3. Execute a SELECT statement that uses the OPENXML rowset provider:

   -- The following code demonstrates the problem
   
   print 'Using WITH TableName and DECIMAL data type... incorrect results'
   select * from OPENXML (@idoc, '/ROOT/Customer',1) WITH openxmlTbl_dec
   
   -- The following code demonstrates the workaround
   
   print ''
   print 'Using WITH TableName and NUMERIC data type... correct results'
   select * from OPENXML (@idoc, '/ROOT/Customer',1) WITH openxmlTbl_num
   print ''
   print 'Using WITH SchemaDeclaration ... correct results'
   select * from OPENXML (@idoc, '/ROOT/Customer',1) WITH ( ContactName varchar(20),Rating decimal (18,10))
   
   EXEC sp_xml_removedocument @idoc

RESULTS

Using WITH TableName and DECIMAL data type, incorrect results display:

ContactName          Rating               
-------------------- -------------------- 
Paul Henriot         2828849149941180153.

Using WITH TableName and NUMERIC data type the correct results display:

ContactName          Rating               
-------------------- -------------------- 
Paul Henriot         1.0123456789

Using WITH SchemaDeclaration the correct results display:

ContactName          Rating               
-------------------- -------------------- 
Paul Henriot         1.0123456789

The correct value for Rating is 1.0123456789