April 16, 2018 at 9:55 am
I am not good at working with XML
The one below works fine..
What I am looking for is a way to list the DATASET as well ( That way I can see the SQL used )
USE ReportServer
GO
WITH XMLNAMESPACES( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' )
Select * FROM
(
SELECT ReportName = name
,DataSourceName1 = x.value('(@Name)[1]', 'VARCHAR(250)')
,DataSourceName2 = x.value('(@Name)[2]', 'VARCHAR(250)')
,DataProvider = x.value('(ConnectionProperties/DataProvider)[1]','VARCHAR(250)')
,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
FROM ( SELECT C.Name,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 )
)abc
WHERE
ConnectionString IS NOT NULL
April 16, 2018 at 10:06 am
Sorry, I think I am OK, Managed to make it work....
WITH XMLNAMESPACES( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' )
Select * FROM
(
SELECT ReportName = name
-- ,DataSourceName1 = x.value('(@Name)[1]', 'VARCHAR(250)')
--,DataSourceName2 = x.value('(@Name)[2]', 'VARCHAR(250)')
-- ,DataProvider = x.value('(ConnectionProperties/DataProvider)[1]','VARCHAR(250)')
-- ,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
,Query = x.value('(DataSet/Query/CommandText)[1]','VARCHAR(1000)')
FROM ( SELECT C.Name,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/DataSets') r ( x )
)abc
April 16, 2018 at 10:20 am
No way one can help without some data, you have been around long enough to know that😉
😎
Further, you can make the query much more efficient by using the text() function, it prevents the reconstruction of the XML for the output, can almost half the cost of retrieving node values.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply