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!
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")])');