Modifying date field in XML document stored in SQL Server table

Question

I have an xml document stored in a SQL Server table xml column that has inaccurate times in the date fields

I 'd like to update all the date and time (SaleDateTime, LineStartTime, LineEndTime) values in the document by 15 seconds so, for instance, 2012-02-01T00:07:50 becomes 2012-02-01T00:08:05 (long story as to why it needs to be this way; it's out of my hands). There can be 1 to many transactions and each transaction can have 1 or more line entries.

I have tried OPENXML, modify method, etc with DATEADD and I can't get it right. I am at my wits end. Any help is appreciated. Thanks in advance!!

Sample is below

CREATE TABLE XMLTable (doc xml);

INSERT INTO XMLTable (doc)
VALUES
(
'<?xml version="1.0" encoding="UTF-8"?>
<Root>
<Transaction>
  <SaleID>1</SaleID>
  <Sale>
    <SaleDateTime>2012-02-01T00:07:00</SaleDateTime>
    <LineItem>
      <Line>1</Line>
      <LineStartTime>2012-02-01T00:07:00</LineStartTime>
      <LineEndTime>2012-02-01T00:07:00</LineEndTime>
          <Amount>13.50</Amount>
    </LineItem>
  </Sale>
</Transaction>
<Transaction>
  <SaleID>2</SaleID>
  <Sale>
    <SaleDateTime>2012-02-01T00:11:00</SaleDateTime>
    <LineItem>
      <Line>1</Line>
      <LineStartTime>2012-02-01T00:11:00</LineStartTime>
      <LineEndTime>2012-02-01T00:11:00</LineEndTime>
          <Amount>13.50</Amount>
    </LineItem>
    <LineItem>
      <Line>2</Line>
      <LineStartTime>2012-02-01T00:11:00</LineStartTime>
      <LineEndTime>2012-02-01T00:11:00</LineEndTime>
          <Amount>5.22</Amount>
    </LineItem>
  </Sale>
</Transaction>
</Root>')

SELECT * FROM XMLTable 

Answer 1

You can use the modify method. For example to replace the first occurance of SaleDateTime:

declare @now datetime = getdate()

update  XMLTable
set     doc.modify('replace value of (/Root/Transaction/Sale/SaleDateTime/text())[1] 
                    with sql:variable("@now")') 

Answer 2

If these XML documents are not huge, or this is a one-time thing and performance is not a priority, you can cast the document as varchar and perform a REPLACE on it to increment the dates.

An example is below (you might wrap this in a function):

declare @doc xml = 
'<?xml version="1.0" encoding="UTF-8"?>
<Root>
<Transaction>
  <SaleID>1</SaleID>
  <Sale>
    <SaleDateTime>2012-02-01T00:07:00</SaleDateTime>
    <LineItem>
      <Line>1</Line>
      <LineStartTime>2012-02-01T00:07:00</LineStartTime>
      <LineEndTime>2012-02-01T00:07:00</LineEndTime>
          <Amount>13.50</Amount>
    </LineItem>
  </Sale>
</Transaction>
<Transaction>
  <SaleID>2</SaleID>
  <Sale>
    <SaleDateTime>2012-02-01T00:11:00</SaleDateTime>
    <LineItem>
      <Line>1</Line>
      <LineStartTime>2012-02-01T00:11:00</LineStartTime>
      <LineEndTime>2012-02-01T00:11:00</LineEndTime>
          <Amount>13.50</Amount>
    </LineItem>
    <LineItem>
      <Line>2</Line>
      <LineStartTime>2012-02-01T00:11:00</LineStartTime>
      <LineEndTime>2012-02-01T00:11:00</LineEndTime>
          <Amount>5.22</Amount>
    </LineItem>
  </Sale>
</Transaction>
</Root>'

declare @New xml = @doc;

;with 
dates (LineStartTime, LineEndTime) as
    (   -- get the start/end dates in any LineItem
        select  p.n.value('(LineStartTime)[1]', 'datetime'),
                p.n.value('(LineEndTime)[1]', 'datetime')
        from    @doc.nodes('Root/Transaction/Sale/LineItem')p(n)
    ),
upd (OldValue, NewValue) as
    (   -- add 15 min to each, and cast as varchar
        select  '<LineStartTime>' + convert(varchar, LineStartTime, 126) + '</LineStartTime>', 
                '<LineStartTime>' + convert(varchar, dateadd(mi, 15, LineStartTime), 126) + '</LineStartTime>'
        from    dates 
        union 
        select  '<LineEndTime>' + convert(varchar, LineEndTime, 126) + '</LineEndTime>', 
                '<LineEndTime>' + convert(varchar, dateadd(mi, 15, LineEndTime), 126) + '</LineEndTime>' 
        from    dates
    )
-- cast @doc as varchar, and replace each occurrence of start/end elements with NewValue
select  @new = cast(replace(cast(@new as varchar(max)), OldValue, NewValue) as xml)
from    upd;

select [Old]=@doc, [New]=@new;

Answer 3

If the structure of the XML document is known to you the fastest option might be to shred the document apart a rebuild it using for xml. This solution can also easily be modified to do the transformation across multiple rows in a table in one go instead of processing one document at a time.

For the structure given in the question the query against a XML variable would look like this.

select T.X.value('(SaleID/text())[1]', 'int') as SaleID,
       (
       select dateadd(second, 15, S.X.value('(SaleDateTime/text())[1]', 'datetime')) as SaleDateTime,
              (
              select L.X.value('(Line/text())[1]', 'int') as Line,
                     dateadd(second, 15, L.X.value('(LineStartTime/text())[1]', 'datetime')) as LineStartTime,
                     dateadd(second, 15, L.X.value('(LineEndTime/text())[1]', 'datetime')) as LineEndTime,
                     L.X.value('(Amount/text())[1]', 'varchar(20)') as Amount
              from S.X.nodes('LineItem') as L(X)
              for xml path('LineItem'), type
              )
       from T.X.nodes('Sale') as S(X)
       for xml path('Sale'), type
       )
from @doc.nodes('/Root/Transaction') as T(X)
for xml path('Transaction'), root('Root'), type

As I said this can be modified to work against a table instead and even be changed into updating the XML column.