USE [AIADatabaseV5] GO /****** Object: StoredProcedure [dbo].[GetImagePinData] Script Date: 2/1/2018 12:15:55 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- alter script of GetImagePinData SP if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetImagePinData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[GetImagePinData] GO CREATE PROCEDURE [dbo].[GetImagePinData] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from SET NOCOUNT ON; DECLARE @cGetImageContentId CURSOR DECLARE @iImageContentId INT DECLARE @xmlData VARCHAR(MAX) TRUNCATE TABLE TempXMLStore SET @cGetImageContentId = 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 @cGetImageContentId FETCH NEXT FROM @cGetImageContentId INTO @iImageContentId WHILE @@FETCH_STATUS = 0 BEGIN SET @xmlData = '' SET @xmlData = @xmlData+(SELECT Tag, Parent, [root!1!aoi], [it!2!pId], [it!2!hX], [it!2!hY], [it!2!pX], [it!2!pY], [it!2!tId], [it!2!bsId], [it!2!bs] FROM (SELECT DISTINCT 1 AS Tag, NULL AS Parent, aoi.FileName AS [root!1!aoi], NULL AS [it!2!pId], NULL AS [it!2!hX], NULL AS [it!2!hY], NULL AS [it!2!pX], NULL AS [it!2!pY], NULL AS [it!2!tId], NULL AS [it!2!bsId], NULL AS [it!2!bs] FROM AtlasOrientImage aoi RIGHT JOIN ImageContent ic ON aoi.ContentDescriptorId = ic.ContentDescriptorId WHERE ic.Id = @iImageContentId UNION ALL SELECT DISTINCT 2 AS Tag, 1 AS Parent, aoi1.FileName AS [root!1!aoi], ap.Id AS [it!2!pId], ap.HeadX AS [it!2!hX], ap.HeadY AS [it!2!hY], ap.PointX AS [it!2!pX], ap.PointY AS [it!2!pY], ap.VocabTermNumbers AS [it!2!tId], vtn.VocabSystemsNumbersId AS [it!2!bsId], vs.Name AS [it!2!bs] FROM AnnotationPin ap INNER JOIN ImageContent ic ON ap.ImageContentId = ic.Id INNER JOIN VocabTermNumbers vtn ON ap.VocabTermNumbers = vtn.TermNumber INNER JOIN VocabSystems vs ON vs.SystemNumber = vtn.VocabSystemsNumbersId LEFT JOIN AtlasOrientImage aoi1 ON aoi1.ContentDescriptorId = ic.ContentDescriptorId WHERE ImageContentId = @iImageContentId ) as A ORDER BY [root!1!aoi],[it!2!pId] FOR XML Explicit) INSERT INTO TempXMLStore SELECT @iImageContentId, @xmlData FETCH NEXT FROM @cGetImageContentId INTO @iImageContentId END END GO