Reports and SSRS - 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


ċ
usp_GetReportAsXML.sql
(1k)
Andy Hughes,
Jul 25, 2012, 8:14 AM
Comments