2

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!

1 Answer 1

4

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")])');
Sign up to request clarification or add additional context in comments.

1 Comment

Excellent! Thanks a lot!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.