使用XQuery modify修复SQL Update以在SQL2005上工作

Question

我正在尝试将表中的一组字段移动到同一个表中包含的xml blob中。成功后，我将从表中删除该列。下面是我想出的一个非常简单的版本(没有drop列)，这在SQL2008上工作得很好-但是我发现这在SQL2005上不能工作。由于字段的数量，我实际上是通过在SP中执行构造的XQuery: SQL type 'datetime' is not supported in XQuery.语句来执行此操作的，但为了简单起见，我在示例中使用了一个普通语句：

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

我尝试过用xs:string(sql:column("#Case.DecisionDate"))包装sql:column("#Case.DecisionDate")，但似乎没有什么帮助。@marc_s已经指出，直到SQL2008才引入在.modify(insert语句中使用sql:column( -所以我认为这是转移注意力。

由于这是一个一次性的迁移脚本，并且只需要运行一次，我是否认为我应该从Set方法转移到过程循环方法，以满足我的需求。由于服务器版本的限制和我试图实现的目标，这听起来像是正确的方法吗？非常感谢任何人的指点。

Answer 1

第一部分：

您可以使用CTE来查询使用日期时间样式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]')

与使用update语句相比，使用此语句的输出略有不同。如果它们是.000，它将省略毫秒。如果它们确实有一个值，那么它就会被包含进来，这样你就不会遗漏任何数据。只是不同而已。

第二部分：

我真的不明白这是如何很好地连接到上面的代码来给你一些样例代码的。但是，如果您需要从其他表添加更多内容，您可以连接到CTE中的这些表，并将CTE的列作为输出用于modify语句插入值。