USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetTermNumberData]    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].[GetTermNumberData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTermNumberData]
GO

CREATE PROCEDURE [dbo].[GetTermNumberData]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	DECLARE @cGetTermNumber CURSOR
	DECLARE @iImageContentId INT
	DECLARE @xmlData VARCHAR(MAX)
	
	TRUNCATE TABLE TempXMLStore
	SET @cGetTermNumber = 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 = 2
	OPEN @cGetTermNumber
	FETCH NEXT FROM @cGetTermNumber INTO @iImageContentId
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @xmlData = '<?xml version="1.0" encoding="utf-8"?><root>'
		SET @xmlData = @xmlData+(SELECT(SELECT DISTINCT vt.TermNumber as tn, vt.VocabLexiconId as lId, vt.TermText as tt
		FROM VocabTerms vt
		INNER JOIN AnnotationPin ap ON vt.TermNumber = ap.VocabTermNumbers
		INNER JOIN ImageContent ic ON ap.ImageContentId = ic.Id  
		WHERE ic.Id = @iImageContentId FOR XML RAW('it')) as XMLSTRING)
		SET @xmlData = @xmlData+'</root>'
		INSERT INTO TempXMLStore SELECT @iImageContentId, @xmlData
		
		FETCH NEXT FROM @cGetTermNumber INTO @iImageContentId
	END
END


GO