sql-server - TSQL Xpath修改XML字符串

Question

我正在尝试修改XML字符串以将节点的值替换为其他值。最终目标是对一列中的帐号进行大规模加密，但是目前，我只是尝试替换XML字符串中的一个。

当我执行此操作时，即使我试图返回数据，也会得到“命令已成功完成”。

DECLARE @xml XML = '<optional><account>155555555</account></optional>'


SET @xml.modify('replace value of (/optional/account/text())[1] with "' + (
              SELECT 'ABC-' + ParamValues.item.value('.', 'varchar(max)')
              FROM @xml.nodes('/optional/account') AS ParamValues(item)
       )+ '"')

SELECT @xml

关于为什么这不是用 ABC-Account Number Here代替XML字符串中的帐号并返回它的想法？

Answer 1

要回答您的特定问题，“为什么不替换帐号”，这是因为XML函数参数必须是字符串文字。您正在为modify XML函数构造参数，这会导致错误情况。

通常，当您在XML函数参数中不使用字符串文字时，SQL Server会引发错误，但是在这种情况下，由于nodes()函数调用，它必须引起混淆。

正如您在注释中指出的那样，这是一个简单的示例，其中包含字符串文字，它可以工作：

DECLARE @xml XML = '<optional><account>155555555</account></optional>'
SET @xml.modify('replace value of (/optional/account/text())[1] with "1"');
SELECT @xml;

但是，如果尝试构造 modify XML函数参数，如下面的示例所示，它将失败：

DECLARE @xml XML = '<optional><account>155555555</account></optional>'
SET @xml.modify('replace value of (/optional/account/text())[1] with "' + '1' + '"');
SELECT @xml;

错误是：


消息8172，级别16，状态1，第2行XML数据的参数1
类型方法“修改”必须是字符串文字。


显然，如果在其中添加 nodes()会使您有点狂热，则会混淆SQL Server并抑制错误情况：

DECLARE @xml XML = '<optional><account>155555555</account></optional>'
SET @xml.modify('replace value of (/optional/account/text())[1] with "' + (SELECT '1' FROM @xml.nodes('/optional/')) + '"');
SELECT @xml;

那不是错误，而是在显示任何数据并简单打印之前终止：


命令已成功完成。


因此，您实际上无法构造XML函数参数。但是，您仍然可以使用外部信息。 Kiran Hedge showed how使用 sql:variable() XQuery extension function。

但是，您实际上不必花那么长时间。您正在使用XML内部的数据，XML处理器可以对XML进行本地访问。因此，您可以执行以下操作：

DECLARE @newXmlSingleReplacement XML = '<optional><account>155555555</account></optional>';
SET @newXmlSingleReplacement.modify('replace value of ((/optional/account)/text())[1] with fn:concat("ABC-",((/optional/account)/text())[1])');
SELECT @newXmlSingleReplacement;

因此，对于您的简化示例，Kiran的解决方案或此解决方案都可以正常工作。但是您可能有一个更复杂的XML文档。它可能有多个要更改的“行”。

如果您从上方尝试使用具有多个帐号的XML文档使用相同的代码，则仅替换第一个数字：

DECLARE @newXmlSingleReplacement XML ='<optional><account>155555555</account></optional><optional><account>255555555</account></optional>';
    SET @newXmlSingleReplacement.modify('replace value of ((/optional/account)/text())[1] with fn:concat("ABC-",((/optional/account)/text())[1])');
    SELECT @newXmlSingleReplacement;

结果：

<optional>
  <account>ABC-155555555</account>
</optional>
<optional>
  <account>255555555</account>
</optional>

您可能会认为只需删除索引（ 1）并影响所有行。不幸的是，根据 Microsoft's documentation， replace value of的第一个参数“必须仅标识单个节点”。因此，您无法列出所有帐号并对其进行操作。

这个例子：

DECLARE @newXmlSingleReplacement XML ='<optional><account>155555555</account></optional><optional><account>255555555</account></optional>';
SET @newXmlSingleReplacement.modify('replace value of ((/optional/account)/text()) with fn:concat("ABC-",((/optional/account)/text()))');
SELECT @newXmlSingleReplacement;

导致此错误：


消息2389，级别16，状态1，第2行XQuery [modify（）]：'concat（）'
需要一个单例（或空序列），找到类型的操作数
'xdt：untypedAtomic *'


因此，您可以遍历所有“行”，并每次执行 modify()操作。您将需要一种通过计数器跟踪进度的方法。这是一个例子，使用稍微复杂一些的XML来证明这个概念。

DECLARE @xml XML = '<optional><other>Test123</other><account>155555555</account></optional><optional><other>Test321</other><account>255555555</account></optional>';
DECLARE @newxml XML = @xml, @AccountCount int = 0, @Counter int = 0;
SET @AccountCount = @newxml.value('fn:count(//account)','int');
WHILE @Counter <= @AccountCount
BEGIN
    SET @Counter = @Counter + 1;
    SET @newxml.modify('replace value of ((/optional/account)[position()=sql:variable("@Counter")]/text())[1] with fn:concat("ABC-",((/optional/account)[position()=sql:variable("@Counter")]/text())[1])');
END
SELECT  @newxml; 

结果：

<optional>
  <other>Test123</other>
  <account>ABC-155555555</account>
</optional>
<optional>
  <other>Test321</other>
  <account>ABC-255555555</account>
</optional>

当然，如果可以的话，我们宁愿避免SQL代码中的循环。对集合进行操作的单个语句通常会产生更好的性能。

一种选择是在调整过程中的值的同时切碎XML并对其进行重整。这种方法的缺点是您必须知道XML的详细信息才能进行重构。根据XML文档的复杂性，它可能也是一个昂贵且复杂的语句。这是一个例子：

DECLARE @xml XML = '<optional><other>Test123</other><account>155555555</account></optional><optional><other>Test321</other><account>255555555</account></optional>';
SELECT
    v.value('(./other)[1]','varchar(500)') AS other,
    'ABC-' + v.value('(./account)[1]','varchar(500)') AS account
FROM        @xml.nodes('/optional') AS T(v)
FOR XML PATH ('optional'), TYPE;

但这不是改革XML的唯一方法。您可以使用XML系统本身及其 FLWOR statement support对其进行重构。例如：

DECLARE @xml XML = '<optional><other>Test123</other><account>155555555</account></optional><optional><other>Test321</other><account>255555555</account></optional>';
SELECT @xml.query ('
    for $optional in //optional
    return
        <optional>
            {$optional/other}
            <account>ABC-{$optional/account/text()}</account>
        </optional>

');

但是同样，这需要了解并手动重新创建XML的结构。有避免这种情况的方法。下一个示例需要对现有XML的最少了解。它实质上在帐户节点级别循环遍历节点，并且仅当它们被命名为“帐户”时才替换它们。

DECLARE @xml XML = '<optional><other>Test123</other><account>155555555</account></optional><optional><other>Test321</other><account>255555555</account></optional>';
SELECT @xml.query ('
    for $optional in //optional
    return
        <optional>
            {
                for $subnode in $optional/*
                    return
                        if (fn:local-name($subnode) = "account")
                        then    
                            <account>ABC-{$subnode/text()}</account>
                        else
                            $subnode
            }
        </optional>
');

基于对这些非常小的示例XML文档使用 SET STATISTICS TIME ON;进行的粗略测试，看来 nodes()切碎和重构速度稍快。它还具有最简单，成本最低的查询计划。