T-SQL Replace XML node

There is no direct approach to replace a complete node with another one.

But you can delete it and insert the new one:

DECLARE @xmlSource AS XML = '<Root><Transactions><ReplaceMe>This information should be gone</ReplaceMe></Transactions></Root>'
DECLARE @xmlInsert AS XML = '<NewNode>New Information</NewNode>'
DECLARE @NodeName NVARCHAR(500) = 'ReplaceMe'

SET @xmlSource.modify('delete /Root/Transactions/*[local-name(.) eq sql:variable("@NodeName")]');

SELECT @xmlSource; --ReplaceMe is gone...

SET @xmlSource.modify('insert sql:variable("@xmlInsert") into (/Root/Transactions)[1]');

SELECT @xmlSource; 

The result

<Root>
  <Transactions>
    <NewNode>New Information</NewNode>
  </Transactions>
</Root>

UPDATE a generic solution

From your comments I understand, that you have no idea about the XML, just the need to replace one node where you know the name with another node...

This solution is string based (which is super ugly anyway) and has some flaws:

• If there are several nodes with this name, only the first one will be taken
• If the node exists multiple times with the same content, it would be replaced in both places
• If your xml contains CDATA-sections they will be transfered into properly escaped normal XML implicitly. No semantic loss, but this could break structural validations...

This should work even with special characters, as all conversions are from XML to NVARCHAR and back. Escaped characters should stay the same on both sides.

Otherwise one had to use a recursive approach to get the full path to the node and build my first statement dynamically. This was cleaner but more heavy...

DECLARE @xmlSource AS XML = '<Root><Transactions><ReplaceMe>This information should be gone</ReplaceMe></Transactions></Root>'
DECLARE @xmlInsert AS XML = '<NewNode>New Information</NewNode>'
DECLARE @NodeName NVARCHAR(500) = 'ReplaceMe'

SELECT 
CAST( 
REPLACE(CAST(@xmlSource AS NVARCHAR(MAX))
       ,CAST(@xmlSource.query('//*[local-name(.) eq sql:variable("@NodeName")][1]') AS NVARCHAR(MAX))
       ,CAST(@xmlInsert AS NVARCHAR(MAX))
        )
AS XML) 

To replace in place with XML we'll need to insert our new nodes immediately before (or after) the nodes we want to replace.

Start off by getting the number of nodes we want to replace:

DECLARE @numToReplace int = @xmlSource.value('count(//*[local-name(.) eq sql:variable("@NodeName")])', 'int')

Then iterate through each node and flag the node to be deleted (this lets us replace the nodes with a node of the same name).

DECLARE @iterator int = @numToReplace
WHILE @iterator > 0
BEGIN
    SET @xmlSource.modify('insert attribute ToDelete {"delete"} into ((//*[local-name(.) eq sql:variable("@NodeName")])[sql:variable("@iterator")])[1]');
    SET @iterator = @iterator - 1
END

n.b. you need to nest the target query ((*query*)[sql:variable("@numToReplace")])[1], it doesn't like variables in the last node indexer

Then insert the new node before each old node

SET @iterator = @numToReplace
WHILE @iterator > 0
BEGIN
    SET @xmlSource.modify('insert sql:variable("@xmlInsert") before ((//*[local-name(.) eq sql:variable("@NodeName")][@ToDelete="true"])[sql:variable("@iterator")])[1]')
    SET @iterator = @iterator - 1;
END

Then you can just remove all the old nodes

SET @xmlSource.modify('delete (//*[local-name(.) eq sql:variable("@NodeName")][@ToDelete="true"])')