"incorrect syntax near modify" while updating an xml type of column value

Question

I have a XML type of column called "Details" in my "table" in SQL Server 2008. The value of this column is:

<output xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Request>
    <header>
      <message-id>300_500</message-id>
      <userid>eray</userid>
      <timestamp>2012-01-01T12:00:0.000</timestamp>
      <currentstatus>COMPLETED</currentstatus>
    </header>
    ...
  </Request>
</output>

What I want to do is to replace the timestamp value in the xml column (details) of Table with another timestamp value from Table2 by using an update statement. This is what I am trying:

update t
set t.Details.modify('replace value of 
    (/output/Request/header/timestamp/text())[1] with 
    ("'+t2.MessageTimestamp+'")')
from Table t
inner join Table2 t2 on t2.apptId = t1.apptId

However, it is not working and giving me the error:

Incorrect syntax near 'modify'

I tried to fix it for a while by reading many articles online, but could not see what is wrong here. Any help would be appreciated.

Answer 1

I think it's this you are looking for

DECLARE @mockupT1 TABLE(ID INT,apptId INT,Details XML);
INSERT INTO @mockupT1 VALUES
(1,1,N'<output xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Request>
    <header>
      <message-id>300_500</message-id>
      <userid>eray</userid>
      <timestamp>2012-01-01T12:00:0.000</timestamp>
      <currentstatus>COMPLETED</currentstatus>
    </header>
    ...
  </Request>
</output>');

DECLARE @mockupT2 TABLE(apptId INT,MessageTimeStamp DATETIME);
INSERT INTO @mockupT2 VALUES(1,GETDATE());

update t1
set Details.modify('replace value of
 (/output/Request/header/timestamp/text())[1] with sql:column("t2.MessageTimeStamp")')
from @mockupT1 AS t1
inner join @mockupT2 t2 on t2.apptId = t1.apptId;

SELECT * FROM @mockupT1;

Within the XQuery only literals are allowed. But T-SQL added sql:variable() and sql:column() to XQuery. This allows to use the value of a declared variable or a column's value.