Extract T-SQL code from multiple RDL files by Arvind Shyamsundar ([email protected])

sqlFromRDL.ps1

<#  
.SYNOPSIS  
    Extract T-SQL code from multiple RDL files

.NOTES  
    Author     : Arvind Shyamsundar ([email protected])
	
.PARAMETERS
	-RootFolder: full path to the root folder which contains the RDL files
	-Recurse: whether to search sub folders or not

.LINK  
    http://blogs.msdn.com/b/arvindsh
	
.HISTORY
	2013.04.30	First version for blog
#>

param
(
	[string] 	$RootFolder,
	[bool]	$Recurse
)

if ($Recurse -eq $true)
{
	$rdlfiles = get-childitem -path $RootFolder -Filter "*.rdl" -Recurse
}
else
{
	$rdlfiles = get-childitem -path $RootFolder -Filter "*.rdl"
}

foreach ($filename in $rdlfiles)
{
	[xml]$xml =get-content $filename.FullName
	$ns = new-object Xml.XmlNamespaceManager ($xml.NameTable)
	$ns.AddNamespace("rd", "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner")
	$ns.AddNamespace("cl", "http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition")
	$ns.AddNamespace("rep", "http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition")

	$xml.SelectNodes("//rep:DataSets/rep:DataSet/rep:Query", $ns) | foreach-object {			
			if (($_.CommandType -eq "Text") -or ($_.CommandType -eq $null))
			{
				$datasetname = $_.SelectSingleNode("..", $ns).Name
				
				("-- ~~" + $filename + "; dataset: " + $datasetname + "~~")
				$_.SelectSingleNode("./rep:CommandText", $ns).InnerText.Replace("&gt;", ">").Replace("&lt;", "<").Replace("&amp;", "&").Replace("&apos;", "'").Replace("&quot;", "`"")
				"GO"
			}
		}	
}