T-SQL Replace XML node

Question

I would like to replace the XML node with a new node. I am trying to make this dynamic, so replacing node name is a variable

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'

The resulting XML should look like this:

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

Answer 1

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

But you can delete it and insert a 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; 

Result

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

UPDATE General Solution

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

This solution is line-based (which is super ugly anyway) and has some disadvantages:

• If there are several nodes with this name, only the first one will be executed
• node ,
• xml CDATA, XML . , ...

, XML NVARCHAR . .

, node . , ...

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) 

Answer 2

XML, ( ) , .

, :

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

node node, ( node ).

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. ((*query*)[sql:variable("@numToReplace")])[1], node

node 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

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