Credits:
Author: ChillyDBA
Date: 25 Jul 2012
Description
Stored procedure to retrieve report XML using TSQL code. This is stored in the report server database in the Catalog table as varbinary.
From SQL Server 2005 onward, if the query is executed with 'Results to Grid' option, then the XML will be displayed as a clickable link which will open in a new page and display the XML in a formatted code window, otherwise it will be displayed as a single line of XML text.
A filter can be supplied containing a full or partial report name to restrict the results returned
Code
Stored Procedure:
DROP PROC usp_GetReportAsXML GO
CREATE PROC usp_GetReportAsXML @ReportName NVARCHAR(850) = NULL AS
/**************************************************** Purpose: To retreive one or many reports in XML format. Uses the Catalog table in the report server database (assumed to be called by its default name of 'ReportServer')
Author: ChillyDBA History: 25 Jul 2012 *****************************************************/
SELECT c1.Name AS ReportName, CONVERT(XML,(CONVERT(VARBINARY(MAX),c1.Content))) AS ReportXML, c2.Name AS ReportFolder FROM ReportServer.dbo.Catalog c1 (NOLOCK) INNER JOIN ReportServer.dbo.Catalog c2 (NOLOCK) ON c1.ParentID = c2.ItemID WHERE c1.Content IS NOT NULL AND c1.Name LIKE '%' + ISNULL(@ReportName, c1.Name) + '%' -- if param is null, then return all reports AND c1.[Type] = 2 -- For Report objects alone
|