sql-server - 使用 XQuery 仅查找并替换 xml 值的一部分？

Question

我的一个专栏中有一个 XML，看起来像这样:

<BenutzerEinstellungen>
       <State>Original</State>
       <VorlagenHistorie>/path/path3/test123/file.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path21/anothertest/second.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path15/test123/file.doc</VorlagenHistorie>
</BenutzerEinstellungen>

我想将 VorlagenHistorie 中的 all test123 出现(可以有多个)替换为 另一个测试，我更新后所有路径都指向 test123。

我知道，如何使用相等运算符检查和替换所有值，我在这个答案中看到了它: Dynamically replacing the value of a node in XML DML

但是是否有 CONTAINS 运算符，是否可以替换值的 INSIDE，我的意思是只替换值的一部分？

提前致谢!

Answer 1

我通常不会建议基于字符串的方法。但在这种情况下，做这样的事情可能是最简单的

declare @xml XML=
'<BenutzerEinstellungen>
       <State>Original</State>
       <VorlagenHistorie>/path/path/test123/file.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path/anothertest/second.doc</VorlagenHistorie>
</BenutzerEinstellungen>';

SELECT CAST(REPLACE(CAST(@xml AS nvarchar(MAX)),'/test123/','/anothertest/') AS xml);

更新

如果此方法是全局，您可以尝试这样的方法:

我将 XML 作为派生表读取并将其作为 XML 写回。在这种情况下，您可以确定，只有具有 VorlageHistorie 的节点才会被触及...

SELECT @xml.value('(/BenutzerEinstellungen/State)[1]','nvarchar(max)') AS [State]
      ,(
        SELECT REPLACE(vh.value('.','nvarchar(max)'),'/test123/','/anothertest/') AS [*]
        FROM @xml.nodes('/BenutzerEinstellungen/VorlagenHistorie') AS A(vh)
        FOR XML PATH('VorlagenHistorie'),TYPE
       )
FOR XML PATH('BenutzerEinstellungen');

更新 2

试试这个。它将读取所有未被称为 VorlagenHistorie 原样 的节点，然后添加具有替换值的 VorlageHistorie 节点。唯一的缺点可能是，如果在 VorlagenHistorie 元素之后还有其他节点，则文件的顺序会有所不同。但这不应该真正触及您的 XML 的有效性......

declare @xml XML=
'<BenutzerEinstellungen>
       <State>Original</State>
       <Unknown>Original</Unknown>
       <UnknownComplex>
       <A>Test</A>
       </UnknownComplex>
       <VorlagenHistorie>/path/path/test123/file.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path/anothertest/second.doc</VorlagenHistorie>
</BenutzerEinstellungen>';

SELECT @xml.query('/BenutzerEinstellungen/*[local-name(.)!="VorlagenHistorie"]') AS [node()]
      ,(
        SELECT REPLACE(vh.value('.','nvarchar(max)'),'/test123/','/anothertest/') AS [*]
        FROM @xml.nodes('/BenutzerEinstellungen/VorlagenHistorie') AS A(vh)
        FOR XML PATH('VorlagenHistorie'),TYPE
       )
FOR XML PATH('BenutzerEinstellungen');

更新 3

使用可更新的 CTE 首先获取值，然后一次性设置它们:

declare @tbl TABLE(ID INT IDENTITY,xmlColumn XML);
INSERT INTO @tbl VALUES
(
'<BenutzerEinstellungen>
       <State>Original</State>
       <Unknown>Original</Unknown>
       <UnknownComplex>
       <A>Test</A>
       </UnknownComplex>
       <VorlagenHistorie>/path/path/test123/file.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path/anothertest/second.doc</VorlagenHistorie>
</BenutzerEinstellungen>')
,('<BenutzerEinstellungen>
       <State>Original</State>
       <VorlagenHistorie>/path/path/test123/file.doc</VorlagenHistorie>
       <VorlagenHistorie>/path/path/anothertest/second.doc</VorlagenHistorie>
    </BenutzerEinstellungen>');

WITH NewData AS
(
    SELECT ID
      ,xmlColumn AS OldData
      ,(
        SELECT t.xmlColumn.query('/BenutzerEinstellungen/*[local-name(.)!="VorlagenHistorie"]') AS [node()]
              ,(
                SELECT REPLACE(vh.value('.','nvarchar(max)'),'/test123/','/anothertest/') AS [*]
                FROM t.xmlColumn.nodes('/BenutzerEinstellungen/VorlagenHistorie') AS A(vh)
                FOR XML PATH('VorlagenHistorie'),TYPE
               )
        FOR XML PATH('BenutzerEinstellungen'),TYPE
       ) AS NewXML
    FROM @tbl AS t
)
UPDATE NewData
SET OldData=NewXml;

SELECT * FROM @tbl;