Report_Subreports.sql

WITH 
[content_binaries]
AS 
(
    SELECT 
	     [cat].[ItemID]
	   , [cat].[Name]
	   , [cat].[Path]
	   , [cat].[Type]
	   , [TypeDescription] = 
		  CASE [cat].[Type]
			 WHEN 2 THEN 'Report'
			 WHEN 5 THEN 'Data Source'
			 WHEN 7 THEN 'Report Part'
			 WHEN 8 THEN 'Shared Dataset'
			 ELSE 'Other'
		  END  
	   , [Content] = CONVERT(VARBINARY(MAX), [cat].[Content])
    FROM 
	   [dbo].[Catalog] AS [cat]
    WHERE 
	   1=1
	   AND [cat].[Content] IS NOT NULL
	   AND [cat].[Type] = 2 
)
,
[content_binaries_format]
AS 
(
    SELECT 
	     [bin].[ItemID]
	   , [bin].[Name]
	   , [bin].[Path]
	   , [bin].[Type]
	   , [bin].[TypeDescription]
	   , [Content] = --strip off the BOM if it exist
		  CASE
			 WHEN LEFT([bin].[Content], 3) = 0xEFBBBF THEN CONVERT(VARBINARY(MAX), SUBSTRING([bin].[Content], 4, LEN([bin].[Content])))
			 ELSE [bin].[Content]
		  END  
    FROM 
	   [content_binaries] AS [bin]
)
SELECT 
	 [cbf].[ItemID]
    , [cbf].[Name]
    , [cbf].[Path]
    , [cbf].[Type]
    , [cbf].[TypeDescription]
    , [cbf].[Content] 
    , [ContentVarchar] = CONVERT(VARCHAR(MAX), [cbf].[Content]) 
    , [ContentXML] = CONVERT(XML, [cbf].[Content]) 
FROM 
    [content_binaries_format] AS [cbf]
WHERE
    1=1
    AND CONVERT(VARCHAR(MAX), [cbf].[Content]) LIKE '%<subreport%';