Update using XML part2

ALTER proc [dbo].[sp_Update_ModelPrice]
(
@xmlString varchar(max)
)
as
DECLARE @index int
--set @xmlString='<brand><model id="1"><category id="1"><subCatid>1</subCatid><price>12</price></category></model></brand>'
BEGIN
EXEC sp_xml_preparedocument @index OUTPUT, @xmlString
update dbo.tblModelPrice
set Price=xmlprice

FROM OPENXML (@index, 'brand/model/category')
WITH (xmlModelId int '../@id',xmlCatid Varchar(10) '@id',xmlsubCat varchar(100) 'subCatid',xmlprice varchar(100) 'price')
where ModelID=xmlModelId and ModelCategoryID=xmlCatid and ModelSubCategoryID=xmlsubCat

EXEC sp_xml_removedocument @index

END

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