Data Conversion Issue

May 14 2020 11:11 AM

My below code works fine, where i get the colum(Payment1 and Payment2) values from [TEMP_PAYMENT_TABLE] to MY_TABLE.

When i add the add third colum(Payment_Due) that does substract operation(Column1 "MINUS" Colum2)
I get the below error

Conversion failed when converting the nvarchar value '0.0000' to data type int.

Here the are few null values also in colum 1 and colum2
  1. WITH mycte AS (  
  2. SELECT [PROCESS]  
  3. ,TRY_CAST([LOAD_XML] AS XML) bond  
  4. FROM [TEMP_PAYMENT_TABLE]  
  5. )  
  6. INSERT INTO [dbo].[MY_TABLE]  
  7. (,[PAYMENT_VALUE1]  
  8. ,[PAYMENT_VALUE2]  
  9. --,[PAYMENT_DUE]  
  10. SELECT   
  11. S.a.value('(Compamy/Payment1/text())[1]''nvarchar(255)') Payment1  
  12. ,S.a.value('(Compamy/Payment2/text())[1]''nvarchar(255)') Payment2  
  13. --,S.a.value('(Compamy/Payment1/text())[1]', 'nvarchar(255)')-S.a.value('(Compamy/Payment2/text())[1]', 'nvarchar(255)')  
  14. FROM mycte t  
  15. CROSS APPLY t.Payment_xmlload.nodes('/.') S(a)  
  16. --select * from [dbo].[MY_TABLE]  


Answers (1)