Are you constructing the entire query as dynamic SQL and then executing it? Assuming you are, it should work - I just tried it on a random report on my report server. If you are not doing that, how are you injecting the variable into the query string?
In any case, if it will help, this is the code I tested - it is copied from what you posted, except I removed the conditionals in the @SSRSDefinition:DECLARE @SSRSDefinition NVARCHAR(256);
SET @SSRSDefinition = '''http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'''
DECLARE @sql NVARCHAR(MAX);
SET @sql = '
;WITH XMLNAMESPACES (DEFAULT ' + @SSRSDefinition +')
SELECT ReportID = ItemID
, ReportName = Name
, ReportPath = Path
, DataSourceName = x.value(''(@Name)[1]'', ''VARCHAR(250)'')
, DataProvider = x.value(''(ConnectionProperties/DataProvider)[1]'',''VARCHAR(250)'')
, ConnectionString = x.value(''(ConnectionProperties/ConnectString)[1]'',''VARCHAR(250)'')
--INTO tReportDataSources
FROM (
SELECT C.Name
, C.ItemID
, C.Path
, CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
FROM ReportServer.dbo.Catalog C
WHERE C.Content IS NOT NULL
AND C.Type = 2
) a
CROSS APPLY reportXML.nodes(''/Report/DataSources/DataSource'') r ( x )
ORDER BY Name ;';
exec sp_executesql @sql;
XML NameSpace with Variable ?
Posted - 01/29/2013 : 22:25:40
