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
|
