USP_CONSTITUENTRECOGNITION_GETDECLINEDLEVELS_ADDFROMXML

    CREATE procedure dbo.USP_CONSTITUENTRECOGNITION_GETDECLINEDLEVELS_ADDFROMXML
      (
      @CONSTITUENTID uniqueidentifier,
      @RECOGNITIONPROGRAMID uniqueidentifier,
      @XML xml,
      @CHANGEAGENTID uniqueidentifier = null,
      @CHANGEDATE datetime = null
      )

      as

      set nocount on;

      if @CHANGEAGENTID is null
          exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

      if @CHANGEDATE is null
          set @CHANGEDATE = getdate()


      declare @xmlTable table ( [ID] uniqueidentifier,
                                [RECOGNITIONLEVELID] uniqueidentifier)

      insert into @xmlTable SELECT T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
            T.c.value('(RECOGNITIONLEVELID)[1]','uniqueidentifier') AS 'RECOGNITIONLEVELID'
            FROM @XML.nodes('/DECLINEDRECOGNITIONLEVELS/ITEM') T(c)


      -- build a temporary table containing the values from the XML

      declare @TempTbl table (
         [ID] uniqueidentifier,
         [RECOGNITIONLEVELID] uniqueidentifier)

      insert into @TempTbl select 
          [ID],
          [RECOGNITIONLEVELID] 
      from @xmlTable

      update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

      if @@Error <> 0
          return 1;

      -- insert new items

      insert into [CONSTITUENTRECOGNITIONDECLINEDLEVEL] 
          ([CONSTITUENTID], 
          [ID],
          [RECOGNITIONLEVELID],        
          [RECOGNITIONPROGRAMID],
          ADDEDBYID, 
          CHANGEDBYID, 
          DATEADDED, 
          DATECHANGED)
      select @CONSTITUENTID, 
          [ID],
          [RECOGNITIONLEVELID], 
          @RECOGNITIONPROGRAMID,
          @CHANGEAGENTID, 
          @CHANGEAGENTID, 
          @CHANGEDATE, 
          @CHANGEDATE
      from @TempTbl as [temp]

      if @@Error <> 0
          return 2;

      return 0;