c# - 存储过程读取xml，有什么更好的办法吗？ SQL 服务器 2008

Question

我做这样的事情:

CREATE PROCEDURE [dbo].[InsertStudents]
(
    @students nvarchar(max)
)
AS
DECLARE @studentstable TABLE
(
    RowIndex int,
    FirstName nvarchar(50),
    LastName nvarchar(50),
    Number nvarchar(20),
    IdSchool int
)
DECLARE @xmldata xml
SET @xmldata = @students
INSERT INTO @studentstable
SELECT S.Stud.query('./RowIndex').value('.','int') RowIndex, 
       S.Stud.query('./FirstName').value('.','nvarchar(50)') FirstName,
       S.Stud.query('./LastName').value('.','nvarchar(50)') LastName,
       S.Stud.query('./Number').value('.','nvarchar(50)') Number,
       S.Stud.query('./IdSchool').value('.','int') IdSchool,
FROM @xmldata.nodes('/Students/Student') AS S(Stud)   

 DECLARE @totalrows int
 DECLARE @currentrow int
 DECLARE @totalinserts int

 DECLARE @currentfirstname nvarchar(50)
 DECLARE @currentlastname nvarchar(50)
 DECLARE @currentnumber nvarchar(20)
 DECLARE @currentidschool int

 DECLARE @insertresult int

 SET @totalinserts = 0
 SET @totalrows=(SELECT COUNT(*) FROM @studentstable)
 SET @currentrow=0

 WHILE(@currentrow<@totalrows) BEGIN
    SELECT @currentfirstname = FirstName, @currentlastname = LastName, @currentnumber = Number, @currentidschool = IdSchool
    FROM @studentstable
    WHERE RowIndex=@currentrow
    EXEC @insertresult = InsertStudent @currentfirstname, @currentlastname, @currentnumber, @currentidschool
    IF @insertresult=0 BEGIN
        SET @totalinserts=@totalinserts+1
    END 
    SET @currentrow = @currentrow + 1
 END
 SELECT @totalinserts

如果插入有效，InsertStudent 返回 0，如果已经有该编号的学生，则返回 1。

有没有办法在不弄乱那个变量表的情况下做到这一点？

Answer 1

像这样使用merge。

merge Student
using (select S.Stud.query('./RowIndex').value('.','int') RowIndex, 
              S.Stud.query('./FirstName').value('.','nvarchar(50)') FirstName,
              S.Stud.query('./LastName').value('.','nvarchar(50)') LastName,
              S.Stud.query('./Number').value('.','nvarchar(50)') Number,
              S.Stud.query('./IdSchool').value('.','int') IdSchool
       from @xmldata.nodes('/Students/Student') as S(Stud)) as SXML
on Student.Number = SXML.Number
when not matched then
  insert (FirstName, LastName, Number, IdSchool)
    values (SXML.FirstName, SXML.LastName, SXML.Number, SXML.IdSchool);

select @@rowcount