IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ConvertFreeBedsInformation]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ConvertFreeBedsInformation]
GO
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:		Vitacore (с)
---- Create date: 2018-12-05
---- Description:	конвертация FREE_BEDS_INFORMATION для Карелии по 263 приказу
---- =============================================
--CREATE PROCEDURE [dbo].[sp_ConvertFreeBedsInformation]
--	@Xml xml
--AS
--BEGIN


--DECLARE @t1 table
--(OuterXML varchar(max), ERR varchar(max))

--create table #ERROR
--(
--	[OSHIB] int,
--	[TEXT] varchar(max)
--)

----------------------------------------------
--SELECT
--	 T.c.value('./ZGLV[1]/DATA[1]', 'datetime') AS [DATA]
--	,T.c.value('./ZGLV[1]/TYPE[1]', 'varchar(6)') AS [TYPE]
--	,T.c.value('./ZGLV[1]/ID[1]', 'uniqueidentifier') AS [ID]
--	,T.c.value('./ZAP[1]/DTA_RAB[1]', 'datetime') AS [DTA_RAB]
--	,T.c.value('./ZAP[1]/MCOD_STC[1]', 'varchar(6)') AS [MCOD_STC]
--	,T.c.value('./ZAP[1]/MPODR_STC[1]', 'varchar(2)') AS [MPODR_STC]
--	,T.c.value('./ZAP[1]/USL_OK[1]', 'int') AS [USL_OK]
--	,T.c.value('./ZAP[1]/KOD_PFK[1]', 'int') AS [KOD_PFK]
--	,T.c.value('./ZAP[1]/KOL_PUS[1]', 'int') AS [KOL_PUS]
-- into #XML
-- FROM   @xml.nodes('/FREE_BEDS_INFORMATION') T(c)

	



--	--insert into #ERROR ([OSHIB],[TEXT])
--	--select 1, 'Не заполнен обязательный атрибут USL_OK'
--	--from #XML
--	--where USL_OK is null

--if (Select COUNT(*) from #ERROR) <> 0--Если есть ошибки, то дальше нет смысла делать проверки
--begin
--INSERT @t1 (OuterXML, ERR)
--select null,
--	(select 
--	'<?xml version="1.0" encoding="Windows-1251"?>
--	' + (
--	select 
--		OSHIB AS 'OSHIB',
--		[TEXT] as 'TEXT'
--	FROM #ERROR
--	FOR XML PATH('PR'), 
--	ROOT('FLK_P')
--	)	)
--end
--else
--begin

--INSERT @t1 (OuterXML, ERR)
--select
--	(select
--		--header
--		x.[MCOD_STC]		AS 'header/LPU' 
--		,x.[TYPE]			AS 'header/TYPE'
--		,x.[ID]			AS 'header/ID'

--		,x.[DTA_RAB]		as 'body/ACTUAL_DATE'
--		,x.[KOL_PUS]		as 'body/AMOUNT'
--		,(select top 1 sb.DivisionLinkStructureBedID 
--			from [RDS].T_DIVISION_LINK_STRUCTURE_BED sb
--			join [RDS].T_DIVISION d on d.DivisionID = sb.Division
--			join RDS.T_BRANCH b on b.BranchID = d.Branch
--		where sb.StructureBed = x.KOD_PFK and b.LpuMain = x.[MCOD_STC] --and [MPODR_STC] is not null and [MPODR_STC] = b.BranchCode
--		)	as 'body/DLSB'

--	from #XML x
--	FOR XML PATH (''), ROOT('FREE_BEDS_INFORMATION')
--	)
--,null
--end

--select * from @t1

--drop table #ERROR

--	drop table #XML
--end

