Excluding nodes from XML data before returning from SQL Server

This post follows on from a question I recently replied to, for how to exclude a specific node from an XML column value before returning it, using TSQL.

Example setup

CREATE TABLE Example  
(  
ID INTEGER IDENTITY(1,1) PRIMARY KEY,  
XmlField XML  
)  
  
INSERT Example (XmlField) VALUES ('<Root><ChildA>Value I want to see</ChildA><ChildB>Value I do not want to see</ChildB></Root>')  

So if you want to return the XML minus the ChildB node, how do you do it?

Modify it

Literally, using the modify method that is supported on the XML data type. To quote MSDN, modify:

Modifies the contents of an XML document. Use this method to modify the content of an xml type variable or column. This method takes an XML DML statement to insert, update, or delete nodes from XML data. The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.

That last statement, to me at least, seems slightly open for misinterpretation as strictly speaking it isn’t only valid in an UPDATE statement - you can use it as part of a standalone SET statement on an XML variable (though yes, to UPDATE that variable).

So, while you can’t do something like this:

SELECT XmlField.modify('delete /Root/ChildB')  
FROM Example  
WHERE ID = 1  

as it results in the following error:

Msg 8137, Level 16, State 1, Line 1  
Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.  

you can instead do this:

DECLARE @xml XML  
SELECT @xml = XmlField  
FROM Example  
WHERE ID = 1  
  
-- Delete the node from the XML in the variable  
SET @xml.modify('delete /Root/ChildB')  
  
SELECT @xml  

That does the trick.

Dealing with multiple rows

If you wanted to do something like this from the database side when returning multiple rows then you’d need to either:

1. Create a scalar UDF to perform the removal.

e.g.

    CREATE FUNCTION dbo.RemoveNode(@xml XML)  
     RETURNS XML  
    AS  
    BEGIN  
     SET @xml.modify('delete /Root/ChildB')  
     RETURN @xml  
    END  
    GO  
      
    SELECT dbo.RemoveNode(XmlField) AS XmlField  
    FROM Example

Note the parameter to the modify method must be a literal string, so you can’t parameterise the UDF to make it a bit more generic by using that parameter in the call to modify().

2. SELECT the xml values into a temp table/table variable, do an UPDATE like this:

```sql
UPDATE #Temp  
SET XmlField.modify('delete /Root/ChildB')  
```
and then SELECT from the temp table/table variable.  

3. Use SQLCLR

Of course, the typical question should be asked - do you need to do this on the database side, or can you return the XML as-is from SQL Server and modify it accordingly in your calling code? In the original case acting on a single XML value, it’s OK but when you start looking into scalar UDF’s etc for multi-row requirements then you should be careful to check performance.


See also