title: Modifying date field in XML document stored in SQL Server table
body: <p>I have an xml document stored in a SQL Server table xml column that has inaccurate times in the date fields</p>
  
  <p>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.</p>
  
  <p>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!!</p>
  
  <p>Sample is below</p>
  
  <pre><code>CREATE TABLE XMLTable (doc xml);
  
  INSERT INTO XMLTable (doc)
  VALUES
  (
  '&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;Root&gt;
  &lt;Transaction&gt;
    &lt;SaleID&gt;1&lt;/SaleID&gt;
    &lt;Sale&gt;
      &lt;SaleDateTime&gt;2012-02-01T00:07:00&lt;/SaleDateTime&gt;
      &lt;LineItem&gt;
        &lt;Line&gt;1&lt;/Line&gt;
        &lt;LineStartTime&gt;2012-02-01T00:07:00&lt;/LineStartTime&gt;
        &lt;LineEndTime&gt;2012-02-01T00:07:00&lt;/LineEndTime&gt;
            &lt;Amount&gt;13.50&lt;/Amount&gt;
      &lt;/LineItem&gt;
    &lt;/Sale&gt;
  &lt;/Transaction&gt;
  &lt;Transaction&gt;
    &lt;SaleID&gt;2&lt;/SaleID&gt;
    &lt;Sale&gt;
      &lt;SaleDateTime&gt;2012-02-01T00:11:00&lt;/SaleDateTime&gt;
      &lt;LineItem&gt;
        &lt;Line&gt;1&lt;/Line&gt;
        &lt;LineStartTime&gt;2012-02-01T00:11:00&lt;/LineStartTime&gt;
        &lt;LineEndTime&gt;2012-02-01T00:11:00&lt;/LineEndTime&gt;
            &lt;Amount&gt;13.50&lt;/Amount&gt;
      &lt;/LineItem&gt;
      &lt;LineItem&gt;
        &lt;Line&gt;2&lt;/Line&gt;
        &lt;LineStartTime&gt;2012-02-01T00:11:00&lt;/LineStartTime&gt;
        &lt;LineEndTime&gt;2012-02-01T00:11:00&lt;/LineEndTime&gt;
            &lt;Amount&gt;5.22&lt;/Amount&gt;
      &lt;/LineItem&gt;
    &lt;/Sale&gt;
  &lt;/Transaction&gt;
  &lt;/Root&gt;')
  
  SELECT * FROM XMLTable 
  </code></pre>