Microsoft SQL Server 2008 R2 Unleashed- P193:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1914 CHAPTER 47 Using XML in SQL Server 2008 Removing XML Nodes by Using delete delete uses its XPath parameter to locate the node to remove. In the example in Listing any alph node that has a name attribute with a value of B is deleted. Then the remaining values for alph @name are selected using nodes to illustrate the success of the deletion. LISTING Deleting Nodes Using delete DECLARE @XmlVar xml SET @XmlVar 1 alphnumerics item alph name A val 65 item item alph name B val 66 item item alph name C val 67 item item num name 1 val 49 item item num name 2 val 50 item item num name 3 val 51 item alphnumerics SET @ delete item alph @name B SELECT . @name char l as RemainingAlphNames FROM @ item alph as XmlTable XmlCol go AlphNames A C 2 row s affected Modifying XML with insert and replace value of You can insert and update new nodes in document trees by using insert. In these situations node position counts most. Let s look at a real-world example for the scenarios in this section say that a content author is building a structured document. Each node has both its respective level or depth and its order of appearance. Your DML operations must respect both. The markup Using the xml Data Type 1915 and table storage for such a scenario might look something like the untyped XML in Listing . LISTING Simple Untyped XML Markup for a Book CREATE TABLE SimpleBook BookId int IDENTITY 1 1 PRIMARY KEY CLUSTERED BookXml xml GO INSERT SimpleBook SELECT book book_id 1 title A Great Work title chapter chapter_id 1 title An Excellent Chapter title section id 1 title A Boring Section title paragraph para_id 1 Something boring. paragraph section section id 2 title Another Fine Section title paragraph para_id 2 Another fine paragraph. paragraph section chapter book 47 In this listing notice that the XML element content in the first section seems out of place considering the laudatory content of the chapter and book .
