How to Modify XML Data using Functions in SQL Server

Similar to any other type of data, programmer might also need to modify the XML data. To modify data, you can use the modify function provided by the XML data type of the SQL Server. The modify function specifies an XQuery expression and a statement that specifies the kind of modification that needs to be done.

This function allows you to perform the following modifications:

  • Insert: Used to add nodes to XML in an XML column or variable. For example, the management of AdventureWorks wants to add another column specifying the type of customer, in the CustDetails table. The default value in the Type column should be ‘Credit’. To resolve this problem, the database developer of AdventureWorks will create the following query:

    UPDATE CusomtDetails SET Cust_Details.modify (‘ inser attribute Type{“Credit”} as first into (/Customer) [1]’)
  • Replace: Used to update the XML data. For example, James Stephen, one of the customers of AdventureWorks, has decided to change his customer type from Credit to Cash. As a database developer, you can create the following query to reflect this change:
  • Delete: Used to remove a node from the XML data. For example, the management of AdventureWorks has decided to remove the ‘City’ column from the customer details. You can write the following query to display the results:

    UPDATE CustomDetails SET Cust_Details.modify (‘delete (/Customer/@City) [1]’)

Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google