Sql Server Append Xml Child Nodes To Parent Node

I need to have a script which can insert / append new xml child nodes to a pre-existing xml parent node. --New child nodes DECLARE @XMLChildData XML SET @XMLChildData = '

Solution 1:

Extract the Person nodes from @XMLChildData to a separate variable and add that to the Persons node of @XMLParentData.

DECLARE@PersonList XML

SET@PersonList=@XMLChildData.query('Persons/*')

SET@XMLParentData.modify('insert sql:variable("@PersonList") as last into /Persons[1]')

SELECT@XMLParentData

Another way is to extract the Person nodes from both variables and rebuild the Persons node using FOR XML PATH.

SET@XMLParentData= (
                     SELECT@XMLParentData.query('/Persons/Person'),
                            @XMLChildData.query('/Persons/Person')
                     FOR XML PATH(''), ROOT('Persons'), TYPE
                     )

Share