USP_DATAFORMTEMPLATE_EDIT_FAFNFGCampaignLevelHierarchy

          CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_FAFNFGCampaignLevelHierarchy 
          (
            @ID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier,
            @LEVELHIERARCHY xml
          ) 
          as 
          begin
            set nocount on;

            if @CHANGEAGENTID is null  
              exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

            declare @CURRENTDATE datetime;
            set @CURRENTDATE = getdate();

            begin try                
              declare @TempTbl table (
                ID uniqueidentifier,
                PARENTID uniqueidentifier,
                NAME nvarchar(100),
                SEQUENCE int);

              insert into 
                @TempTbl
              select
                T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                T.c.value('(PARENTID)[1]','uniqueidentifier') AS 'PARENTID',
                T.c.value('(NAME)[1]','nvarchar(100)') AS 'NAME',
                T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE'
              from
                @LEVELHIERARCHY.nodes('/LEVELHIERARCHY/ITEM') T(c);                                

              -- Update the hierarchyids using using a recursive cte to build up paths (i.e. '/2/1/3')
              with LEVEL_SEQUENCE_CTE as (
                select
                  ID,
                  PARENTID,
                  SEQUENCE
                from
                  @TempTbl
              ),
              LEVEL_PATHS_CTE as (
                select
                  ID,
                  PARENTID,
                  (select FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH from dbo.FAFNFGCAMPAIGNLEVEL where FAFNFGCAMPAIGNLEVEL.NFGCAMPAIGNID = @ID  and FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH.GetLevel() = 1) as HIERARCHYPATH
                from
                  @TempTbl
                where
                  PARENTID is null                                    
                union all    
                select
                  LEVEL_SEQUENCE_CTE.ID,
                  LEVEL_SEQUENCE_CTE.PARENTID,
                  CAST(HIERARCHYPATH.ToString() + cast(LEVEL_SEQUENCE_CTE.SEQUENCE as varchar(20)) + '/' AS hierarchyid)
                from
                  LEVEL_PATHS_CTE
                  inner join LEVEL_SEQUENCE_CTE on LEVEL_SEQUENCE_CTE.PARENTID = LEVEL_PATHS_CTE.ID
              )update
                dbo.FAFNFGCAMPAIGNLEVEL
              set
                FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH = LEVEL_PATHS_CTE.HIERARCHYPATH,
                FAFNFGCAMPAIGNLEVEL.CHANGEDBYID = @CHANGEAGENTID,
                FAFNFGCAMPAIGNLEVEL.DATECHANGED = @CURRENTDATE
              from
                dbo.FAFNFGCAMPAIGNLEVEL
                inner join LEVEL_PATHS_CTE on FAFNFGCAMPAIGNLEVEL.ID = LEVEL_PATHS_CTE.ID
              where FAFNFGCAMPAIGNLEVEL.NFGCAMPAIGNID = @ID and FAFNFGCAMPAIGNLEVEL.HIERARCHYPATH <> LEVEL_PATHS_CTE.HIERARCHYPATH;
            end try
            begin catch
              exec dbo.USP_RAISE_ERROR;
              return 1;
            end catch

            return 0;                        
          end