The Reporting Services Catalog table in the ReportServer database contains the RDL XML in the Content column, serialised to binary format.
I had an issue recently where I needed to retrieve the RDL XML from the Content column from a backup of the ReportServer database using SQL Server 2005. Here's the code that did the job:
SELECT [Name],
CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXML
FROM ReportServer_Backup.dbo.[Catalog] WITH (NOLOCK)
WHERE --get only reports, not folders or other resources
[Type] = 2
I would not recommend running this against the entire table. I've used the NOLOCK hint to avoid locking up the table, and also screened our non-reports by adding Type = 2 to the where clause.
I originally found this great little snippet by at the SQL Server and XML blog. Thanks Jacob! It came in so useful that I thought I'd write it down for later.
Tags: sql server, reporting services, database, xml