drop procedure if exists public.sp_unloadingpatientscreatexml;
CREATE procedure public.sp_unloadingpatientscreatexml("GuidPackage" uuid, inout ff text = null, inout filename text = null)
AS
$BODY$
Begin
ff := 
(select 
'<?xml version="1.0" encoding="windows-1251"?>' 
||
(
select
xmlelement
(
	name "PERS_LIST"
,	--ZGLV
	xmlforest
	(
		xmlforest
		(
			'1.1' AS "VERSION"
		,	uah.DATA AS "DATA"
		,	uah.YEAR AS "YEAR"
		,	uah.MONTH AS "MONTH"
		,	uah.FILENAME AS "FILENAME"
		) as "ZGLV"
	)
,	(
		select
		xmlagg
		(
			xmlelement
			(
				name "PERS"
			,	xmlforest
				(	uad.ID AS "ID"
				,	uad.IM AS "IM"
				,	uad.FAM AS "FAM"
				,	uad.OT AS "OT"
				,	uad.W AS "W"
				,	uad.DR AS "DR"
				,	uad.DOCTYPE AS "DOCTYPE"
				,	uad.DOCSER AS "DOCSER"
				,	uad.DOCNUM AS "DOCNUM"
				,	uad.VPOLIS AS "VPOLIS"
				,	uad.SPOLIS AS "SPOLIS"
				,	uad.NPOLIS AS "NPOLIS"
				,	uad.ENP AS "ENP"
				,	uad.SNILS AS "SNILS"
				,	uad.REGION AS "REGION"
				,	uad.GOROD AS "GOROD"
				,	uad.RAJON AS "RAJON"
				,	uad.NP AS "NP"
				,	uad.UL AS "UL"
				,	uad.DOM AS "DOM"
				,	uad.KORP AS "KORP"
				,	uad.KV AS "KV"
				,	uad.CODE_MO AS "CODE_MO"
				,	uad.PODR AS "PODR"
				,	uad.N_UCH AS "N_UCH"
				,	uad.TYPE_UCH AS "TYPE_UCH"
				,	uad.FAM_DOCT AS "FAM_DOCT"
				,	uad.IM_DOCT AS "IM_DOCT"
				,	uad.OT_DOCT AS "OT_DOCT"
				,	uad.SNILS_DOCT AS "SNILS_DOCT"
				,	uad.POST_DOCT AS "POST_DOCT"
				,	uad.TYPE_DOCT AS "TYPE_DOCT"
				,	uad.DATE_PR_DOCT AS "DATE_PR_DOCT"
				,	uad.DATE_PRIKR AS "DATE_PRIKR"
				,	uad.TYPE_PRIKR AS "TYPE_PRIKR"
				,	uad.DATE_OTKR AS "DATE_OTKR"
				,	uad.SMO AS "SMO"
				,	(
						SELECT
						xmlagg
						(
							xmlforest
							(	uac.CONTACT AS "CONTACT"
							,	uac.TYPE AS "TYPE"
							)
						)
						FROM AKUZ.T_UNLOADING_ATTACHED_CONTACT uac
						WHERE uac.UnloadingAttachedData = uad.ID
					) as "CONTACTS"
				)
			)
		)
		FROM AKUZ.T_UNLOADING_ATTACHED_DATA uad
		WHERE uad.UnloadingAttachedHeader = "GuidPackage"
	)
)
FROM AKUZ.T_UNLOADING_ATTACHED_HEADER uah
WHERE uah.UnloadingAttachedHeaderID = "GuidPackage"
));
	filename := (SELECT uah.filename
	FROM AKUZ.T_UNLOADING_ATTACHED_HEADER uah
	WHERE uah.UnloadingAttachedHeaderID = "GuidPackage")::text;
End;
$BODY$ Language plpgsql;