So after my last post, my developer friend came back to me and noted that I hadn’t really demonstrated the situation we had discussed; our work was a little more challenging than the sample script I had provided. In contrast to what I previously posted, the challenge was to delete nodes where a sub-node contained an attribute of interest. Let me repost the same sample code as an illustration:
DECLARE @X XML = '<root> <class teacher="Smith" grade="5"> <student name="Ainsworth" /> <student name="Miller" /> </class> <class teacher="Jones" grade="5"> <student name="Davis" /> <student name="Mark" /> </class> <class teacher="Smith" grade="4"> <student name="Baker" /> <student name="Smith" /> </class> </root>' SELECT @x
If I wanted to delete the class nodes which contain a student node with a name of “Miller”, there are a couple of ways to do it; the first method involves two passes:
SET @X.modify('delete /root/class//.[@name = "Miller"]/../*') SET @X.modify('delete /root/class[not (node())]') SELECT @x
In this case, we walk the axis and find a node test of class (/root/class); we then apply a predicate to look for an attribute of name with a value of Miller ([@name=”Miller”]) in any node below the node of class (//.). We then walk back up a node (/..), and delete all subnodes (/*).
That leaves us with an XML document that has three nodes for class, one of which is empty (the first one). We then have to do a second pass through the XML document to delete any class node that does not have nodes below it (/root/class[not (node())]).
The second method accomplishes the same thing in a single pass:
SET @x.modify('delete /root/class[student/@name="Miller"]') SELECT @x
In this case, walk the axis to class (/root/class), and then apply a predicate that looks for a node of student with an attribute of name with a value of Miller ([student/@name=”Miller”); the difference in this syntax is that the pointer for the context of the delete statement is left at the specific class as opposed to stepping down a node, and then back up.
