How to avoid duplicates in XML while inserting into SQL Table

[prathap3567]

Note: this is a sample code and scenario. I will not be using distinct while using select statement.

I'm trying to upload Itemcodes into an empty table for the first time using XML.

below is the XML data

{"ProductDetails":"<Root>\r\n <ProductDetails>\r\n <ItemCode>2503902</ItemCode\r\n </ProductDetails>\r\n <ProductDetails>\r\n <ItemCode>3591948</ItemCode>\r\n </ProductDetails>\r\n

<ProductDetails>\r\n <ItemCode>2503902</ItemCode\r\n </ProductDetails>\r\n</Root>"}

When m trying to insert into the table, I'm checking if the itemcode exists in the table or not and inserting as below

INSERT INTO inv_productdetails(ItemCode)

select T.N.value('(ItemCode/text())[1]', 'varchar(50)')

from @ProductDetails.nodes('Root/ProductDetails') as T(N)

where not exists (select top 1 * from dbo.inv_productdetails

where ItemCode = T.N.value('(ItemCode/text())[1]', 'varchar(50)') );

I'm not using any transaction or anything. Its a plain simple query to insert the itemcodes.

But I have noticed that while the above code runs, its not able to consider the not exists statement.

Can you please help me with a workaround.

Thanks.

[#2]

@prathap3567 Upload the data to a buffer table. Do any needed data cleansing and validation, and then transfer the data to the final destination. If genuine duplicates are possible you can use DISTINCT when pulling the data from the buffer table. Clear the buffer table before each use.