DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10325
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[LicenseType] nvarchar(max),[GenerationTime] int,[ApplicationTime] int,[ActivationKey] int
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; IF object_id(''tempdb.dbo.#templicenses'') IS NOT null DROP TABLE #templicenses
CREATE TABLE #templicenses(licxml varchar(max),rkey int)
BEGIN TRY
INSERT INTO #templicenses
SELECT CONVERT(VARCHAR(MAX),licxml),rkey from SimLicenses WITH (NOLOCK) WHERE datalength(licxml) > 0
UPDATE #templicenses
SET licxml = SUBSTRING(licxml,CHARINDEX(''<GenerationDate'',licxml),CHARINDEX(''<LACustomers'',licxml)-CHARINDEX(''<GenerationDate'',licxml))+SUBSTRING(licxml,CHARINDEX(''<LAServerHistory'',licxml),Len(''</LAServerHistory>'') + CHARINDEX(''</LAServerHistory>'',licxml)-CHARINDEX(''<LAServerHistory'',licxml))
DELETE FROM #templicenses WHERE CHARINDEX(''<GenerationDate'',licxml) = 0 OR CHARINDEX(''<LAServerHistory'',licxml) = 0
SELECT TOP 10
        csInfo.n.value(''@LicenseType'',''varchar(64)'') AS LicenseType,
        DATEDIFF(SECOND,{d ''1970-01-01''}, csInfo1.n.value(''@Date'',''varchar(64)'')) AS GenerationTime,
        l.licupdatetime AS ApplicationTime,t.rkey AS ActivationKey
FROM (SELECT convert(xml,licxml) as licxml,rkey from #templicenses) t
CROSS Apply t.licxml.nodes(''/LAServerHistory'') AS csInfo(n)
CROSS Apply t.licxml.nodes(''/GenerationDate'') AS csInfo1(n)
INNER JOIN (SELECT max(licupdatetime) AS licupdatetime, rkey FROM LicCsl WITH (NOLOCK) GROUP BY rkey) l ON t.rkey = l.rkey
ORDER BY l.licupdatetime DESC
END TRY
BEGIN CATCH
END CATCH
DROP TABLE #templicenses ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [LicenseType] AS '@LicenseType',[GenerationTime] AS '@GenerationTime',[ApplicationTime] AS '@ApplicationTime',[ActivationKey] AS '@ActivationKey' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10325'), type
)
  FOR XML PATH ('SurveyResults') 
)

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')) 
