[SSRS] Get the SQL Query from your RDL via T-SQL

Coworker asked for this.

Basically we wanted to get the queries out of the RDLs on our Reporting Services server.  I saw a post originally that discussed using the tool to pull all the RDLs off of the machine, then parsing them.  Hey, waitasecond....

Immediately found the blog that showed how to use the double-convert to get back the XML.  Once I had that, I started querying the XML to get out what I wanted.  Now why am I getting NULL.....

So, after spending an hour banging my head against the XML and getting back NULL, I went to StackOverflow, whereupon a nice person named Mikael gave me the syntax I was looking for (thanks, Mikael!).  Turns out that you have to ignore the namespace, which means your syntax can get... weird (at least compared to what I normally use).

Overall, though, pleased how it turned out. Please let me know if you have any questions, comments or if there's more stuff I should show.  DataSource coming in a couple weeks; I have the code, just need to integrate it.

(Click on the "View Raw" to get the SQL in a new window.  Thanks!)

	/*
	Version 1.01 - 2013/06/27 mbourgon thebakingdba.blogspot.com
	Purpose: Look through the RDLs stored in your SSRS server (ReportServer DB), returning info like the Datasource and Query.
	This version doesn't contain the DataSource. 1.1 or 1.2 will, since it can be stored in 2 different places.
	Getting the RDL cribbed from http://markvsql.com/2009/11/find-all-deployed-ssrs-reports-that-reference-a-particular-field/
	XML query from Mikael Eriksson on StackOverflow http://stackoverflow.com/questions/17332257/sql-query-xml-while-ignoring-namespace
	(the trick is ignoring the namespace, which led me to this non-normal [for me anyway] syntax) Thanks Mikael!
	1.00 - first revision - it's alive!
	1.01 - adding ORDER BY
	*/
	;
	WITH cte
	AS (
	--gets the RDL; note the double convert.
	SELECT [path], [name] AS Report_Name,
	CONVERT(XML, CONVERT(VARBINARY(MAX), content)) AS rdl
	FROM reportserver.dbo.catalog
	)
	SELECT LEFT([Path], LEN([path]) - CHARINDEX('/',REVERSE([Path])) + 1) AS Report_Path,
	Report_Name,
	T1.N.value('@Name', 'nvarchar(128)') AS DataSetName,
	T2.N.value('(*:DataSourceName/text())[1]', 'nvarchar(128)') AS DataSourceName,
	ISNULL(T2.N.value('(*:CommandType/text())[1]', 'nvarchar(128)'), 'T-SQL') AS CommandType,
	T2.N.value('(*:CommandText/text())[1]', 'nvarchar(max)') AS CommandText
	FROM cte AS T
	CROSS APPLY T.rdl.nodes('/*:Report/*:DataSets/*:DataSet') AS T1 (N)
	CROSS APPLY T1.N.nodes('*:Query') AS T2 (N)
	ORDER BY Report_Path, Report_Name, DataSetName, DataSourceName, CommandType, CommandText

view raw SSRS_Show_RDL_Queries hosted with ❤ by GitHub