-- избавление от SheduleElements в шаблоне назначений 
select Recipe, HospitalDestinationDictionaryID, cast(null as xml) as scheduleXml
INTO #RR 
from AKUZ.T_HOSPITAL_DESTINATION_DICTIONARY

UPDATE x
	SET 
		x.Recipe =REPLACE(Recipe,'<?xml version="1.0" encoding="utf-16"?>','')
FROM #RR x

UPDATE x
	SET 
		x.Recipe =REPLACE(Recipe,'<?xml version="1.0" encoding="utf-8"?>','')
FROM #RR x

UPDATE x
	SET 
		x.scheduleXml = try_cast(x.Recipe as xml)
FROM #RR x

UPDATE #RR SET scheduleXml.modify('delete /HospitalRecipeSerialize/SheduleElements') where scheduleXml is not null

UPDATE #RR SET scheduleXml.modify('delete /HospitalRecipeSerialize/Components/Component/Methodology') where LEN(CAST(scheduleXml AS NVARCHAR(MAX))) > 4000

-- xml update
UPDATE hdd
SET hdd.Recipe = cast(scheduleXml as varchar(max)) 
FROM AKUZ.T_HOSPITAL_DESTINATION_DICTIONARY hdd
JOIN #RR x on x.HospitalDestinationDictionaryID = hdd.HospitalDestinationDictionaryID

drop table #RR

--добавление поля Примечания, чтобы врач вносил какие-нибудь изменения в назначении, не трогая Signa
ALTER TABLE [AKUZ].[T_HOSPITAL_DESTINATION] ADD [Note] varchar(500) NULL
GO

-- изменение Signa с varchar(max) на varchar(1000) в t_hospital_destination
UPDATE akuz.t_hospital_destination
	SET Signa = LEFT(Signa,1000)
WHERE LEN(Signa) > 1000

alter table akuz.t_hospital_destination alter column Signa varchar(1000) not null
go

-- изменение Signa с varchar(max) на varchar(1000) и Recipe с varchar(max) на varchar(4000) в t_hospital_destination_dictionary
UPDATE AKUZ.T_HOSPITAL_DESTINATION_DICTIONARY
	SET Signa = LEFT(Signa, 1000)
WHERE LEN(Signa) > 1000;

alter table akuz.t_hospital_destination_dictionary alter column Signa varchar(1000) not null
go
alter table akuz.t_hospital_destination_dictionary alter column Recipe varchar(4000) not null
go