USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetThreeDHighLightData]    Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetThreeDHighLightData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetThreeDHighLightData]
GO

CREATE PROCEDURE [dbo].[GetThreeDHighLightData]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE @cGetImageContent CURSOR
	DECLARE @iImageContentId INT
	DECLARE @xmlData VARCHAR(MAX)
	
	TRUNCATE TABLE TempXMLStore
	SET @cGetImageContent = CURSOR FAST_FORWARD 
	FOR
		SELECT  ic.Id
		FROM ContentDescriptor cd 
		INNER JOIN ImageContent ic ON ic.ContentDescriptorId = cd.id AND cd.isActive = 'Y' AND cd.ContentTypeId = 3
	OPEN @cGetImageContent
	FETCH NEXT FROM @cGetImageContent INTO @iImageContentId
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @xmlData = '<?xml version="1.0" encoding="utf-8"?><root>'
		SET @xmlData = @xmlData+(SELECT(SELECT ThreeDTermsToHighLight.HighLightId as hid, ThreeDTermsToHighLight.TermNumberId as tn
			FROM VocabTerms Inner join ThreeDTermsToHighLight on VocabTerms.TermNumber=ThreeDTermsToHighLight.TermNumberId
			WHERE VocabTerms.VocabLexiconId = 1 AND IcId = @iImageContentId ORDER BY hid
			FOR XML RAW('it')) as XMLSTRING)
		SET @xmlData = @xmlData+'</root>'
		INSERT INTO TempXMLStore SELECT @iImageContentId, @xmlData
		
		FETCH NEXT FROM @cGetImageContent INTO @iImageContentId
	END
END
GO