Updating db via xml
A database schema can be written in XML pretty trivially.
element SELECT x.query(' /Root/Product Description') FROM T -- update the Product Name attribute value UPDATE T SET x.modify(' replace value of (/Root/Product Description/@Product Name) with "New Road Bike" ') -- verify the update SELECT x.query(' /Root/Product Description') FROM T This example replaces values in a manufacturing instructions document stored in a typed XML column.
In the example, you first create a table (T) with a typed XML column in the Adventure Works database.
If the value is a list of values, the update statement replaces the old value with the list.
In modifying a typed XML instance, must be an expression that can be atomized. The following examples of the replace value of XML DML statement illustrates how to update nodes in an XML document.
'; SELECT @my Doc; -- update text in the first manufacturing step SET @my Doc.modify(' replace value of (/Root/Location/step/text()) with "new text describing the manu step" '); SELECT @my Doc; -- update attribute value SET @my Doc.modify(' replace value of (/Root/Location/@Labor Hours) with "100.0" '); SELECT @my Doc; Note that the target being updated must be, at most, one node that is explicitly specified in the path expression by adding a "" at the end of the expression.