Replacing the one child node value which is exists multiple places with in XML

Question

I need to replace the values 'OBLIGEE ATTORNEY' and 'saravanan' of child node <var..> by the value 'EDWIN CHAND'.

BEGIN
DECLARE @LS_EXECUTEQUERY_DESC NVARCHAR(4000),
        @LS_NODE_NAME VARCHAR(100)='user_input_attn_obligee_desc',
        @LS_NAME_DESC  VARCHAR(100) = 'EDWIN CHAND',
        @LS_DOCUMENT_XML XML=cast('<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>' AS XML);

 SET @LS_EXECUTEQUERY_DESC = 'SET @LS_DOCUMENT_XML.modify(''replace value of (/root//var[contains(@name,"'+(@LS_NODE_NAME)+'")] /text())[1] with "'+CAST(ISNULL(@LS_NAME_DESC,'') AS VARCHAR)+'"'')';  
 EXEC SP_EXECUTESQL @stmt = @LS_EXECUTEQUERY_DESC ,@params = N'@LS_DOCUMENT_XML xml OUTPUT' ,@LS_DOCUMENT_XML = @LS_DOCUMENT_XML OUTPUT;

             SET @LS_EXECUTEQUERY_DESC = CAST (@LS_DOCUMENT_XML AS VARCHAR(MAX)); 
             SELECT @LS_EXECUTEQUERY_DESC;

END;
             

But the above query replace the first occurrence alone not all occurrences. Even it didn't throw any error. Could please some one help me to do this. Thanks in advance.

Needed 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>*

Answer 1

When using XML modify() the replace operation must target a single node:

Expression1

Identifies a node whose value is to be updated. It must identify only a single node. That is, Expression1 must be a static singleton. If the XML is typed, the type of the node must be a simple type.

This means that to replace the contents of two separate nodes you will need two separate operations.

SQL Server also supports two XQuery Extension Functions, sql:column() and sql:variable(), which allow you to reference column and variable values from within expressions.

We can utilize sql:variable() to simplify your code to avoid the use of sp_executesql...

declare @LS_NODE_NAME nvarchar(100) = N'user_input_attn_obligee_desc',
        @LS_NAME_DESC  nvarchar(100) = N'EDWIN CHAND',
        @LS_DOCUMENT_XML 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>';

set @LS_DOCUMENT_XML.modify(N'
  replace value of (/root/first/var[@name=sql:variable("@LS_NODE_NAME")]/text())[1]
  with sql:variable("@LS_NAME_DESC")
  ');

set @LS_DOCUMENT_XML.modify(N'
  replace value of (/root/second/var[@name=sql:variable("@LS_NODE_NAME")]/text())[1]
  with sql:variable("@LS_NAME_DESC")
  ');

select @LS_DOCUMENT_XML;

This yields the result:

<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>

---

To address subsequent comments from the OP...

The XPath query used in XML modify() has a number of limitations that prevent you from using simple solutions such as alternating paths (/root/(first,second)/var[@name=sql:variable("@LS_NODE_NAME")]/text()[1]) or replacing [1] with something like [sql:variable("@NodeIndex")] in a while loop.

If you don't know ahead of time how many nodes need to be matched, or the specific XPath for all of the elements, it is possible to use the // (descendant-or-self) axis specifier along with the XML exist() method and then simply replace the text() for nodes that don't already match, such as with the following SQL...

declare @ModifyCount int = 0;
while (@ModifyCount < 10 and @LS_DOCUMENT_XML.exist('//var[@name=sql:variable("@LS_NODE_NAME")][text()!=sql:variable("@LS_NAME_DESC")]') = 1)
begin
  set @LS_DOCUMENT_XML.modify(N'
    replace value of (//var[@name=sql:variable("@LS_NODE_NAME")][text()!=sql:variable("@LS_NAME_DESC")]/text())[1]
    with sql:variable("@LS_NAME_DESC")
    ');
  set @ModifyCount += 1;
end
select @ModifyCount, @LS_DOCUMENT_XML;

The problems with this, though, include:

1. //var isn't paying attention to any ascendant node hierarchy, so may not always target the path(s) you expect.
2. So as to avoid infinite loops do not trust exist() by itself - always include a Guard Counter such as the @ModifyCount with its limit of 10 recursions.