Updating a date in XML with xquery

• Jason-356205

  SSC Veteran

  Points: 281

  More actions

  January 20, 2009 at 2:42 pm

  #128153

  I have an XML untyped column in my table with data like this:

  <Appointment/>

  <Date>1/14/2008</Date>

  </Appointment>

  I'd like to add 1 year to the date through a query, but am having a hard time formulating the xquery to do so.

  Thanks for your help!

  Jay

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 20, 2009 at 2:44 pm

  #929986

  Look up "modify() method" in Books Online. It has directions on updating XML data.

  Edit: Actually, just took another look at it, and it's pretty sparse. Check out the sample XML applications on MSDN for better data.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• jacob sebastian

  SSChampion

  Points: 11822

  More actions

  January 20, 2009 at 7:51 pm

  #930038

  Here is on option:

  DECLARE @x XML

  SELECT @x = '

  [Appointment]

  [Date]1/14/2008[/Date]

  [/Appointment]'

  DECLARE @d VARCHAR(10)

  SELECT @d = CONVERT( VARCHAR,

  DATEADD(year, 1, @x.value('(Appointment/Date)[1]','DATETIME')),

  101)

  SET @x.modify('

  replace value of (Appointment/Date/text())[1]

  with sql:variable("@d")

  ')

  SELECT @x

  /*

  [Appointment]

  [Date]1/14/2008[/Date]

  [/Appointment]

  */

  I have posted a collection of tutorials on common XQuery tasks here:

  http://blog.sqlserver.me/2008/06/xquery-labs-collection-of-xquery-sample.html

  .