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 WITH mycte AS ( SELECT [PROCESS] ,TRY_CAST([LOAD_XML] AS XML) bond FROM [TEMP_PAYMENT_TABLE] ) INSERT INTO [dbo].[MY_TABLE] (,[PAYMENT_VALUE1] ,[PAYMENT_VALUE2] --,[PAYMENT_DUE] SELECT S.a.value( '(Compamy/Payment1/text())[1]' , 'nvarchar(255)' ) Payment1 ,S.a.value( '(Compamy/Payment2/text())[1]' , 'nvarchar(255)' ) Payment2 --,S.a.value('(Compamy/Payment1/text())[1]', 'nvarchar(255)')-S.a.value('(Compamy/Payment2/text())[1]', 'nvarchar(255)') FROM mycte t CROSS APPLY t.Payment_xmlload.nodes( '/.' ) S(a) --select * from [dbo].[MY_TABLE]

It seems like the issue with data type as per the error. Use the same data type in all the column. Thanks

Answer

Data Conversion Issue

Mansoor Mohammed

544

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

WITH mycte AS (
SELECT [PROCESS]
,TRY_CAST([LOAD_XML] AS XML) bond
FROM [TEMP_PAYMENT_TABLE]
)
INSERT INTO [dbo].[MY_TABLE]
(,[PAYMENT_VALUE1]
,[PAYMENT_VALUE2]
--,[PAYMENT_DUE]
SELECT
S.a.value('(Compamy/Payment1/text())[1]', 'nvarchar(255)') Payment1
,S.a.value('(Compamy/Payment2/text())[1]', 'nvarchar(255)') Payment2
--,S.a.value('(Compamy/Payment1/text())[1]', 'nvarchar(255)')-S.a.value('(Compamy/Payment2/text())[1]', 'nvarchar(255)')
FROM mycte t
CROSS APPLY t.Payment_xmlload.nodes('/.') S(a)
--select * from [dbo].[MY_TABLE]