how to fetch data from XML and update database table.
- by ppp
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
BEGIN
DECLARE @ptrHandle int
EXEC sp_xml_preparedocument @ptrHandle OUTPUT, @departmentBudgetXML
....Here I want to update Budget foreach departmentID in @departmentBudgetXML
