Modify your XML document using SQL

Many of the situations, we will have to modify the xml that is passed on to the sql server.

Some of the common operations you might need to handle in SQL are

• adding an element
• adding an attribute
• updating an attribute
• delete an element
• delete an attribute

 Let us try this with a sample document.

Declare @myXML xml

SELECT @myXML = '<CUSTOMERSDATA>
<CUSTOMER FName="Raja" LName="Rao" City="Bangalore" State="KA" />
</CUSTOMERSDATA>'


Add an Element

SET @myXML.modify('insert <CUSTOMER FName="Ravi" LName="Krishna" City="Chicago" State="IL" />
into (/CUSTOMERSDATA)[1]') ;


select @myXML

Output:

<CUSTOMERSDATA>
<CUSTOMER FName="Raja" LName="Rao" City="Bangalore" State="KA" />
<CUSTOMER FName="Ravi" LName="Krishna" City="Chicago" State="IL" />
</CUSTOMERSDATA>

Add an attribute

SET @myXML.modify('insert attribute Phone {"333-333-3333" }
into (/CUSTOMERSDATA/CUSTOMER[@FName="Ravi"])[1]') ;


select @myXML


Output:
<CUSTOMERSDATA>
<CUSTOMER FName="Raja" LName="Rao" City="Bangalore" State="KA" />
<CUSTOMER FName="Ravi" Phone="333-333-3333" LName="Krishna" City="Chicago" State="IL" />
</CUSTOMERSDATA>



Updating an attribue

SET @myXML.modify('replace value of (/CUSTOMERSDATA/CUSTOMER[@FName="Ravi"]/@Phone)[1] with "444-444-4444"') ;


select @myXML

Output:<CUSTOMERSDATA>
<CUSTOMER FName="Raja" LName="Rao" City="Bangalore" State="KA" />
<CUSTOMER FName="Ravi" Phone="444-444-4444" LName="Krishna" City="Chicago" State="IL" />
</CUSTOMERSDATA>



Delete attribue

SET @myXML.modify('delete (/CUSTOMERSDATA/CUSTOMER[@FName="Ravi"]/@Phone)[1]') ;


select @myXML

Output:
<CUSTOMERSDATA>
<CUSTOMER FName="Raja" LName="Rao" City="Bangalore" State="KA" />
<CUSTOMER FName="Ravi" LName="Krishna" City="Chicago" State="IL" />
</CUSTOMERSDATA>



Delete Element

SET @myXML.modify('delete (/CUSTOMERSDATA/CUSTOMER[@FName="Ravi"])[1]') ;


select @myXML


Output:

<CUSTOMERSDATA>
<CUSTOMER FName="Raja" LName="Rao" City="Bangalore" State="KA" />
</CUSTOMERSDATA>