SQL Server: how to delete xml node by sql variable?

Question

My first question on Stack Overflow :)

I have XML:

DECLARE @xml XML = '<root><tag1 /><tag2 /></root>';

I need to remove node, but, path to node is variable "@path".

DECLARE @path XML = '/root/tag2';

My query is:

SET @xml.[modify]('delete sql:variable("@path")');

But, I get error: Msg 9342, Level 16, State 1, Line 9 XQuery [modify()]: An XML instance is only supported as the direct source of an insert using sql:column/sql:variable.

So my question is: how can I delete xml node by sql parameter?

You cannot use a variable for the XPath, but you can use various predicates. Please provide more information about your real-world-needs... Just to mention: There is a way via dynamically created SQL and EXEC()... — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 23, 2017 at 10:09
Its complex to describe, just created simplified example. I have more requirements here: one is to keep same session. So, dynamic sql is last option to use. Thanks for your answer. — Juozas
– Juozas, Commented Feb 23, 2017 at 10:16

Gottfried Lesigang · Accepted Answer · 2017-02-23 10:26:38Z

6

There is no general recipie...

Just some ideas:

If you know the node's name

DECLARE @xml XML = '<root><tag1 /><tag2 /></root>';
DECLARE @nodeToDelete VARCHAR(100)='tag2';
SET @xml.modify('delete (/root/*[local-name()=sql:variable("@nodeToDelete")])[1]');
SELECT @xml;

If you know the node's name with FLWOR-query

DECLARE @xml XML = '<root><tag1 /><tag2 /></root>';
DECLARE @nodeToDelete VARCHAR(100)='tag2';
SET @[email protected]('<root>
                     {
                     for $nd in /root/*[local-name()!=sql:variable("@nodeToDelete")]
                       return $nd
                     }
                     </root>');
SELECT @xml;

dynamically created

DECLARE @xpath VARCHAR(100)='/root/tag2';

DECLARE @command VARCHAR(MAX)=
'DECLARE @xml XML = ''<root><tag1 /><tag2 /></root>'';
 SELECT @xml;
 SET @xml.modify(''delete ' +  @xpath + ''');
 SELECT @xml';

PRINT @command;
EXEC(@command);

answered Feb 23, 2017 at 10:26

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Juozas Over a year ago

At last, I used dynamic way. Thanks.

Collectives™ on Stack Overflow

SQL Server: how to delete xml node by sql variable?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related