Click here to Skip to main content
15,919,749 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

I want to update set of records at one time, through xml, So may i know how to write stored procedure in ms sql server 2005 which update the statusId of employees.

Regards
Nagaraj.J
Posted

1 solution

hello fried you can play around with your original data and table structure in below query to get your desired output
SQL
DECLARE @Student TABLE (
	ID Int,
	Name VARCHAR(10)
)

DECLARE @XML1 XML = '<students>
	<student><id>1</id><name>ABC</name></student>
	<student><id>2</id><name>XYZ</name></student>
	<student><id>3</id><name>ABC1</name></student>
	<student><id>4</id><name>XYZ1</name></student>
	<student><id>5</id><name>CDE1</name></student>
</students>'
DECLARE @XML XML = '<students>
	<student><id>1</id><name>ABC1111</name></student>
	<student><id>2</id><name>XYZ1111</name></student>
	<student><id>3</id><name>ABC1111</name></student>
	<student><id>4</id><name>XYZ1111</name></student>
	<student><id>5</id><name>CDE1111</name></student>
</students>'


INSERT INTO @Student (ID,Name)
SELECT  n.value('./ID[1]','INT')  As StudentID
		,n.value('./Name[1]','varchar(max)')  As StudentName
FROM @XML1.nodes('/Students/Student') x(n)
SELECT * FROM @Student


UPDATE @Student SET Name = AA.StudentName
FROM (
SELECT  n.value('./ID[1]','INT')  As StudentID
		,n.value('./Name[1]','varchar(max)')  As StudentName
	FROM @XML.nodes('/Students/Student') x(n)
	) AS AA
WHERE ID = AA.StudentID

SELECT * FROM @Student
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900