SQL Server XQuery: deleting nodes using .modify()
Quick blog post; got asked today by a developer friend of mine about how to delete nodes in an XML fragment using the .modify() method. After some head-scratching and some fumbling around (its been a few months since IвЂ™ve done any work with XML), we came up with a version of the following script:
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 --delete the classes that belong to teacher Smith SET @X.modify('delete /root/class/.[@teacher="Smith"]') SELECT @X
Now, let me try to explain it:
- Given a simple document that has a root with classes, and students in each class, we want to delete all classes that are being taught by a teacher named вЂњSmithвЂќ.
- First, we delete the nodes under those classes that belong to Smith
- Using XPath, we walk the axis and use a node test to restrict to /root/class/. (the current node under class).
- We then apply a predicate looking for a teacher attribute with a value of вЂњSmithвЂќ
- The .modify() clause applies the delete command to the @X variable, and updates the XML