How to merge 2 XML variables in SQL Server 2008

[chicknician]

Lets assume I have 2 XML variables in SQL Server 2008 with the following XML\[code\]DECLARE @FIRST XML = '<DBPerson> <firstname>John</firstname> <lastname>Bob</lastname> </DBPerson>', @Second XML = '<FromUI> <lastname>New Bob</lastname> <age>39</age> </FromUI>';\[/code\]I want the following output:\[code\]<DBPerson> <firstname>John</firstname> <lastname>New Bob</lastname> <age>39</age></DBPerson>\[/code\]Basically I want to merge contents of 2 XML variables into one where the variable \[code\]@Second\[/code\] should hold precedence (if a node exists in both \[code\]@First\[/code\] & \[code\]@Second\[/code\], the node inside \[code\]@Second\[/code\] should be considered).The approach I have taken is to first get the list of all unique elements within both the root elements like this:\[code\]WITH ALLFields AS( SELECT x.y.value('local-name(.)','varchar(50)') As Element FROM @Second.nodes('FromUI/*') AS x(y) UNION SELECT x.y.value('local-name(.)','varchar(50)') As Element FROM @FIRST.nodes('DBPerson/*') AS x(y))SELECT * FROM ALLFields AF\[/code\]But I am clueless how to proceed from here on. I know I have to use \[code\]sql:column\[/code\] somewhere to build a table first to get just node names and their values (based on \[code\]AllFields\[/code\]) and then I can use \[code\]FOR XML PATH('DBPerson')\[/code\] to form the final xml but a bit unsure of the usage of \[code\]sql:column\[/code\]Any help highly appreciated.UPDATE:I have boiled it down to the following query:\[code\]DECLARE @FIRST XML = '<DBPerson><firstname>John</firstname><lastname>Bob</lastname></DBPerson>', @Second XML = '<FromUI><lastname>New Bob</lastname><age>39</age></FromUI>';WITH ALLFields AS( SELECT x.y.value('local-name(.)','varchar(50)') As Element FROM @Second.nodes('FromUI/*') AS x(y) UNION SELECT x.y.value('local-name(.)','varchar(50)') As Element FROM @FIRST.nodes('DBPerson/*') AS x(y)), Filtered AS( SELECT Element , @FIRST.value('(DBPerson/*[local-name()=sql:column("Element")])[1]','varchar(max)') AS F , @Second.value('(FromUI/*[local-name()=sql:column("Element")])[1]','varchar(max)') AS S FROM ALLFields AF), FinalValues AS( SELECT Element , CASE WHEN S IS NULL THEN F ELSE S END AS V FROM Filtered)SELECT * FROM FinalValues\[/code\]This query gives me a table with all the elements in one column and the data for the elements in another column. Now how do i generate my final XML like this:\[code\]<DBPerson><firstname>John</firstname><lastname>New Bob</lastname><age>39</age></DBPerson>\[/code\]