因此，我正在处理一个包含 XML 数据的表中的字段，并且 XML 字段中的参数数量逐行变化(变量名称也会变化)。

我需要能够为我的 <variablename>tminus1</variablename> 搜索包含 XML 的字段 (我没有设计 XML 结构，我只是必须解决它的人)并替换 <ValueAsString>data here</ValueAsString> 使用由监视表更改的触发器动态生成的新数据。

由于 XML 的设置方式，我花了几天时间试图解决这个问题，但我不知所措。谁能帮忙？触发器部分很简单，它可以找到正确的 XML 位置来替换我遇到的困难。

    <Parameters>
<Parameter><VariableID>(1012,14505)</VariableID><VariableName>ArtworkFormat</VariableName><ListID>(1042,1601)</ListID><ListValue>0</ListValue><ValueAsString /></Parameter>
<Parameter><VariableID>(2226,14505)</VariableID><VariableName>ArtworkProofType</VariableName><ListID>(1045,1601)</ListID><ListValue>0</ListValue><ValueAsString /></Parameter>
<Parameter><VariableID>(2224,14505)</VariableID><VariableName>ArtworkReceivedVia</VariableName><ListID>(1043,1601)</ListID><ListValue>0</ListValue><ValueAsString /></Parameter>
<Parameter><VariableID>(2225,14505)</VariableID><VariableName>ArtworkReturnVia</VariableName><ListID>(1044,1601)</ListID><ListValue>0</ListValue><ValueAsString /></Parameter>
<Parameter><VariableID>(10306,14505)</VariableID><VariableName>tminus1</VariableName><ValueAsString>10/12/2016 4:00 PM</ValueAsString></Parameter>
<Parameter><VariableID>(10308,14505)</VariableID><VariableName>tminus3</VariableName><ValueAsString>10/10/2016 4:00 PM</ValueAsString></Parameter>
<Parameter><VariableID>(10307,14505)</VariableID><VariableName>tminus2</VariableName><ValueAsString>10/11/2016 4:00 PM</ValueAsString></Parameter>
</Parameters>

最佳答案

一步或两步替换

正如其他人指出的那样，如果您想替换给定为 <ValueAsString /> 的节点的文本值，就会出现问题。 . .modify(N'replace value of...要求 /text()最后作为替换的目标，但没有...简单的方法适用于给定的情况，但如果在 <Parameter> 中则不起作用您要更改的目标节点是空的，因为它在 VariableName="ArtworkReturnVia" 中:

DECLARE @SearchFor NVARCHAR(100)='tminus1';
DECLARE @SetValue NVARCHAR(100)='NewData';

UPDATE @tbl SET XmlColumn.modify(N'replace value of (/Parameters/Parameter[VariableName=sql:variable("@SearchFor")]/ValueAsString)[1]/text()[1]
                                   with sql:variable("@SetValue")')

一种直接的方法是在任何情况下删除这个元素并插入:

UPDATE @tbl SET XmlColumn.modify(N'delete (/Parameters/Parameter[VariableName=sql:variable("@SearchFor")]/ValueAsString)[1]')
UPDATE @tbl SET XmlColumn.modify(N'insert <ValueAsString>{sql:variable("@SetValue")}</ValueAsString> into (/Parameters/Parameter[VariableName=sql:variable("@SearchFor")])[1]')

另一种方法:Full FLWOR

UPDATE @tbl SET XmlColumn=XmlColumn.query
('<Parameters>
  {
    for $p in /Parameters/Parameter
    return <Parameter>
    {
    if($p/*/text()=sql:variable("@SearchFor")) then
            for $nd in $p/*
            return
            if(local-name($nd)="ValueAsString") then
                    <ValueAsString>{sql:variable("@SetValue")}</ValueAsString>
            else
                    $nd
    else $p
    }
    </Parameter>
  }
</Parameters>
');

另一个:Semi FLWOR

WITH Prms AS
(
    SELECT ID --needs an ID here
          ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NodeOrder
          ,p.query('.') AS prm
    FROM @tbl 
    CROSS APPLY XmlColumn.nodes('/Parameters/Parameter') AS A(p)
)
UPDATE tbl SET XmlColumn=
(
SELECT CASE WHEN prm.value('(/Parameter/VariableName)[1]','nvarchar(max)')=@SearchFor 
            THEN prm.query('<Parameter>
                            {
                            for $nd in /Parameter/*
                            return
                                if(local-name($nd)="ValueAsString") then
                                    <ValueAsString>{sql:variable("@SetValue")}</ValueAsString>
                                else
                                    $nd
                            }
                            </Parameter>
                           ')
            ELSE prm END
FROM Prms
WHERE Prms.ID=tbl.ID
ORDER BY Prms.NodeOrder
FOR XML PATH(''),ROOT('Parameters')
)
FROM @tbl AS tbl

关于SQL XML 查找和替换，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/40032640/