CODEWITHSUNDEEP
t-sqlvariablesxquery
Eon
Eon

Reputation: 3974

Delete node where attribute = variable

I am in need of some help - how do I delete a node from an untyped XML Field where the attribute is equal to a sql Variable?

This is what I have tried:

DECLARE @AttributeKey varchar(500) = 'TestColorBox1';

UPDATE
    Numbers
SET
    AttributeKeyValues.modify('delete (/attributes/attribute[@key="{sql:variable("@AttributeKey")"]}/*')
WHERE
    AccountId = 2000046

    SELECT * FROM Numbers

ps. Sorry if the question is vague, I literally started with xquery yesterday. I already read up that you define a sql Variable as 

"sql:variable("@variableName")"

Error:

  Msg 9303, Level 16, State 1, Line 6
    XQuery [Numbers.AttributeKeyValues.modify()]: Syntax error near '@', expected ']'.

Update:

Got it to run without errors: still not deleting the node i need to delete

DECLARE @AttributeKey varchar(500) = 'TestColorBox1';

UPDATE
    Numbers
SET
    AttributeKeyValues.modify('delete (/attributes/attribute[@key="{sql:variable("@AttributeKey")}"])')
WHERE
    AccountId = 2000046

    SELECT * FROM Numbers

Another Update: XML Used

<attributes>
  <attribute key="TestColorBox1">TEST1</attribute> <!-- Targeted Field -->
  <attribute key="test2345">TEST2</attribute>
  <attribute key="test23454">TEST3</attribute>
  <attribute key="test24568798">TEST4</attribute>
  <attribute key="TEST123214124124">TEST5</attribute>
</attributes>

Upvotes: 1

Views: 1367

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

Delete the attribute node in attributes where the attribute @key is equal to the value of local variable @AttributeKey

modify('delete /attributes/attribute[@key = sql:variable("@AttributeKey")]')

Upvotes: 1

Related Questions