Replace node value which occurs multiple places in the XML of SQL Server

I tried with below code to replace the values of user_input_attn_obligee_desc which exists in multiple places in the XML with the value EDWIN CHAND.

BEGIN

DECLARE @d1 XML = '
<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';

DECLARE @counter INT = 1;
DECLARE @nodeCount INT = @d1.value('count(/root//*[(@name=sql:variable("@element_name"))])', 'INT');

WHILE @counter <= @nodeCount
BEGIN
    SET @d1.modify('replace value of (/root//*[(@name=sql:variable("@element_name"))])[sql:variable("@counter")] with sql:variable("@d")');
    SET @counter = @counter + 1;
END;

SELECT @d1;

END

But I get this error:

XQuery [modify()]: The target of 'replace' must be at most one node, found 'element(*,xdt:untyped) *'

What I have to change in the code to fix this?

Please try the following solution.

It better and much more safe to use .exist() XQuery method instead of relying on counters.

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>