CODEWITHSUNDEEP
sql-serverxmlt-sql
Juozas
Juozas

Reputation: 935

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

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?

Upvotes: 1

Views: 5031

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67281

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);

Upvotes: 6

Related Questions