DROP PROCEDURE IF EXISTS public.sp_v039emptypeload;
DROP FUNCTION IF EXISTS public.sp_v039emptypeload;

CREATE PROCEDURE public.sp_v039emptypeload("Xml" bytea, "FileName" character varying)
LANGUAGE 'plpgsql'
AS $BODY$
	
DECLARE 
    T xml;
    tempStrXml text;
BEGIN
	
	select convert_from("Xml", 'WIN1251') into tempStrXml;	
	select replace(tempStrXml, 'encoding="windows-1251"','')::xml into T; 
	
	DROP TABLE IF EXISTS temp_emptype_load;

	CREATE TEMP TABLE temp_emptype_load AS
	SELECT 
		ID_VZ,
		NULLIF(N_VZ, '') AS N_VZ,
		DATEBEG :: timestamp,
		NULLIF(DATEEND, '') :: timestamp as DATEEND
	FROM XMLTABLE 
	(
		'/packet/zap' PASSING T 
		COLUMNS 
			ID_VZ text PATH './ID_VZ',
			N_VZ text PATH './N_VZ',
			DATEBEG text PATH './DATEBEG',
			DATEEND text PATH './DATEEND'
	);
	
	update ies.t_v039_emp_type t2
	set N_VZ = t1.N_VZ
		,DATEEND = t1.DATEEND
	from temp_emptype_load t1
	where 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 gen_random_uuid(), t1.ID_VZ, t1.N_VZ, t1.DATEBEG, t1.DATEEND
	from temp_emptype_load 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 IF EXISTS temp_emptype_load;
	
	RETURN;
END
$BODY$;