Query ReportServer Catalog for schema info of RDL reports

Mark 26 Reputation points
2022-06-27T08:13:13.9+00:00

I'm trying to get a list of all SSRS reports in the catalog and list their names and the xmlns attribute on the report element. I seem to be able to get the first attribute "MustUnderstand" and it's value, but can't seem to get the xmlns attribute and value.

Any help would be greatly appreciated.

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
3,066 questions
{count} votes

Accepted answer
  1. Isabellaz-1451 3,616 Reputation points
    2022-06-28T09:37:42.4+00:00

    Hi @Mark

    You can use below tsql query statement :

     ;with CTE as (SELECT  
       [Path]  
     , CASE [Type]  
         WHEN 2 THEN 'Report'  
         WHEN 5 THEN 'Data Source'      
       END AS TypeName  
     , content = CAST(CAST(content AS varbinary(max)) AS xml)  
     , [Description]  
     FROM ReportServer.dbo.[Catalog] CTG  
     WHERE  
       [Type] IN (2, 5))  
       SELECT  [Path],TypeName,[xmlns] = content.value('namespace-uri((/*:Report)[1])','nvarchar(max)') from CTE  
    

    215731-image.png

    Best Regards,
    Isabella


1 additional answer

Sort by: Most helpful
  1. Isabellaz-1451 3,616 Reputation points
    2022-06-28T01:44:24.913+00:00

    Hi @Mark

    You can use this tsql query statement to get the content of the report,then you can get the xmlns attribute

    SELECT  
      [Path]  
    , CASE [Type]  
        WHEN 2 THEN 'Report'  
        WHEN 5 THEN 'Data Source'      
      END AS TypeName  
    , CAST(CAST(content AS varbinary(max)) AS xml)  
    , [Description]  
    FROM ReportServer.dbo.[Catalog] CTG  
    WHERE  
      [Type] IN (2, 5);  
    

    215476-image.png

    215551-image.png

    I refer to this thread:https://www.mssqltips.com/sqlservertip/6001/ssrs-reportserver-database-overview-and-queries/

    Best Regards,
    Isabella


    If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.