Get Report as XML

Applicability:

SQL Server 2000: Not Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

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