--- Please follow the below comments to insert SQL statements.
-- Client version query
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 153
DECLARE @surveyXML NVARCHAR(MAX)

--------- BEGIN SURVEY QUERY ---------
IF OBJECT_ID('tempdb.dbo.#PolicyCopy') IS NOT NULL DROP TABLE #PolicyCopy
CREATE TABLE #PolicyCopy (id INT, archGroupId INT, name NVARCHAR(64), type INT, isSnapCopy INT, isActive INT, sourceCopyId INT, flags INT, extendedFlags INT, dedupeFlags INT)

IF COL_LENGTH('archGroupCopy', 'extendedFlags') IS NULL
	INSERT INTO #PolicyCopy SELECT id, archGroupId, name, type, isSnapCopy, isActive, sourceCopyId, flags, 0, dedupeFlags FROM archGroupCopy WHERE type IN (1, 2)
ELSE
	EXEC sp_executesql N'INSERT INTO #PolicyCopy SELECT id, archGroupId, name, type, isSnapCopy, isActive, sourceCopyId, flags, extendedFlags, dedupeFlags FROM archGroupCopy WHERE type IN (1, 2)'


DECLARE @spCopyXML XML = (
	SELECT
		AG.id AS '@policyId',
		AG.name AS '@policyName',
		AG.flags AS '@policyFlags',
		AGC.id AS '@copyId',
		AGC.name AS '@copyName',
		AGC.type AS '@copyType',
		CASE WHEN AGC.id IN (AG.defaultCopy, AG.defaultSnapCopy) THEN 1 ELSE 0 END AS '@isDefaultCopy',
		AGC.isSnapCopy AS '@isSnapCopy',
		AGC.isActive AS '@isActiveCopy',
		AGC.sourceCopyId AS '@sourceCopyId',
		AGC.flags AS '@copyFlags',
		AGC.extendedFlags AS '@extendedFlags',
		AGC.dedupeFlags AS '@dedupeFlags',
		AR.retentionDays AS '@retentionDays',
		AR.archiverRetDays AS '@archiverRetDays',
		AR.fullCycles AS '@retentionCycles',
		AR.flags AS '@agingFlags',
		ISNULL(ARE.extRretentionRules, 0) AS '@extRretentionRules',
		ISNULL(L.AliasName, 'N/A') AS '@defaultLibrary',
		ISNULL(L.LibraryId, 0) AS '@defaultLibraryid'		
	FROM	
		archGroup AG WITH (NOLOCK)
		INNER JOIN #PolicyCopy AGC WITH (NOLOCK) ON AGC.archGroupId = AG.id 
		LEFT OUTER JOIN archAgingRule AR WITH (NOLOCK) ON AR.copyId = AGC.id
		LEFT OUTER JOIN (
			SELECT copyId, SUM(retentionRule) AS extRretentionRules
			FROM archAgingRuleExtended WITH (NOLOCK)
			GROUP BY copyId) ARE ON ARE.copyId = AGC.id
		LEFT OUTER JOIN (
			SELECT DPT.CopyId, MAX(MPL.LibraryId) AS LibraryId
			FROM MMDataPath DPT WITH (NOLOCK)
				INNER JOIN MMDrivePool DPL WITH (NOLOCK) ON DPL.DrivePoolId = DPT.DrivePoolId
				INNER JOIN MMMasterPool MPL WITH (NOLOCK) ON MPL.MasterPoolId = DPL.MasterPoolId
			WHERE (DPT.Flag & 1) = 1
			GROUP BY DPT.CopyId
			) LIB ON LIB.CopyId = AGC.id
		LEFT OUTER JOIN MMLibrary L WITH (NOLOCK) ON L.LibraryId = LIB.LibraryId
	WHERE	
		AG.type IN (1, 2) AND AGC.type IN (1, 2)

FOR XML PATH ('Copy')
-- Replace <<RESULTS_NAME>> with specific name
)

IF @spCopyXML IS NOT NULL
BEGIN
	declare @selCopyCriteriaXML xml	
	SET @selCopyCriteriaXML = (
		SELECT copyId AS '@copyId', 
			   A.type AS '@type', 
			   numPeriod AS '@numPeriod',
			   dayNumber AS '@dayNumber',
			   AGC.flags& (4096|8192) AS '@copyflags'
		FROM archSelectiveCopy A WITH (NOLOCK)
		inner join archGroupCopy AGC WITH (NOLOCK) on A.copyId=AGC.id
		INNER JOIN archGroup AG WITH (NOLOCK) ON AGC.archGroupId = AG.id
		WHERE AG.type IN (1, 2) AND AGC.type IN (1, 2)

		FOR XML PATH('ArchSelectiveCopy')
	-- Replace <<RESULTS_NAME>> with specific name
	)

	set @spCopyXML.modify('insert (sql:variable("@selCopyCriteriaXML")) before (/Copy)[1]')
END

SET @surveyXML = CAST (@spCopyXML AS nvarchar(max) )

--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)
SET NOCOUNT OFF

--------- END - GENERATED CODE ---------