SQL Server includes an extension method to XQuery, the modify() method. The modify() method allows you to modify parts of your XML data. You can add or delete subtrees, replace values, or perform similar XML modifications. The modify() method includes Data Manipulation Language (DML) commands such as insert, delete, and replace value of.
SQL Server supports piecemeal XML modification. This means that when you modify your XML document, such as adding elements, changing attributes, or deleting elements, SQL Server performs just the necessary operations on the XML rather than replacing the entire XML document.
Inserting an Element
With the insert command, you can insert XML as the first or last element. You can specify whether to insert the XML before, after, or as a direct descendant of an existing XML element. You can also insert attributes using this method. Without needing to modify the entire document, you can use insert to easily put XML into existing documents. The insert command also allows the user to use a scalar T-SQL variable of type xml within the insert expression. For example, consider the following shopping list:<List>
Suppose we want to insert a value stored in a T-SQL variable into this list as another item. This can be accomplished using the following code:DECLARE @GroceryList xml
The result of the @GroceryList variable is as follows:<List>
Deleting an Element
With the delete command, you can delete XML elements or attributes from your XML document. For example, you might remember that you don’t need dog food after all.SET @GroceryList.modify ('delete /List/Item[Dog Food]')
This invocation of the delete command within the modify method deletes the dog food item that was added to your grocery list in the preceding section.
Changing a Node Value
The replace value of command allows you to replace a node with a new value that you specify. You can replace only a single node at a time; you cannot select multiple nodes.
The following example inserts and changes the favoritecolor element for person number 3 in the XML document:--First insert a new value
Limitations of XML Modification
You have some limitations when you modify your XML, including the following:
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.