CODEWITHSUNDEEP
sqlsql-serverxmlreplacexquery-sql
SARAVANAN CHINNU
SARAVANAN CHINNU

Reputation: 59

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?

Upvotes: 3

Views: 95

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22311

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>

Upvotes: 3

Related Questions