关于xml存储过程 [英] Regarding xml stored procedure

问题描述

你好，

我想一次通过xml更新一组记录，所以我可能知道如何在ms sql server 2005中编写存储过程，从而更新了员工的statusId.

问候
Nagaraj.J

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

推荐答案

您好，您可以在下面的查询中使用原始数据和表结构来获取所需的输出

hello fried you can play around with your original data and table structure in below query to get your desired output

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

这篇关于关于xml存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！