MSSQL 2012: delete XML attribute by using a variable

Question

Good afternoon,

I would like to delete XML attributes by using a TSQL variable. This is a mimimum working example (TSQL-code):

DECLARE @xml XML;
SET @xml = CONVERT(XML, N'
<recipe>
    <parameters>
        <parameter name="a" desc="A">ValueA</parameter>
        <parameter name="b" desc="B">ValueB</parameter>
        <parameter name="c" desc="C">ValueC</parameter>
    </parameters>
</recipe>
');
SET @xml.[modify]('delete (/recipe/parameters/parameter/@desc)');
SELECT  @xml;

This gives the desired output: all attributes "desc" have been removed:

<recipe>
  <parameters>
    <parameter name="a">ValueA</parameter>
    <parameter name="b">ValueB</parameter>
    <parameter name="c">ValueC</parameter>
  </parameters>
</recipe>

However, I would like to define a TSQL-variable

DECLARE @attr NVARCHAR(MAX) = N'desc';

How can I pass this to the XQuery modify operation? I can use sql:variable("@attr") inside the XML DML, but how can I use it to address an attribute?

Thanks for any help!

Answer 1

You can use a wildcard when specifying the attribute and then check for the name of the attribute in a predicate.

SET @xml.[modify](
  'delete(/recipe/parameters/parameter/@*[local-name() = sql:variable("@attr")])');