Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 XML NameSpace with Variable ?

Author  Topic 

ZMike
Posting Yak Master

110 Posts

Posted - 2013-01-29 : 22:25:40
I'm setting up some queries that will be running against a couple SSRS Servers. We have some that are 2005 and 2008.

Here is what I have

SET @SSRSReportDesigner = '''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'''

IF (@RealServerName = 'x')
SET @SSRSDefinition = '''http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'''
ELSE
SET @SSRSDefinition = '''http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'''


SET @SSRSPlatform = ';WITH XMLNAMESPACES ( DEFAULT ' + @SSRSDefinition + ',' + @SSRSReportDesigner + ' AS rd )'




After that then I have this : It prints exactly like the hard coded statement but if I switch the ;With XMLNAMESPACES with my solution it fails. Is there a good way to make it see that this ?

;WITH XMLNAMESPACES DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )

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 ;


James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-30 : 07:25:16
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;
Go to Top of Page

ZMike
Posting Yak Master

110 Posts

Posted - 2013-01-30 : 10:15:05
James K,

That works great. I modified it slightly to fit my parameters. Thank you very much !
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-30 : 10:21:05
You are very welcome - glad to be of help.

As always, be careful about using dynamic SQL, mainly because of SQL injection risk. A malicious user can send something unexpected in the @SSRSDefinition parameter and cause destructive effects on your database.
Go to Top of Page
   

- Advertisement -