how to fetch data from XML and update database table

I am passing serialized collection(XML) to stored procedure. My XML structure is-

<ArrayofDepartmentEntity>
  <Department>
    <id>1004</id>
    <budget>2500.oo</budget>
  </Department>
  <Department>
    <id>1080</id>
    <budget>3500.oo</budget>
  </Department>
  <Department>
    <id>1029</id>
    <budget>4500.00</budget>
  </Department>
</ArrayofDepartmentEntity>

How can I UPDATE corresponding budget column where department IDs are in above XML?? can any body write down the sql syntax.

my SP syntax-

ALTER PROCEDURE [dbo].[usp_SaveDepartentBudget] (                  
@departmentBudgetXML ntext = NULL                  
)                          
AS                        
B开发者_开发问答EGIN 
DECLARE @ptrHandle int     
EXEC sp_xml_preparedocument @ptrHandle OUTPUT, @departmentBudgetXML
....Here I want to update Budget foreach departmentID in @departmentBudgetXML

---

Try this code.

DECLARE @XMLString XML
SET @XMLString = '<ArrayofDepartmentEntity>
  <Department>
    <id>1004</id>
    <budget>2500.oo</budget>
  </Department>
  <Department>
    <id>1080</id>
    <budget>3500.oo</budget>
  </Department>
  <Department>
    <id>1029</id>
    <budget>4500.00</budget>
  </Department>
</ArrayofDepartmentEntity>'
SELECT
Department.col.value('id[1]','VARCHAR(20)')AS Id,
Department.col.value('budget[1]','VARCHAR(20)')AS Budget
FROM @XMLString.nodes('//Department') Department(col)

EDIT : Here is the code to Update

UPDATE tblInfo SET Budget = Department.col.value('budget[1]','MONEY')
FROM @XMLString.nodes('//Department') Department(col)
WHERE tblInfo.Id = Department.col.value('id[1]','INT')