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?