USP_ORGANIZATIONHIERARCHY_UPDATEFROMXML

            CREATE PROCEDURE [dbo].[USP_ORGANIZATIONHIERARCHY_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()

            /*This is a customized version of the UPDATEFROMXML to accommodate the same relations structure.
              Be warned that the insert/update/delete order is different
            */

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

            declare @TempTbl table (
               [ID] uniqueidentifier,
               [PARENTID] uniqueidentifier,
               [SEQUENCE] int
            )

            insert into @TempTbl select 
                [ID],
                [PARENTID],
                [SEQUENCE]
            from dbo.UFN_ORGANIZATIONHIERARCHY_FROMITEMLISTXML(@XML)

            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 dbo.ORGANIZATIONHIERARCHY(
                [ID],
                [PARENTID],
                [SEQUENCE],
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select  
                [ID],
                [PARENTID],
                [SEQUENCE],
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE
            from @TempTbl as [temp]
            where not exists (select ID from dbo.ORGANIZATIONHIERARCHY as data where data.ID = [temp].ID)

            if @@Error <> 0
                return 2;

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

            update ORGANIZATIONHIERARCHY
                    set ORGANIZATIONHIERARCHY.[PARENTID]=temp.[PARENTID],
                    ORGANIZATIONHIERARCHY.[SEQUENCE]=temp.SEQUENCE,
                    ORGANIZATIONHIERARCHY.CHANGEDBYID = @CHANGEAGENTID,
                    ORGANIZATIONHIERARCHY.DATECHANGED = @CHANGEDATE
                from dbo.ORGANIZATIONHIERARCHY inner join @TempTbl as [temp] on ORGANIZATIONHIERARCHY.ID = [temp].ID
                where (ORGANIZATIONHIERARCHY.[PARENTID]<>temp.[PARENTID]) or 
                    (ORGANIZATIONHIERARCHY.[PARENTID] is null and temp.[PARENTID] is not null) or 
                    (ORGANIZATIONHIERARCHY.[PARENTID] is not null and temp.[PARENTID] is null) or 
                    (ORGANIZATIONHIERARCHY.[SEQUENCE]<>temp.[SEQUENCE]) or 
                    (ORGANIZATIONHIERARCHY.[SEQUENCE] is null and temp.[SEQUENCE] is not null) or 
                    (ORGANIZATIONHIERARCHY.[SEQUENCE] is not null and temp.[SEQUENCE] is null)

            if @@Error <> 0
                return 3;    

            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;

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

            delete from dbo.ORGANIZATIONHIERARCHY 
            where ORGANIZATIONHIERARCHY.ID not in 
                (select ID from @TempTbl)

            select @e=@@error;

            -- reset CONTEXT_INFO to previous value 

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @e <> 0
                return 4;

            return 0;