TSQL: How can I update the xml tag value with the xml tag value from another related table?

Question

How to update xml tag value with xml tag value from another related table?

something like that:

UPDATE v2 SET [xml].modify ('replace value of (//TAG1/text())[1] with "CAST(v1.[xml].query(''//TAG2'') AS NVARCHAR(MAX))"') FROM table2 v2, table1 v1 WHERE v2.id = v1.id 

Answer 1

I don’t think you can do it in one step, but you can do it in two steps if you are on SQL Server 2008:

 DECLARE @NewValue NVARCHAR(50) SELECT @NewValue = [xml].value('(//TAG2)[1]', 'NVARCHAR(50)') FROM dbo.v1 WHERE id = 1 UPDATE dbo.v2 SET [xml].modify('replace value of (//TAG1/text())[1] with sql:variable("@NewValue")') WHERE id = 1 

The ability to specify sql:variable in replace value of XQuery is a new feature in SQL Server 2008, so if you are stuck in 2005, unfortunately, this will not work.

Answer 2

I am very late to this question, but if you plan on mass updating the XML column in the future and you are in SQL 2005+, you can use CTE to do this:

 WITH NewXmlData AS ( SELECT v2.Id AS id , CAST(v1.[xml].query('//TAG2') AS NVARCHAR(MAX)) AS NewValue FROM table2 AS v2 INNER JOIN table1 AS v1 ON v2.id = v1.id ) UPDATE v2 SET [xml].modify ('replace value of (//TAG1/text())[1] with sql:column("NewXmlData.NewValue")') FROM table2 AS v2 INNER JOIN NewXmlData AS nxd ON v2.id = nxd.id 

Answer 3

The function with sql:variable also works on server 2005. You just need to put everything in curly braces :

change ("replace the value (// TAG1 / text ()) [1] with {sql: variable (" @NewValue ")} ')