spBulkUpdate_ClientClassYears

CREATE  PROCEDURE spBulkUpdate_ClientClassYears (
    @ClientsID as integer,
    @XML as ntext
)
AS
DECLARE @idoc int

BEGIN TRANSACTION

EXEC sp_xml_preparedocument @idoc OUTPUT, @XML

DELETE FROM ClientClassYears 
WHERE ClientsID=@ClientsID AND ID NOT IN ( SELECT DISTINCT ClientClassYearsID FROM ClassYearNotes )

DECLARE myCursor CURSOR LOCAL FOR SELECT * FROM OPENXML (@idoc, '/classyearlist/classyear',1) 
    WITH ( ClassYearName nvarchar(10) '@name' , 
           ClassYearsGuid uniqueidentifier '@guid' )

DECLARE @Name nvarchar(10), @Guid uniqueidentifier

OPEN myCursor

FETCH NEXT FROM myCursor INTO @Name, @Guid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
    IF NOT EXISTS( SELECT ID FROM ClientClassYears WHERE ClientsID=@ClientsID AND Guid=@Guid )
    BEGIN
            INSERT INTO ClientClassYears ( Name, Guid, ClientsID )
        VALUES ( @Name, @Guid, @ClientsID )
    END
    END
    FETCH NEXT FROM myCursor INTO @Name, @Guid
END

CLOSE myCursor
DEALLOCATE myCursor

COMMIT TRANSACTION

EXEC sp_xml_removedocument @idoc