替换SQL Server XML中多处出现的 node 值

请try 以下解决方案.

使用.exist()XQuery方法而不是依赖计数器更好、更安全.

SQL个

DECLARE @d1 XML = 
N'<root>
  <first>                                                                               
    <var name="user_input_attn_obligee_desc">OBLIGEE ATTORNEY</var> 
  </first> 
  <second>
    <var name="user_input_attn_obligee_desc">saravanan</var> 
  </second>   
  <user_input_attn_obligor_desc>OBLIGOR ATTORNEY</user_input_attn_obligor_desc>                          
</root>';

DECLARE @d NVARCHAR(MAX) = 'EDWIN CHAND';
DECLARE @element_name NVARCHAR(MAX) = 'user_input_attn_obligee_desc';

WHILE @d1.exist('/root/*/var[@name=sql:variable("@element_name")][text()!=sql:variable("@d")]') = 1
BEGIN
    SET @d1.modify('replace value of 
        (/root/*/var[@name=sql:variable("@element_name")][text()!=sql:variable("@d")]/text())[1] 
        with sql:variable("@d")');
END;

-- test
SELECT @d1;

Output个

<root>
  <first>
    <var name="user_input_attn_obligee_desc">EDWIN CHAND</var>
  </first>
  <second>
    <var name="user_input_attn_obligee_desc">EDWIN CHAND</var>
  </second>
  <user_input_attn_obligor_desc>OBLIGOR ATTORNEY</user_input_attn_obligor_desc>
</root>