USP_CORPORATEPEERS_UPDATEFROMXML

      CREATE procedure dbo.USP_CORPORATEPEERS_UPDATEFROMXML
      (
      @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 ( RELATIONSHIPTYPES uniqueidentifier,UNIQUE(RELATIONSHIPTYPES))

      begin try
      insert into @xmlTable SELECT T.c.value('(RELATIONSHIPTYPES)[1]','uniqueidentifier') AS 'RELATIONSHIPTYPES'
            FROM @XML.nodes('/CORPORATEPEERS/ITEM') T(c)
      end try
      begin catch
        raiserror('BBERR_CORPORATEDUPLICATE',13,1)
      end catch

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

      declare @TempTbl table (RELATIONSHIPTYPES uniqueidentifier)

      insert into @TempTbl select 
        RELATIONSHIPTYPES
      from @xmlTable


      if @@Error <> 0
          return 1;

      declare @contextCache varbinary(128);
      declare @e int;

      -- cache current context information 

      set @contextCache = CONTEXT_INFO();

      -- set CONTEXT_INFO to @CHANGEAGENTID 

      if not @CHANGEAGENTID is null
          set CONTEXT_INFO @CHANGEAGENTID;

      -- update any items that no longer exist in the XML table

      update dbo.[RELATIONSHIPCONFIGURATION] set RELATIONSHIPCONFIGURATION.ISPEERRELATIONSHIPTYPE = 0 where [RELATIONSHIPCONFIGURATION].ID in 
          (select ID from dbo.RELATIONSHIPCONFIGURATION where ISPEERRELATIONSHIPTYPE = 1 EXCEPT select RELATIONSHIPTYPES from @TempTbl)    

      select @e=@@error;

      -- reset CONTEXT_INFO to previous value 

      if not @contextCache is null
          set CONTEXT_INFO @contextCache;

      if @e <> 0
          return 2;

      -- update the items that exist in the XML table and the db

      update dbo.[RELATIONSHIPCONFIGURATION]
              set [RELATIONSHIPCONFIGURATION].[ISPEERRELATIONSHIPTYPE]=1,
              [RELATIONSHIPCONFIGURATION].CHANGEDBYID = @CHANGEAGENTID,
              [RELATIONSHIPCONFIGURATION].DATECHANGED = @CHANGEDATE
          from  @TempTbl as [temp] 
        inner join dbo.[RELATIONSHIPCONFIGURATION] on RELATIONSHIPCONFIGURATION.ID = [temp].RELATIONSHIPTYPES
          where ([RELATIONSHIPCONFIGURATION].[ID]=temp.[RELATIONSHIPTYPES]) or 
              ([RELATIONSHIPCONFIGURATION].[ID] is null and temp.[RELATIONSHIPTYPES] is not null) or 
              ([RELATIONSHIPCONFIGURATION].[ID] is not null and temp.[RELATIONSHIPTYPES] is null)

      if @@Error <> 0
          return 3;    

        -- insert new items

      insert into [RELATIONSHIPCONFIGURATION] 
          ([ID], 
          [GENDERCODE],
          [APPLIESTOINDIVIDUALS],
            [APPLIESTOORGANIZATIONS], 
            [APPLIESTOGROUPS], 
            [RELATESTOINDIVIDUALS],
          [RELATESTOORGANIZATIONS],
          [RELATESTOGROUPS],
          [ISPEERRELATIONSHIPTYPE],
          [APPLIESTOSTUDENTS],
          [RELATESTOSTUDENTS],
          [ADDEDBYID],
          [CHANGEDBYID],
            [DATECHANGED],
          [DATEADDED])
      select  
          [RELATIONSHIPTYPES],
          0,
          1,
          1,
          1,
          1,
          1,
          1,
          1,
          0,
          0,
          @CHANGEAGENTID, 
          @CHANGEAGENTID, 
          @CHANGEDATE, 
          @CHANGEDATE
      from @TempTbl as [temp]
      where not exists (select ID from dbo.[RELATIONSHIPCONFIGURATION] as data where data.ID = [temp].RELATIONSHIPTYPES)

      if @@Error <> 0
          return 4;

      return 0;