TSQL Replace value in XML String

问题:

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

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

Here is what I have:

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

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?

回答1:

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

Documentation on MSDN