Is there a way to get the original XML code from a deployed report ssrs report to the reporting server?

Question

I'm attempting to create backups for all of our deployed reports on the reporting server, being I don't have many permissions this is proving difficulty.

The first thing I tried was created a subscription with XML FILE and Report Data and had it exported to a folder, when I try to open the file once it's saved it's only a portion of what the xml was in ssrs.

The second attempt was getting the xml directly from the sql server which is how the layout needs to be using the below query

    Select 
    Name as ReportName
    ,CONVERT(XML,CONVERT(VARBINARY(MAX),Content)) AS ReportContent 
    from Catalog
    Where Name ='report_name' 

The only problem is I haven't been able to find a way to autosave the xml portions into a separate file we can use for backup as we are wanting to backup our reports every month.

Is there a way to save all of our reports on the server this way?

(Still fairly new to Overflow so please let me know if more information is needed)

Thank You

Answer 1

Courtest of bretstateham.com (I've just tested and it works):

WITH ItemContentBinaries AS
(
  SELECT
     ItemID,Name,[Type]
    ,CASE Type
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
  FROM ReportServer.dbo.Catalog
  WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists..
ItemContentNoBOM AS(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
       ELSE
         Content
     END AS Content
  FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
 FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
     ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)