CODEWITHSUNDEEP
sqlsql-server
VAAA
VAAA

Reputation: 15039

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

I have a XML column 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 each value by 1 week.

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

Upvotes: 0

Views: 140

Answers (1)

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

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

Upvotes: 2

Related Questions