if exists ( select * from sys.procedures p where p.name = 'sp_V039EmpTypeLoad' ) DROP PROCEDURE [dbo].[sp_V039EmpTypeLoad]
GO
CREATE PROCEDURE [dbo].[sp_V039EmpTypeLoad]
 @Xml xml
 ,@FileName varchar(50)
AS 
BEGIN

select
	T.c.value('./ID_VZ[1]','varchar(2)') as ID_VZ
	,case when T.c.value('./N_VZ[1]','varchar(254)')='' then NULL else T.c.value('./N_VZ[1]','varchar(254)') end as N_VZ
	,convert(datetime,T.c.value('./DATEBEG[1]','varchar(20)'),104) as DATEBEG
	,case when T.c.value('./DATEEND[1]','varchar(20)')='' then NULL else convert(datetime,T.c.value('./DATEEND[1]','varchar(20)'),104) end as DATEEND
into #V039
from @Xml.nodes('/packet/zap') T(c)

update t2
set t2.N_VZ=t1.N_VZ
	,t2.DATEEND=t1.DATEEND
from #V039 t1
join [IES].[T_V039_EMP_TYPE] t2 on (t1.ID_VZ = t2.ID_VZ and t1.DATEBEG = t2.DATEBEG)

insert into [IES].[T_V039_EMP_TYPE](V039EmpTypeID, ID_VZ, N_VZ, DATEBEG, DATEEND)
select NEWID(), t1.ID_VZ, t1.N_VZ, t1.DATEBEG, t1.DATEEND
from #V039 t1
left join [IES].[T_V039_EMP_TYPE] t2 on (t1.ID_VZ = t2.ID_VZ and t1.DATEBEG = t2.DATEBEG)
where t2.V039EmpTypeID is null

drop table #V039

END