2

how can to prevent duplicate inserts for every Execute?

insert into  IQ (XMLDT)values('<row>sdsdf</row>')
  • 4
    Can we get just a wee bit more code or story to go with this? – jcolebrand Dec 14 '12 at 18:12
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.