Is it possible to replace an entire node with SQL DML?

Question

I am very new to the SQL DML and I could use any help in discerning if this is possible.

declare @xml XML, @id int, @new XML
SET @id = 2
SET @xml = '<root><node ids=1><child /></node><node ids=2><child /></node></root>'
SET @new = '<node ids=2,3><child /></node>'

I would like to set the entire node element off the root to be the same as @new but I am having trouble figuring out the syntax with SQL DML. I know the following does not work for a few reasons, but consider it almost pseudo-code towards the functionality I am trying to perform.

SET @xml.modify('replace value of (/root/node[@id=sql:variable("@id")]) with sql:variable(@new)')

I may have more syntax wrong than I realize since I am not familiar with this stuff yet. I know that it expects me to specify /text() for an untyped value, but I'd like to replace the wrapping "node" tag as well and that it should take the first with a [1] afterward. What is the proper syntax for handling this sort of call?

Answer 1

you can delete the node child element and then insert new node element

SET @xml.modify(' delete (/root/node)');
SET @xml.modify('insert  sql:variable("@new") into (/root)[1]  ')