在SQL 2005上使用XQuery修改修复SQL Update

Question

我试图将一堆字段从表移动到包含在同一表中的xml blob。这是成功后，我将从表中删除列。下面是我想出的一个非常简单的版本(没有删除列)，它在SQL 2008上工作得很好——但是我发现这在SQL 2005上不起作用。由于字段的数量，我得到错误XQuery: SQL type 'datetime' is not supported in XQuery.，我实际上是通过在SP内执行构造的SQL语句来做到这一点，但为了简单起见，我在示例中使用了正常语句:

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指出，.modify(insert语句中sql:column(的使用直到SQL 2008才引入-所以我认为这是转移注意力。

由于这是一个一次性的迁移脚本，只需要运行一次，我认为我应该从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的输出，用于修改语句中插入值。