Save data through XML

 StringBuilder objBuilder = GetCheckedSitemap();
Role objRole = new Role();
          objRole.AddedBy = "admin";
          objRole.LastModBy = "admin";
          objRole.RoleID = roleID;
          objRole.XMLData = objBuilder.ToString();

 private StringBuilder GetCheckedSitemap()
    {
    
        StringBuilder objBuilder = new StringBuilder();
        objBuilder.Append("<?xml version=\"1.0\"?>");
        objBuilder.Append("<Root>");
  objBuilder.Append("<sitemaprole roleid=\"" + roleID + "\" SiteMapID=\"" + row1["NavId"] + "\"/>");
   objBuilder.Append("</Root>");

        return objBuilder;
    }

proc
@XMl xml=null


 update tblUwkrn  set flow= flow+ '','' + @flow
                         ,StaffId=@StaffId
                         ,StaffDate=GETDATE()
                         ,StaffStatus=@StaffStatus
                         ,AsmStatus=@AsmStatus
         from tblUwkrn inner join
         @XMl.nodes(''UwkrnList'')r(item)
        on tblUwkrn.uwkrn=item.value(''@UwkrnNo'',''nvarchar(100)'')               
       and eventid=@EventId and AsmId=@AsmId and confirm_status=''Yes''

CREATE PROCEDURE[dbo].[usp_ADM_SaveSiteMapRole]
(
   
    @RoleID varchar(50),
   
    @XMLData varchar(max),
    @AddedBy varchar(50),
    @LastModBy varchar(50),
    @EventId nvarchar(50)
)

AS
BEGIN

BEGIN TRAN SaveSiteMapRole
 BEGIN TRY
   
    DECLARE @hDoc int
    DECLARE @Error int   

    EXEC sp_xml_preparedocument @hDoc OUTPUT,@XMLData

    DELETE FROM ADM_SiteMapRole
    WHERE RoleID=@RoleID and EventId=@EventId

    SELECT @EventId as eventId, roleid,sitemapid,1 as IsActive,@AddedBy as AddedBy,GETDATE() as AddedDTM,
        @LastModBy as LastModBy,GETDATE() as LastModDTM
    INTO #ADM_SiteMapRole
    FROM OpenXML(@hDoc,''/Root/sitemaprole'')
    WITH (
            roleid varchar(50) ''@roleid'',
            sitemapid int ''@SiteMapID''       
         )
       
         declare
         @eventId1 varchar(50),
         @roleid1 varchar(50),
         @sitemapid1 int,       
         @IsActive1 bit,
         @AddedBy1 varchar(50),
         @AddedDTM1 datetime,
         @LastModBy1 varchar(50),
         @LastModDTM1 datetime
       
       
        DECLARE sr_cursor CURSOR LOCAL FOR
            SELECT eventId,roleid,sitemapid,IsActive,AddedBy,AddedDTM,LastModBy,LastModDTM
            FROM #ADM_SiteMapRole                
        OPEN sr_cursor;
        FETCH NEXT FROM sr_cursor INTO @eventId1, @roleid1,@sitemapid1,@IsActive1,@AddedBy1,@AddedDTM1,@LastModBy1,@LastModDTM1
            WHILE @@FETCH_STATUS = 0
            BEGIN
                --login here
                if not exists(select 1 from ADM_SiteMapRole where RoleID=@roleid1 and SiteMapID=@sitemapid1 and IsActive=@IsActive1 and EventId= @eventId1)
                begin
                    --insert
                    INSERT INTO ADM_SiteMapRole(EventId,RoleID,SiteMapID,IsActive,AddedBy,AddedDTM,LastModBy,LastModDTM)
                    SELECT @eventId1, @roleid1,@sitemapid1,@IsActive1,@AddedBy1,@AddedDTM1,@LastModBy1,@LastModDTM1
                    --find all parent id and insert for them 
                    declare @t1  as table
                    (
                        id int not null identity(1,1),
                        sitemapid int,
                        parentid int                   
                    )
                   
                    insert into @t1(sitemapid,parentid )
                    select sitemapid,parentid
                    from dbo.func_ADM_GetParentsOfId(@sitemapid1, @eventId1) where sitemapid <> @sitemapid1
                   
                    declare @count int
                    declare @sid int
                    set @count=1
                    while ( @count <= (select MAX(id) from @t1) )
                    begin                   
                        select @sid=sitemapid from @t1 where id=@count
                        if not exists(select 1 from ADM_SiteMapRole where RoleID=@roleid1 and SiteMapID=@sid and IsActive=@IsActive1 and EventId=@eventId1)
                        begin
                            INSERT INTO ADM_SiteMapRole(EventId,RoleID,SiteMapID,IsActive,AddedBy,AddedDTM,LastModBy,LastModDTM)
                            SELECT @eventId1, @roleid1,@sid,@IsActive1,@AddedBy1,@AddedDTM1,@LastModBy1,@LastModDTM1
                        end                       
                        set @count = @count + 1
                    end     
                end               
                FETCH NEXT FROM sr_cursor INTO @eventId1, @roleid1,@sitemapid1,@IsActive1,@AddedBy1,@AddedDTM1,@LastModBy1,@LastModDTM1
            END
        CLOSE sr_cursor;
        DEALLOCATE sr_cursor;      

    --ERROR HANDLER
    SET @Error = @@error
    IF (@Error <> 0)
    BEGIN
        exec sp_xml_removedocument @hDoc
        RETURN
    END   

    drop table #ADM_SiteMapRole
    exec sp_xml_removedocument @hDoc
   
    COMMIT TRAN SaveSiteMapRole
 end Try
 BEGIN CATCH

 ROLLBACK TRAN SaveSiteMapRole
 close  SaveSiteMapRole
 SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE()
,ERROR_MESSAGE()
 END CATCH

END