sql-server - 为 xml 列执行 "insert only if node with specific value does not exist"的有效方法

Question

比如说，我有一个看起来像这样的 xml 列:

<mi>
  <m>42</m>
</mi>

假设表:

Word(WordId:bigint, Wordtext:nvarchar, MessageIndex:xml)

如果@MessageId 已经存在于 Messageindex 的 xml 树中的某处，我不希望以下参数化查询插入新的 xml 节点，而是要么失败并返回确定性错误代码，要么静默:

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

从 Word 中选择 WordId where WordText = @WordText;

如何高效实现这一目标？

Answer 1

这样的事情对你有用吗？

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