SQL Server XML EDIT(Modify)/REMOVE(Delete)/ADD(Insert)

SQL Server XML values/node Amendments

                                                      My previous post I was discuss about XML data type and validations.This post I'm going to discuss about XML Modify(Insert/Update/Delete). Before start this, may be you all have some confusion which method is good(best practice or performance wise) modify XML data or node, I mean using T-SQL syntax like Replace or XML.Modify. to be honest I haven't good answer for that(I hope T-SQL replace much faster than XML modify) but will will discuss that in later post. 

Edit or Replace(Update) XML text values         

Following SQL script will show you how to edit(modify) XML text values contains in XML,

  DECLARE @xml xml='<root>
  <child id="01" name="Database">
    <Location>Colombo</Location>
    </child>
  <child id="02" name="Database User">
    <Location>Colombo Sri Lanka</Location>
    </child>
</root>';

SELECT @xml [Before Change];
--update value text in the first child
SET @xml.modify('
  replace value of (/root/child/Location/text())[1]
  with     "Colombo, Western Province"
')
SELECT @xml [After Change 01];

-- update attribute value 01
SET @xml.modify('
  replace value of (/root/child/@name)[1]
  with     "Database User Group(www.DatabaseUserGroup.com)"
')
SELECT @xml [After Change 02];

-- update attribute value 02
SET @xml.modify('
  replace value of (/root/child/@name)[2]
  with     "http://www.DatabaseUserGroup.com"
')
SELECT @xml [After Change 03];

 Add (Insert) XML Tags  

Hope you have clear idea about change text values in XML.Following SQL script will explain how to Add XML Tags,

 DECLARE @xml xml='<root>
  <child id="01" name="Database">
    <Location>Colombo</Location>
    </child>
  <child id="02" name="Database User">
    <Location>Colombo Sri Lanka</Location>
    </child>
</root>';

SELECT @xml [Before Change];

-- insert Date attribute          
SET @xml.modify('          
insert attribute Category {"Information technology (IT)"}          
into (/root/child[@id=01])[1] ')          
SELECT @xml [After Change 01];  
       
-- insert Date attribute but its value is retrived from a --sql variable @Date          
DECLARE @Date varchar(25);          
SET @Date =CONVERT(varchar(25),GETDATE(),20);
SET @xml.modify('          
insert attribute CurrentDate {sql:variable("@Date") }          
into   (/root/child[@id=01])[1] ')          
SELECT @xml [After Change 02];
         
-- insert sequence of attribute nodes (note the use of ',' --and ()           
-- around the attributes.  
DECLARE @month varchar(25), @weekday varchar(25), @year varchar(4);          
SET @month        = DATENAME(month, GETDATE());
SET @weekday    = DATENAME(weekday, GETDATE());
SET @year        = DATENAME(year, GETDATE());
       
SET @xml.modify('          
insert (  
            attribute Year {sql:variable("@year") },          
            attribute Month {sql:variable("@month") },          
            attribute Weekday {sql:variable("@weekday") }          
        )          
into (/root/child[@id=01])[1] ')          
SELECT @xml [After Change 03];

DECLARE @country xml;
SET @country = N'<Country>Sri Lanka</Country><PostalCode>00000</PostalCode>';        
-- insert new Tags from specified variable         
SET @xml.modify('          
insert sql:variable("@country")          
into (/root/child)[1] ')          
SELECT @xml [After Change 04];

-- insert comment          
SET @xml.modify('          
insert <!-- some comment -->          
before (/root)[1] ')          
SELECT @xml [After Change 05];

-- insert Program after root
SET @xml.modify('
insert <?Program = "SQLServer.exe" ?>
after (/root)[1] ')
SELECT @xml [After Change 06];

-- insert as First new Tags from specified variable
DECLARE @First xml;
SET @First = N'<First>Start Tag</First>';                  
SET @xml.modify('          
insert sql:variable("@First") as first          
into (/root/child)[1] ')          
SELECT @xml [After Change 07];
GO