if exists ( select * from sys.procedures p where p.name = 'sp_F043FREKMPLoad' ) DROP PROCEDURE [dbo].sp_F043FREKMPLoad
GO

CREATE PROCEDURE  [dbo].[sp_F043FREKMPLoad]
 @Xml xml
 ,@FileName varchar(50)
AS 
BEGIN

	SELECT  
		T.c.value('./IDEKMP[1]','varchar(8)') AS IDEKMP,
		T.c.value('./N_EXPERT[1]','varchar(7)') AS N_EXPERT, 
		case when T.c.value('./D_BEGIN[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./D_BEGIN[1]', 'varchar(20)'),104) end AS D_BEGIN,
		case when T.c.value('./D_END[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./D_END[1]', 'varchar(20)'),104) end AS D_END,
		T.c.value('./NAME_E[1]','varchar(2)') AS NAME_E, --?
		T.c.value('./WORKPHONE[1]','varchar(250)') AS WORKPHONE, 
		T.c.value('./MOBILEPHONE[1]','varchar(250)') AS MOBILEPHONE, 
		T.c.value('./EMAIL[1]','varchar(250)') AS EMAIL, 
		T.c.value('./MEE[1]','varchar(10)') AS MEE, 
		T.c.value('./KMP[1]','varchar(10)') AS KMP, 
		case when T.c.value('./DATEBEG[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DATEBEG[1]', 'varchar(20)'),104) end AS DATEBEG,
		case when T.c.value('./DATEEND[1]', 'varchar(20)')='' then null else CONVERT(date,T.c.value('./DATEEND[1]', 'varchar(20)'),104) end AS DATEEND
	INTO #F043
	FROM @xml.nodes('/packet/zap') T(c)

	UPDATE t2 
		SET 
            [N_EXPERT]            = t1.[N_EXPERT]
           ,[D_BEGIN]			  = t1.[D_BEGIN]
           ,[D_END]				  = t1.[D_END]
           ,[NAME_E]			  = t1.[NAME_E]
           ,[WORKPHONE]			  = t1.[WORKPHONE]
           ,[MOBILEPHONE]		  = t1.[MOBILEPHONE]
           ,[EMAIL]				  = t1.[EMAIL]
           ,[MEE]				  = t1.[MEE]
           ,[KMP]				  = t1.[KMP]
           ,[DATEBEG]			  = t1.[DATEBEG]
           ,[DATEEND]			  = t1.[DATEEND]
	FROM #F043 t1 
	JOIN [IES].[T_F043_FREKMP] t2 ON t1.IDEKMP = t2.IDEKMP 


	INSERT INTO [IES].[T_F043_FREKMP]
           ([F043FrekmpID]
           ,[IDEKMP]
           ,[N_EXPERT]
           ,[D_BEGIN]
           ,[D_END]
           ,[NAME_E]
           ,[WORKPHONE]
           ,[MOBILEPHONE]
           ,[EMAIL]
           ,[MEE]
           ,[KMP]
           ,[DATEBEG]
           ,[DATEEND])
    SELECT 
			NEWID()
           ,t1.[IDEKMP]
           ,t1.[N_EXPERT]
           ,t1.[D_BEGIN]
           ,t1.[D_END]
           ,t1.[NAME_E]
           ,t1.[WORKPHONE]
           ,t1.[MOBILEPHONE]
           ,t1.[EMAIL]
           ,t1.[MEE]
           ,t1.[KMP]
           ,t1.[DATEBEG]
           ,t1.[DATEEND]
	FROM #F043 t1 
	LEFT JOIN [IES].[T_F043_FREKMP] t2 ON t1.IDEKMP = t2.IDEKMP
	WHERE t2.F043FrekmpID is null


	DROP TABLE #F043

END
