update xml - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update xml

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-17 : 14:06:38

I have MULTIPLE xml rows. I love to update the CreatedDate inside the xml to become 10 days in the future.
RESULT:
<CreatedDate>2008-22-10T00:00:00</CreatedDate>

DECLARE @myDoc xml
DECLARE @ProdID int
SET @myDoc = '<AD ADID="1" IsOnline="1" IsPrivate="1">
<CreatedDate>2008-12-10T00:00:00</CreatedDate>
<StartDate>2008-12-10T00:00:00</StartDate>
<EndDate>2011-10-14T16:22:16.530</EndDate>
<LastModification>2011-09-20T18:39:01.110</LastModification>
<Source SourceID="19" Name="Private Ads" ForeignID="135007" />
</AD>'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-17 : 14:44:57

I couldn't get the XML-DML replace to work, but delete/insert works:

declare @newdate xml
select @newdate=N'<CreatedDate>' + convert(nvarchar(20), dateadd(day, 10, @myDoc.value('(/AD/CreatedDate)[1]','datetime')),126) + N'</CreatedDate>'
set @myDoc.modify(' delete (/AD/CreatedDate)[1]')
set @myDoc.modify(' insert sql:variable("@newdate") as first into (/AD)[1]')
select @mydoc, @myDoc.value('(/AD/CreatedDate)[1]','datetime')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 15:03:49

using replace


DECLARE @myDoc xml
DECLARE @date varchar(20)
SET @date=REPLACE(CONVERT(varchar(20),DATEADD(dd,DATEDIFF(dd,0,GETDATE()),10),121),' ','T')
SET @myDoc = '<AD ADID="1" IsOnline="1" IsPrivate="1">
<CreatedDate>2008-12-10T00:00:00</CreatedDate>
<StartDate>2008-12-10T00:00:00</StartDate>
<EndDate>2011-10-14T16:22:16.530</EndDate>
<LastModification>2011-09-20T18:39:01.110</LastModification>
<Source SourceID="19" Name="Private Ads" ForeignID="135007" />
</AD>'


SET @myDoc.modify('replace value of (/AD/CreatedDate/text())[1] with sql:variable("@date")')

select @myDoc


output
--------------------------------------
<AD ADID="1" IsOnline="1" IsPrivate="1">
  <CreatedDate>2011-10-28T00:00:00.</CreatedDate>
  <StartDate>2008-12-10T00:00:00</StartDate>
  <EndDate>2011-10-14T16:22:16.530</EndDate>
  <LastModification>2011-09-20T18:39:01.110</LastModification>
  <Source SourceID="19" Name="Private Ads" ForeignID="135007" />
</AD>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-17 : 15:34:20

Thanks visakh! I could swear I tried that but must've done something wrong.

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-17 : 15:42:10

Thanks for the quick reply. however I want all my rows, the createdate to increment 10 more days.
result:
2008-12-22T00:00:00.
2009-12-23T00:00:00.
2010-12-24T00:00:00.

declare @table table (addetail xml)
insert @table
select '<AD>
<CreatedDate>2008-12-10T00:00:00</CreatedDate>

</AD>' union all
select '<AD>
<CreatedDate>2009-01-11T00:00:00</CreatedDate>

</AD>' union all
select '<AD>
<CreatedDate>2010-02-12T00:00:00</CreatedDate>

</AD>'

select REPLACE(CONVERT(varchar(20),DATEADD(dd,DATEDIFF(dd,0,GETDATE()),10),121),' ','T'), * from @table

DECLARE @myDoc xml
DECLARE @date varchar(20)
SET @date=REPLACE(CONVERT(varchar(20),DATEADD(dd,DATEDIFF(dd,0,GETDATE()),10),121),' ','T')
SElecT @myDoc = addetail from @table

SET @myDoc.modify('replace value of (/AD/CreatedDate/text())[1] with sql:variable("@date")')

update @table
set addetail = @mydoc

SELECT * FROM @TABLE

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-17 : 16:08:23

quote:
Thanks for the quick reply. however I want all my rows, the createdate to increment 10 more days.

Would've been helpful to mention that in the first place:

DECLARE @table TABLE (addetail XML)
INSERT @table
SELECT '<AD><CreatedDate>2008-12-10T00:00:00</CreatedDate></AD>' UNION ALL
SELECT '<AD><CreatedDate>2009-01-11T00:00:00</CreatedDate></AD>' UNION ALL
SELECT '<AD><CreatedDate>2010-02-12T00:00:00</CreatedDate></AD>' 

SELECT addetail.value('(/AD/CreatedDate)[1]','datetime'), * FROM @table 

;WITH CTE(addetail,newdate) AS (
SELECT addetail, DATEADD(DAY, 10, d.value('(/AD/CreatedDate)[1]','datetime'))
FROM @table t CROSS APPLY t.addetail.nodes('/AD') AS d(d))
UPDATE CTE SET addetail.modify('replace value of (/AD/CreatedDate/text())[1] with sql:column("newdate")')

SELECT addetail.value('(/AD/CreatedDate)[1]','datetime'), * FROM @table

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-17 : 16:10:39

Thanks R,

But i'm getting an error:

Msg 6739, Level 16, State 1, Line 11
XQuery: SQL type 'datetime' is not supported in XQuery.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-17 : 16:11:40

What version of SQL Server are you using? It works for me on SQL 2008 R2.

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-17 : 16:16:17

2k5 thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-17 : 16:41:02

Replace the relevant section with this:

;WITH CTE(addetail,newdate) AS (
SELECT addetail, convert(varchar(25), DATEADD(DAY, 10, d.value('(/AD/CreatedDate)[1]','datetime')),126)
FROM @table t CROSS APPLY t.addetail.nodes('/AD') AS d(d))
UPDATE CTE SET addetail.modify('replace value of (/AD/CreatedDate/text())[1] with sql:column("newdate")')

Tested on SQL 2005 Express, so should work on anything.

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-17 : 17:07:49

I wrote my own its not as good as yours but I think it did the job. Thanks!! I noticed I could use the PK to join the tables.
SELECT adid, addetail, convert(varchar(50), dATEADD(DAY, 30, d.value('(/AD/CreatedDate)[1]','datetime')), 126) newdate
into #temp6
FROM adxml t CROSS APPLY t.addetail.nodes('/AD') AS d(d) where isonline = 1

UPDATE #temp6 SET addetail.modify('replace value of (/AD/CreatedDate/text())[1] with sql:column("newdate")')

UPDATE a
set a.addetail = t.addetail
from adxml a
join #temp6 t
on a.adid = t.adid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 00:59:50

quote:
Originally posted by robvolk

Thanks visakh! I could swear I tried that but must've done something wrong.

No problem

It didnt work for me first
finally did some tweaks and managed to make it work

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources