RKsFAQ's: How insert in a table using xml file/data

XML insertion

CREATE PROC sp_insert_employee @empdata ntext 
AS 
       DECLARE @hDoc int 
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @empdata 
    INSERT INTO Employee 
      SELECT * 
      FROM OPENXML(@hDoc, '/Employee') 
            WITH Employee 
     EXEC sp_xml_removedocument @hDoc

XML Update

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