if exists ( select * from sys.all_objects ao where ao.name = 'sp_EhrOnkTNMLoad' ) DROP PROCEDURE [dbo].[sp_EhrOnkTNMLoad]
GO

CREATE PROCEDURE [dbo].[sp_EhrOnkTNMLoad]
  @Xml xml
 ,@FileName varchar(50) = ''
AS 
BEGIN
	select
	     T.c.value('./ID[1]', 'int') AS [ID]
		,T.c.value('./ShortNameOfTopography[1]', 'varchar(1000)') AS [ShortNameOfTopography]
		,T.c.value('./ICDOTopography[1]', 'varchar(20)') AS [ICDOTopography]
		,T.c.value('./Morphology[1]', 'varchar(200)') AS [Morphology]
		,T.c.value('./Stage[1]', 'varchar(50)') AS [Stage]
		,T.c.value('./Tumor[1]', 'varchar(50)') AS [Tumor]
		,T.c.value('./ID_Tumor[1]', 'int') AS [ID_Tumor]
		,T.c.value('./Nodus[1]', 'varchar(50)') AS [Nodus]
		,T.c.value('./ID_Nodus[1]', 'int') AS [ID_Nodus]
		,T.c.value('./Metastasis[1]', 'varchar(50)') AS [Metastasis]
		,T.c.value('./ID_Metastasis[1]', 'int') AS [ID_Metastasis]
		,T.c.value('./Addition[1]', 'varchar(300)') AS [Addition]
		,T.c.value('./ID_Addition[1]', 'int') AS [ID_Addition]
		,T.c.value('./Classification[1]', 'int') AS [Classification]
		,T.c.value('./Version[1]', 'int') AS [Version]
	into #TNM
	from @xml.nodes('book/entries/entry') T(c) 

	update t0
	set  t0.[ShortNameOfTopography]	=	t1.[ShortNameOfTopography]	
		,t0.[ICDOTopography]=			t1.[ICDOTopography]		
		,t0.[Morphology] =				t1.[Morphology]			
		,t0.[Stage]	=					t1.[Stage]					
		,t0.[Tumor]	=					t1.[Tumor]					
		,t0.[ID_Tumor] =				t1.[ID_Tumor]				
		,t0.[Nodus]	=					t1.[Nodus]					
		,t0.[ID_Nodus] =				t1.[ID_Nodus]				
		,t0.[Metastasis] =				t1.[Metastasis]			
		,t0.[ID_Metastasis]	=			t1.[ID_Metastasis]			
		,t0.[Addition] =				t1.[Addition]				
		,t0.[ID_Addition] =				t1.[ID_Addition]			
		,t0.[Classification] =			t1.[Classification]		
		,t0.[Version] =					t1.[Version]
	from #TNM t1
		join ies.T_EHR_ONK_TNM t0 on t0.id = t1.ID

	INSERT INTO [IES].T_EHR_ONK_TNM
           (EhrOnkTnmID
		   ,[ID] 
		   ,[ShortNameOfTopography] 
		   ,[ICDOTopography] 
		   ,[Morphology] 
		   ,[Stage] 
		   ,[Tumor] 
		   ,[ID_Tumor] 
		   ,[Nodus] 
		   ,[ID_Nodus] 
		   ,[Metastasis] 
		   ,[ID_Metastasis] 
		   ,[Addition]  
		   ,[ID_Addition] 
		   ,[Classification] 
		   ,[Version])
	select 
			 NEWID()
			,t0.[ID]
			,t0.[ShortNameOfTopography]
			,t0.[ICDOTopography]
			,t0.[Morphology]
			,t0.[Stage] 
			,t0.[Tumor]
			,t0.[ID_Tumor]
			,t0.[Nodus] 
			,t0.[ID_Nodus] 
			,t0.[Metastasis] 
			,t0.[ID_Metastasis] 
			,t0.[Addition] 
			,t0.[ID_Addition] 
			,t0.[Classification] 
			,t0.[Version]
	FROM #TNM t0
		left join ies.T_EHR_ONK_TNM t1 on t1.ID = t0.ID
	where t1.EhrOnkTnmID is null
END
