USE [ZEKSPro]
GO
/****** Object:  StoredProcedure [dbo].[spModelBulkUpdate]    Script Date: 02/08/2014 19:09:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[spModelBulkUpdate]
(
	@strXML varchar(max)
)


As
BEGIN
		Declare @XMLContent  XML
		set @XMLContent = @strXML

		declare @str varchar(max)

		set @str ='select '

		declare @coltable table
		(
			colid int Identity(1,1),
			colheader varchar(500)
		)

		insert into @coltable
		SELECT 
		a.b.value('local-name(.)', 'nvarchar(max)') test
		FROM @XMLContent.nodes('/NewDataSet[1]/Table1[1]/*') as a(b)
		Declare @elementname varchar(max)
		set @elementname = ''
		Declare @count int, @index int
		set @count = (Select COUNT(*) from @coltable)
		set @index = 1
		
		while (@count >0)
		begin
			set @elementname = Replace((Replace((Replace((Replace((Replace((Replace((Replace((Replace((Replace((select colheader from @coltable where colid = @index),'_x0020_',' ')),'_x000D__x000A_','')),'x003','')),'_x0028_','(')),'_x0029_',')')),'x0023','.')),'x002C',',')),'x002F','/')),'x00B0','°')
			set @str = @str + 'a.b.value(''./' + (select colheader from @coltable where colid = @index) + '[1]'',''nvarchar(max)'')' + ' as ' +'['+ @elementname +'],' + CHAR(10) 
			--set @str = @str + 'a.b.value(''./' + (select colheader from @coltable where colid = @index) + '[1]'',''nvarchar(max)'')' + ' as ' +'['+ Replace((Replace((select colheader from @coltable where colid = @index),'_x0020_',' ')),'_x000D__x000A_','')+'],' + CHAR(10) 
			set @count = @count - 1
			set @index = @index + 1
		end
		set @str = SUBSTRING(@str,0,len(@str)-1)
		
		set @str = @str +' into ##ty2 FROM @XMLContent.nodes(''/NewDataSet[1]/Table1'') as a(b);'
		
		declare @query varchar(max)
		set @query = 'Declare @XMLContent  XML Set @XMLContent = '''+ CONVERT(varchar(max), @XMLContent)+''''
		set @str = @query + @str 
		
		execute (@str)

		

		DECLARE @colsUnpivot AS NVARCHAR(MAX),@query1 AS NVARCHAR(MAX)

		select @colsUnpivot 

		= stuff((select ','+quotename(C.name)

		FROM tempdb.sys.columns c

		WHERE c.object_id = OBJECT_ID('tempdb..##ty2' ) 

		for xml path('')), 1, 1, '')

		
		set @colsUnpivot=REPLACE(@colsUnpivot,'[ProdID],[ProductName],[Instructions],','')
		

		
		set @query1 

		= 'select ProdID, ProductName, Instructions,SpecificationName,SpecValue 
		 into ##ty4 from ##ty2

		unpivot
		(

		SpecValue

		for SpecificationName in ('+ @colsunpivot +')

		) u'



		execute (@query1)
		
		
		Declare @ProdID int,@ProductName nvarchar(100),@Instructions nvarchar(100), @SpecificationName nvarchar(100),@SpecValue nvarchar(100), @SpecificationID int,@SubCatID int, @SpecName nvarchar(100), @CondensorType nvarchar(100)
		Declare cursorSpecification cursor for 
		select rtrim(ltrim(ProdID)),rtrim(ltrim(ProductName)),rtrim(ltrim(Instructions)),rtrim(ltrim(SpecificationName)),rtrim(ltrim(SpecValue)) from ##ty4
		open cursorSpecification
		fetch next from cursorSpecification into @ProdID,@ProductName,@Instructions,@SpecificationName,@SpecValue
		While @@Fetch_Status = 0 
		Begin 
			set @SubCatID = (Select SubCatID from tblProduct where rtrim(ltrim(ProdID)) = @ProdID)
			--print (@SpecificationName)	
			--set @SpecName = Replace((REPLACE(@SpecificationName,'Air Cooled_','')),'Water Cooled_','')
			--set @SpecName = SUBSTRING(@SpecificationName,
			print(@SpecificationName)
			if(CHARINDEX('AIR COOLED',Upper(@SpecificationName))>0)
			begin
				--set @CondensorType = SUBSTRING(@SpecificationName,0,CHARINDEX(@SpecificationName,'Air Cooled_',0))
				set @CondensorType = 1
			end
			else if(CHARINDEX('WATER COOLED',Upper(@SpecificationName))>0)
			begin
				--set @CondensorType = SUBSTRING(@SpecificationName,0,CHARINDEX(@SpecificationName,'Water Cooled_',0))
				set @CondensorType = 2
			end
			else
			begin
				--set @CondensorType = 'Default'
				set @CondensorType = 3
			end
			set @SpecName = Replace((REPLACE(@SpecificationName,'Air Cooled_','')),'Water Cooled_','')
			print (@SpecName)
			print (@CondensorType)
			Declare cursorSpecID cursor for 
			Select SpecID from dbo.tblProductSpecificationMaster where rtrim(ltrim(SpecificationName)) = @SpecName and SubCatID = @SubCatID and rtrim(ltrim(CondensorType)) = @CondensorType
			open cursorSpecID
			fetch next from cursorSpecID into @SpecificationID 
			--print (@SpecificationID)
			While @@Fetch_Status = 0 
			Begin
				if exists(Select * from tblProductDtlSpecificationData where rtrim(ltrim(ProdID)) = @ProdID and rtrim(ltrim(SpecID)) = @SpecificationID)
				begin
					update dbo.tblProductDtlSpecificationData
					set SpecValue = @SpecValue
					where SpecID = @SpecificationID
					and ProdID = @ProdID
					fetch next from cursorSpecID into @SpecificationID 
				end
				else
				begin
					insert into tblProductDtlSpecificationData(ProdID, SpecID, SpecValue)
					values(@ProdID,@SpecificationID,@SpecValue) 
					fetch next from cursorSpecID into @SpecificationID 
				end
			 End
			 Close cursorSpecID
			 Deallocate cursorSpecID
			 fetch next from cursorSpecification into @ProdID,@ProductName,@Instructions,@SpecificationName,@SpecValue
		end
		
		Close cursorSpecification
		Deallocate cursorSpecification
		drop table ##ty2
		drop table ##ty4
END