XML support in Microsoft SQL Server 2005 (2)

XML Data modification

SQL Server 2005 provides a construct for data modification as an extension of XQuery. The subtree can be inserted either before or after the specified node, or as the leftmost or rightmost child node. In addition, a subtree can be inserted into the parent node, in which case it becomes the rightmost child node of the parent node. attributes, elements, and text node inserts are supported.

Delete Subtree is supported. In this case, the entire subtree is removed from the XML instance.

A scalar value can be replaced with a new scalar value.

Example: Inserting a subtree into an XML instance

This example shows the use of the Modify () method, which inserts a new <section> element to the right of the element numbered 1.

UPDATE docs SET xCol.modify('
 insert
  <section num="2">
     </section>        
 after (/doc/section[@num=1])[1]')

Example: Update the price of this book to $49.99

The following UPDATE statement replaces <price> of book ISBN 1-8610-0311-0 with $49.99. The XML instance is typed through the XML Schema Http://myBooksinstance and thus has a namespace declaration in the XML data modification statement.

UPDATE XmlCatalog
SET  Document.modify ('
  default namespace = "http://myBooks"
  replace value of (/bookstore/book[@ISBN=
"1-8610-0311-0"]/price)[1] with 49.99')

Type checking and static errors

XQuery introduces type checking. The compile phase examines the static type correctness of XQuery expressions and data modification statements, and uses XML schemas for type inference (in the case of typed XML). If an expression fails because of a type security violation at run time, a static type error is generated. Examples of static errors are: Adding a string to an integer, receiving a sequence of values where the operation requires a single value, and querying for nonexistent nodes to find typed data. Explicit conversion to the correct type is a workaround for static errors that result from a type mismatch. The XQuery run-time error is converted to an empty sequence.