Supply xml element value in modify() method

Question

I know how to replace element value for the xml element in the modify() method. Here's the example

TSQL Replace value in XML String

My problem is a bit different. Taking example from above link...

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

What I want to do is provide value for 'educational'. In other words I want to do something like this

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

I'm getting the following error because of sql:variable("@name")

The XQuery syntax '/function()' is not supported.

How can I pass both the name of the element to be updated and its value to my stored procedure and have it update the XML column?

Answer 1

You are not allowed to use variables as part of the XPath, but you can use a predicate:

DECLARE @xml XML=
N'<root>
    <optional>
        <educational>SomeText</educational>
        <someOther>blah</someOther>
    </optional>
  </root>';

--The straight approach as you know it:

SET @xml.modify('replace value of (/root/optional/educational/text())[1] with "yeah!"');

SELECT @xml;

--Now we use a variable to find the first node below <optional>, which name is as given:

DECLARE @ElementName VARCHAR(100)='educational';

SET @xml.modify('replace value of (/root/optional/*[local-name()=sql:variable("@ElementName")]/text())[1] with "yeah again!"');

SELECT @xml;

Try it out...