"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.

I don't work with XML much, but the update query usually requires a SET a = b type of format. — Tab Alleman
– Tab Alleman, Commented Feb 5, 2018 at 14:48
set Details.modify. It is never legal to use the base table name (or an alias of it) in the set. — Jeroen Mostert
– Jeroen Mostert, Commented Feb 5, 2018 at 14:49
@JeroenMostert thanks for giving a nice clue to me to solve a further issue :) — Eray Balkanli
– Eray Balkanli, Commented Feb 5, 2018 at 15:53

Dmitriy Grishin - dogrishin · Accepted Answer · 2021-05-14 04:44:48Z

3

The issue is that 'XMLfield.modify' does not work when you attempt to address to field via full path like 'tableAlias.XMLfield.modify'. It leads to the same error: Incorrect syntax near 'modify'.

Here's a short sample demonstrating it (second update fails, but first works)

if OBJECT_ID('tempdb..#a') is not null drop table #a
create table #a(a xml,b nvarchar(max))
insert into #a(a,b) values('<a/>','123')
update t set a.modify('insert (<b>{sql:column("b")}</b>)    as last into (//a)[1]') from #a t
select * from #a
update t set t.a.modify('insert (<b>{sql:column("b")}</b>)  as last into (//a)[1]') from #a t

edited May 14, 2021 at 4:44

answered Apr 15, 2020 at 6:07

Dmitriy Grishin - dogrishin

3724 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gottfried Lesigang · Accepted Answer · 2018-02-06 09:05:25Z

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.

Eray Balkanli · Accepted Answer · 2018-02-05 15:51:57Z

0

I changed the code into the one below and it is working fine now. I could not find a way to use directly a value of another table, so I saved the value to @timestamp variable and used it while replacing as sql:variable("@timestamp").

declare @timestamp varchar(50) 
set @timestamp = (
    select cast(t2.MessageTimeStamp as varchar(50))
    from Table t
    inner join Table2 t2 on t2.apptId = t1.apptId
    where t.Id = @Id
)

update t
set Details.modify('replace value of 
        (/output/Request/header/timestamp/text())[1] with 
        sql:variable("@timestamp")')
from Table t
where Id = @Id

answered Feb 5, 2018 at 15:51

Eray Balkanli

8,05013 gold badges60 silver badges94 bronze badges

1 Comment

Gottfried Lesigang Over a year ago

This is okay, if you want to update one single row. I my answer I show you a set based approach.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related