Sql-server – avoid insert xml duplicate data

sql serverxml

how can to prevent duplicate inserts for every Execute?

insert into  IQ (XMLDT)values('<row>sdsdf</row>')

Best Answer

Given this table:

CREATE TABLE IQ
(
    XMLDT xml NULL
);

We first need a scalar function to extract the value of interest from the XML:

CREATE FUNCTION dbo.GetRow(@XMLDT AS xml)
RETURNS nvarchar(10)
WITH SCHEMABINDING
AS
BEGIN
    RETURN @XMLDT.value('(./row/text())[1]','nvarchar(10)');
END;

Now we can add a computed column that uses the function:

ALTER TABLE dbo.IQ
ADD RowValue AS dbo.GetRow(XMLDT);

And a constraint to enforce the desired uniqueness:

ALTER TABLE dbo.IQ
ADD CONSTRAINT UQ__IQ_XMLDT_RowValue
UNIQUE (RowValue);

Demo:

-- Succeeds
INSERT INTO IQ (XMLDT)
VALUES('<row>sdsdf</row>');

-- Msg 2627, Level 14, State 1, Line 1
-- Violation of UNIQUE KEY constraint 'UQ__IQ_XMLDT_RowValue'
INSERT INTO IQ (XMLDT)
VALUES('<row>sdsdf</row>');
Related Question