存储过程读取xml，还有更好的方法吗?SQL server 2008

本文关键字：方法 SQL 2008 server 更好 xml 读取 存储过程 | 更新日期: 2023-09-27 18:10:01

我这样做:

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。

有没有办法在不搞乱变量表的情况下做到这一点?

存储过程读取xml，还有更好的方法吗?SQL server 2008

使用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
相关文章：