How to avoid duplicates in XML while inserting into SQL Table

Question

question

iviewtheworldthroughmylens asked Mar 25, '21

How to avoid duplicates in XML while inserting into SQL Table

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.

t-sql sqlserver sqlserver2012

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2021-03-25T10:41:22Z

Kev Riley answered Mar 25, '21

You need to think about the insert as an atomic operation on a set of data, not row-by-row.

You are selecting all the itemcodes that do not exist in the table - so in the absence of a distinct, will produce 3 values:

2503902
3591948
2503902

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iviewtheworldthroughmylens commented · Apr 01, 2021 at 11:17 AM

yes Kev Riley...initially i was thinking,the data will be inserted row by row with the XML logic i have written, which would have been easier. I did a work around and checking the duplicates if any before inserting the data and returning message to the front end user.

0 ·

question