Say, I have a xml column that looks like this:
<mi>
<m>42</m>
</mi>
Assuming table:
Word(WordId:bigint, Wordtext:nvarchar, MessageIndex:xml)
I do NOT want the following parameterized query to insert a new xml node if @MessageId already exists somewhere in the xml tree of Messageindex, but rather either fail with a deterministic error code, or silently:
begin try
insert into Word (WordText, MessageIndex) values (@WordText, '<mi></mi>');
update Word set MessageIndex.modify('insert (<m>{sql:variable(""@MessageId"")}</m>) into(/mi)[1]') where WordId = scope_identity();
end try
begin catch
if error_number() = 2627
begin
update Word set
MessageIndex.modify('insert (<m>{sql:variable(""@MessageId"")}</m>) into(/mi)[1]')
where
WordText = @WordText;
end
else
throw
end catch
select WordId from Word where WordText = @WordText;
How do I make this happen efficiently?
Something like this work for you?
DECLARE @Word TABLE (WordId bigint identity, Wordtext NVARCHAR(20), MessageIndex xml )
insert into @word ( messageIndex )
select '<mi>
<m>42</m>
</mi>'
DECLARE @WordText NVARCHAR(20) = 'wordText'
DECLARE @messageId INT = 42
begin try
if exists ( select 1 from @Word where MessageIndex.exist('//mi[.=sql:variable("@MessageId")]') = 0 )
begin
insert into @Word (WordText, MessageIndex) values (@WordText, '<mi></mi>');
update @Word set MessageIndex.modify('insert <m>{sql:variable("@MessageId")}</m> into (mi)[1]')
where WordId = scope_identity();
end
--else
-- do something here?
end try
begin catch
if error_number() = 2627
begin
update @Word set MessageIndex.modify('insert <m>{sql:variable("@MessageId")}</m> into (mi)[1]')
where WordText = @WordText;
end
else
throw
end catch
select * from @Word