VAAA VAAA - 5 months ago 33
SQL Question

SQL - How to update a XML column directly in a query

I have a

column that contains the following value:

<Parameter lastweekran_start="2016-06-04 00:00:00"
lastweekran_end="2016-06-10 23:59:59" />

I need to update attributes
by increasing each value by 1 week.

Any clues on how to do this without creating a new XML?


I found two solutions, both are not very elegant but work.

declare @tbl table(id int, params xml)
insert @tbl values
(1,'<Parameters><Parameter lastweekran_start="2016-06-04 00:00:00" lastweekran_end="2016-06-10 23:59:59" /></Parameters>')
,(2,'<Parameters><Parameter lastweekran_start="2015-06-04 00:00:00" lastweekran_end="2015-06-10 23:59:59" /></Parameters>')

;with par as (--extract values and add 1 week
select id, dateadd(week,1,t.v.value('@lastweekran_start','datetime')) lastweekran_start ,
dateadd(week,1,t.v.value('@lastweekran_end','datetime')) lastweekran_end
from @tbl cross apply params.nodes('Parameters/Parameter') t(v)
update @tbl --and update using modify with replace value of
--1. lastweekran_start
set params.modify('replace value of (Parameters/Parameter/@lastweekran_start)[1]
     with sql:column("lastweekran_start")') 
--2. lastweekran_end
--set params.modify('replace value of (Parameters/Parameter/@lastweekran_end)[1]
--     with sql:column("lastweekran_end")')
from @tbl t inner join par on

--The issue here is that replace value of allows single node at a time
--So the query must run twice for lastweekran_end
--Comment 1st set block and uncomment 2nd one

B) Prepare xml value and update.

;with par as (
select id, cast(concat('<Parameters><Parameter lastweekran_start="', 
convert(varchar,dateadd(week,1,t.v.value('@lastweekran_start','datetime')),121) ,'" lastweekran_end="',
convert(varchar,dateadd(week,1,t.v.value('@lastweekran_end','datetime')),121),'" /></Parameters>') as xml) newParams
from @tbl cross apply params.nodes('Parameters/Parameter') t(v)
update @tbl
set params=par.newParams
from @tbl t inner join par on
--it looks ugly but works from a single run