VAAA VAAA - 4 months ago 6
SQL Question

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

I have a

XML
field that contains the following value:

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


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


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

Answer

I found two solutions, both are not very elegant but work.
A) Using T-SQL XML DML

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 t.id=par.id

--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 t.id=par.id
--it looks ugly but works from a single run
Comments