USP_DATAFORMTEMPLATE_EDIT_SITEHIERARCHY

          CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SITEHIERARCHY
          (
            @CHANGEAGENTID uniqueidentifier,
            @SITEHIERARCHY 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,
                PARENTSITEID uniqueidentifier,
                NAME nvarchar(100),
                SEQUENCE int);

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

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

            return 0;                        
          end