Fixing SQL Update using XQuery modify to work on SQL 2005

Question

I'm trying to move a bunch of fields from a table into an xml blob contained within the same table. After this is successful I will be removing the column from the table. A really simple version (without the drop column) of what I've come up with is below, and this works fine on SQL 2008 - however I've discovered that this will not work on SQL 2005. I get the error XQuery: SQL type 'datetime' is not supported in XQuery. I'm actually doing this through the execution of a constructed SQL statement within a SP because of the number of fields, but for simplicity I've used a normal statement in the example:

if OBJECT_ID('tempdb..#Case') is not null
 DROP Table #Case;

CREATE TABLE #Case 
( 
    id INT, 
    DecisionDate DateTime,
    CaseBlob xml
)

INSERT INTO #Case Values(1, '10-OCT-2011 10:10:00', '<CaseBlob></CaseBlob>')
INSERT INTO #Case Values(2, '20-OCT-2011 10:10:00', '<CaseBlob></CaseBlob>')
INSERT INTO #Case Values(3, null, '<CaseBlob></CaseBlob>')
INSERT INTO #Case Values(4, '21-OCT-2011 10:10:00', '<CaseBlob></CaseBlob>')
INSERT INTO #Case Values(5, null, '<CaseBlob></CaseBlob>')


UPDATE #Case
   SET CaseBlob.modify('insert <DecisionDate>{sql:column("#Case.DecisionDate")}</DecisionDate>
   as last into (/CaseBlob)[1]')
   WHERE #Case.DecisionDate is not null
   AND CaseBlob.exist('(/CaseBlob/DecisionDate)') = 0

SELECT * FROM #CASE

I've tried wrapping the sql:column("#Case.DecisionDate") with xs:string(sql:column("#Case.DecisionDate")) but that doesn't seem to help. It has been pointed out by @marc_s that the use of sql:column( within a .modify(insert statement wasn't introduced until SQL 2008 - so I think this is a red herring.

Due to the fact this is a one off migration script and only requires to be run once, am I thinking that I should move away from the Set methods and move to a procedural looping method to cater for my requirements. Does this sound like the correct approach due to the limitation of server version and what I'm trying to achieve? Any pointers greatly appreciated.

Answer 1

First part:

You can use a CTE to query the date part converted to a string using the date time style 126.

;with C as
(
  select CaseBlob,
         convert(varchar(23), DecisionDate, 126) as DecisionDate
  from #Case
  where DecisionDate is not null and
        CaseBlob.exist('(/CaseBlob/DecisionDate)') = 0
)
update C
set CaseBlob.modify('insert <DecisionDate>{sql:column("DecisionDate")}</DecisionDate>
                     as last into (/CaseBlob)[1]')

There is a tiny difference in the output using this compared to your update statement. It will omit the milliseconds if they are .000. If they actually have a value it will be included so you are not missing any data. It's just different.

Second part:

I don't really understand how this connects to the above well enough to give you some sample code. But if you need to add more stuff from other tables you can join to those tables in the CTE and make the columns as output from the CTE to be used in the modify statement inserting values.