TSQL 替换 XML 字符串中的值 [英] TSQL Replace value in XML String

问题描述

我的表中有一个字段，其中包含可选的 XML 数据字段.

I have a field within my table that contains optional fields of data contained as XML.

其中一个字段在我的 UI 中是可编辑的，我正在尝试在 XML 块中更新该节点/值.

One of those fields is editable in my UI and I am trying to update that node / value within the block of XML.

这是我所拥有的:

UPDATE dbo.TFS_Feedback_New 
SET Details.modify('replace value of (/optional/educational/text())[1] with "@updatedEducation"') 
WHERE feedbackID = @FBID

问题是，我需要将替换作为变量传递.当我拥有它时，它会将 @updatedEducation 作为字段值；不是变量的实际值.

The issue is, I need to pass the replacement as a variable. When I have it as is, it puts in @updatedEducation as the field value; not the actual value of the variable.

有没有办法做到这一点?

Any way to do this?

推荐答案

你已经差不多了，只需要将它包裹在 sql:variable() 中:

You were almost there, only needed to wrap it inside sql:variable():

UPDATE dbo.TFS_Feedback_New
    SET Details.modify('
        replace value of (/optional/educational/text())[1]
        with sql:variable("@updatedEducation")')
    WHERE feedbackID = @FBID

MSDN

这篇关于TSQL 替换 XML 字符串中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！