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

Question

How can I update the value of an xml tag with the value of an xml tag from another related table?

something like this:

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

Makeshift

Answer 1

I don't think you can do this in a single step - but you can do it in two steps, if you're 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 a sql:variable in your replace value of XQuery is a new feature in SQL Server 2008 - so if you're stuck on 2005, this won't work, unfortunately.

Infantry

@halfjust: yes - see the method I showed. But I don't see any way to do a "mass update" - you need a variable to be able to use sql:variable - you cannot have just another XQuery expression.... – Infantry

Answer 2

I'm pretty late to this question, but if you're looking to "mass update" an XML column in the future, and you are in SQL 2005+, you can use a CTE to accomplish 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

Chantel

Thanks for answering even though you were late. This worked perfectly for my needs. – Frangipani

Answer 3

Feature with sql:variable also works on 2005 Server. You just need to put all the construction into braces:

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

Graeme