使用 XQuery 修改修复 SQL 更新以在 SQL 2005 上工作

Question

我正在尝试将表中的一堆字段移动到同一个表中包含的 xml blob 中。成功后，我将从表中删除该列。下面是我提出的一个非常简单的版本（没有删除列），这在 SQL 2008 上运行良好 - 但是我发现这在 SQL 2005 上不起作用。我收到错误 XQuery：XQuery 中不支持 SQL 类型“datetime”。 由于字段数量的原因，我实际上是通过在 SP 中执行构造的 SQL 语句来完成此操作的，但为了简单起见，我使用了普通的 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 指出，直到 SQL 2008 才引入 .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]')

与更新语句相比，使用此方式的输出存在微小差异。如果它们是.000，它将省略毫秒。如果它们确实具有值，则会将其包含在内，这样您就不会丢失任何数据。只是不同而已。

第二部分：

我不太明白这与上面的联系如何足够好，无法为您提供一些示例代码。但是，如果您需要从其他表添加更多内容，您可以联接到 CTE 中的这些表，并将这些列作为 CTE 的输出，以便在插入值的修改语句中使用。