spAddUpdate_SiteSurveyResults

CREATE  procedure [dbo].[spAddUpdate_SiteSurveyResults](
    @SurveyId            int,
    @CurrentUsersID        int,
    @ClientSitesID      int,
    @MachineIP            nvarchar(50),
    @bUpdate            bit,
    @ResultsXml            ntext
)
as
    set nocount on;
    declare @SurveyResponsesId     integer;
    declare @iDoc                 int;
    declare @answers table (ID     int);

    exec sp_xml_preparedocument @idoc output, @ResultsXml;

    if (@bUpdate=1)
    begin
        select @SurveyResponsesId = a.ID
        from SiteSurveyResponses a
        where a.SiteSurveysId = @SurveyId
        and a.ClientUsersID = @CurrentUsersID
        and a.MachineIP = @MachineIP;

        if (@SurveyResponsesId > 0)
        begin
            delete from SiteSurveyResults
            where SiteSurveyResponsesId = @SurveyResponsesId;
        end
    end

    insert into SiteSurveyResponses(SiteSurveysId, ClientUsersId, MachineIP)
    values (@SurveyId, @CurrentUsersID, @MachineIP);

    select @SurveyResponsesId = @@Identity;

    insert into SiteSurveyResults(SiteSurveyResponsesId, SiteSurveyAnswersId, SiteSurveyAnswerColumnsId, AnswerValue)
    select @SurveyResponsesId, SiteSurveyAnswersId, SiteSurveyAnswerColumnsId, AnswerValue
    from OPENXML(@idoc, '/Survey/Result', 1) 
    with (SiteSurveyAnswersId integer, SiteSurveyAnswerColumnsId integer, AnswerValue nvarchar(max)); --AnswerValue resized from nvarchar(512) to varchar(max)

    exec sp_xml_removedocument @idoc;