Xml.modify中的SQL替换函数“替换值”

Question

我正在尝试更新Server中的一些XML数据。XML包含的数据如下所示：

<root>
    <id>1</id>
    <timestamp>16-10-2017 19:24:55</timestamp>
</root>

假设该XML存在于名为Data的列中，该列位于名为TestTable的表中。我希望能够将时间戳中的连字符更改为正斜杠。

我希望我能做这样的事情：

update TestTable
set Data.modify('replace value of 
(/root/timestamp/text())[1] with REPLACE((/root/timestamp/text())[1], "-", "/")')

我得到以下错误：

XQuery TestTable:没有函数‘{http://www.w3.org/2004/07/xpath-functions}：替换()’

当我想到它的时候，这是有道理的。但我想知道，有没有办法在一个更新声明中做到这一点？还是首先需要查询时间戳值并将其保存为变量，然后使用变量更新XML？

Answer 1

您还可以通过内联视图的联接来完成这一任务，并使用SQL替换函数：

   CREATE TABLE TestTable
   (    
       Id INT IDENTITY(1,1) NOT NULL,
       Data XML NOT NULL
   )

   INSERT TestTable (Data) VALUES ('<root>
        <id>1</id>
        <timestamp>16-10-2017 19:24:55</timestamp>
    </root>')

    UPDATE TestTable
    SET Data.modify('replace value of 
    (/root/timestamp/text())[1] with sql:column("T2.NewData")')
    FROM TestTable T1
    INNER JOIN (
        SELECT Id
        , REPLACE( Data.value('(/root/timestamp/text())[1]', 'nvarchar(max)'), '-', '/') AS NewData
        FROM TestTable
    ) T2
    ON T1.Id = T2.Id

    SELECT * FROM TestTable

Answer 2

如果没有外部需要，您必须完全填充，则应该在ISO8601中使用ISO8601日期/时间字符串。

您的dateTime-string与区域性相关。在具有不同语言或日期格式设置的不同系统上阅读此操作将导致错误或--甚至更糟！！--导致错误结果。

像"08-10-2017“这样的日子可以是10月8日或8月10日.

最糟糕的一点是，这可能成功地通过了所有的测试，但是会在客户的机器上出现奇怪的错误消息或糟糕的结果，最终导致真正的数据破坏！

将连字符转换为斜线只是化妆品而已！XML是严格定义的数据容器。任何非字符串数据都必须表示为安全的可转换字符串。

这就是你应该做的：

DECLARE @tbl TABLE(ID INT IDENTITY,YourXML XML);
INSERT INTO @tbl VALUES
(N'<root>
    <id>1</id>
    <timestamp>16-10-2017 19:24:55</timestamp>
</root>');

UPDATE @tbl SET YourXml.modify(N'replace value of (/root/timestamp/text())[1] 
                                 with concat(  substring((/root/timestamp/text())[1],7,4), "-"
                                              ,substring((/root/timestamp/text())[1],4,2), "-"
                                              ,substring((/root/timestamp/text())[1],1,2), "T"
                                              ,substring((/root/timestamp/text())[1],12,8)
                                            ) cast as xs:dateTime?');

SELECT * FROM @tbl;

结果

<root>
  <id>1</id>
  <timestamp>2017-10-16T19:24:55</timestamp>
</root>