;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/SystemsCenterConfigurationManager/2009/06/14/Rules', 'http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest' as p1) 

select ds.CollectionName, 
case when ds.CollectionType=2 then 'Devices'
     when ds.CollectionType=1 then 'Users'
	 end as 'TargetType'
,ds.SoftwareName 'Deployment Name in the Console'
,(LPC.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:Title)[1]', 'nvarchar(max)')) as [AppTitleInSoftwareCenter]
,(LPC.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:Version)[1]', 'nvarchar(max)')) as [AppVersionInSoftwareCenter]
,(LPC.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:ReleaseDate)[1]', 'nvarchar(max)')) as [ReleaseDateInSoftwareCenter]
,(LPC.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:Description)[1]', 'nvarchar(max)')) as [DescriptionInSoftwareCenter]
,(lpc.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:InfoUrl)[1]','nvarchar(max)')) AS [InfoURL]
,(lpc.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:InfoUrlText)[1]','nvarchar(max)')) AS [InfoURLText]
,(lpc.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:PrivacyUrl)[1]','nvarchar(max)')) AS [PrivacyURL]
,(lpc.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:Tags/p1:Tag)[1]','nvarchar(max)')) as [Tag1]
,(lpc.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:Tags/p1:Tag)[2]','nvarchar(max)')) as [Tag2]
,(lpc.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:Tags/p1:Tag)[3]','nvarchar(max)')) as [Tag3]
,(Select CategoryInstanceName from v_CategoryInfo where  CategoryInstance_UniqueID=(lpc.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:UserCategories/p1:Tag)[1]','nvarchar(max)'))) as 'UserCategory1'
,(Select CategoryInstanceName from v_CategoryInfo where  CategoryInstance_UniqueID=(lpc.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:DisplayInfo/p1:Info/p1:UserCategories/p1:Tag)[2]','nvarchar(max)'))) as 'UserCategory2'
,(LPC.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Application/p1:CustomId)[1]', 'nvarchar(max)')) as [Optional Reference]
,(lpc.SDMPackageDigest.value('(/p1:AppMgmtDigest/p1:Resources/p1:Icon/p1:Data)[1]', 'nvarchar(max)')) AS [Icon]
from v_DeploymentSummary ds
join dbo.fn_ListApplicationCIs(1033) LPC on lpc.ModelID=ds.ModelID and lpc.IsLatest=1
where ds.DeploymentIntent=2
and ds.FeatureType=1