Update tale using XML date

1. Create this table:

   CREATE TABLE Employee(eid int, fname varchar(20), lname varchar(20))
   

2. Add sample data:

   INSERT INTO Employee VALUES (1, 'Nancy', 'Davolio')
   INSERT INTO Employee VALUES (2, 'Andrew', 'Fuller')
   

3. Create this stored procedure in the database:

   CREATE PROC sp_update_employee @empdata ntext     
   AS     
   DECLARE @hDoc int       
   exec sp_xml_preparedocument @hDoc OUTPUT,@empdata  
   UPDATE Employee     
   SET     
         Employee.fname = XMLEmployee.fname,    
         Employee.lname = XMLEmployee.lname      
   FROM OPENXML(@hDoc, '/root/Employee')   
                WITH Employee XMLEmployee    
   WHERE  Employee.eid = XMLEmployee.eid    
   EXEC sp_xml_removedocument @hDoc    
   SELECT   *    
   from      Employee 
   FOR XML AUTO