How to avoid duplicates in XML while inserting into SQL table

See more:

SQL

SQL-Server

T-SQL

, +

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

<pre>{"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 I'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.

What I have tried:

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)') );