SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 XML NameSpace with Variable ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ZMike
Posting Yak Master

110 Posts

Posted - 01/29/2013 :  22:25:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 01/30/2013 :  07:25:16  Show Profile  Reply with Quote
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 - 01/30/2013 :  10:15:05  Show Profile  Reply with Quote
James K,

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

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 01/30/2013 :  10:21:05  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000