XML to nest folder content

Question

Hi,

I have a project to update all reports on an SSRS instance and thought I would get a list of all reports into Excel so I can tick them off as I update each one. Easy I thought. I dont use it often (at all) but XML seemed to lend itself to this. I would have something like:

<Server>
  <ReportFolder>
    <ReportFolder>
      <Report>
      </Report>
      <Report>
      </Report>
    </ReportFolder>
    <Report>
    </Report>
    <Report>
    </Report>
    <Report>
    </Report>
  </ReportFolder>
  <ReportFolder>
  </ReportFolder>
</Server>

The Catalog table was my source of data, it has ItemID and ParentID so I can identify objects and their hierarchy but I cannot get the full server in XML form, I can get the contents of one folder, if I specify the folder (name or ItemID) but not the whole server. Once I take out the WHERE ItemID = 1234 line I get something like:

<Server>
  <ReportFolder>
  </ReportFolder>
  <ReportFolder>
  </ReportFolder>
  <ReportFolder>
  </ReportFolder>
    <Report>
    </Report>
    <Report>
    </Report>
    <Report>
    </Report>
    <Report>
    </Report>
    <Report>
    </Report>  
</Server>

I have tried CTE's XML AUTO, XML EXPLICIT and am now getting to feel my disassociation thus far with XML is justified!

Is there a way to get the full (up to 4 levels) of hierarchy in XML form?

Is this something that just isnt relevant to XML and I have taken a wrong turn into a dead-end?

Answer 1

So, good news and bad news. Here's the good news.

CREATE FUNCTION [dbo].[GetReportTree](@ItemId uniqueidentifier)
RETURNS XML
BEGIN RETURN 
  (SELECT   ca.ItemId AS '@ItemId',
      ca.Name AS '@Name',
      ca.Type AS '@Type',
      dbo.GetReportTree(ca.ItemId)
   FROM dbo.Catalog ca
   WHERE (@ItemId IS NULL AND ParentId IS NULL) OR ParentID=@ItemId
   FOR XML PATH('CatalogItem'), TYPE)
END

SELECT dbo.[GetReportTree](NULL)

Produces a hierarchy like this:

<CatalogItem Name="" Type="1">
  <CatalogItem Name="ScrumTest" Type="1">
    <CatalogItem Name="(Hidden) Delta Report Small" Type="2" />
    <CatalogItem Name="(Hidden) Product Burndown Chart Small" Type="2" />
    <CatalogItem Name="(Hidden) Product Cumulative Flow Small" Type="2" />
    <CatalogItem Name="(Hidden) Sprint Burndown Chart Small" Type="2" />
    <CatalogItem Name="All Product Backlog Items" Type="2" />
    <CatalogItem Name="All Sprint Backlog Items" Type="2" />
    <CatalogItem Name="All Sprints" Type="2" />
    <CatalogItem Name="Current Sprint Status" Type="2" />
    <CatalogItem Name="Delta Report" Type="2" />
    <CatalogItem Name="Engineering Reports" Type="1">
      <CatalogItem Name="(Hidden) Bug History Chart Small" Type="2" />
      <CatalogItem Name="Bug Count" Type="2" />
      <CatalogItem Name="Bug History Chart" Type="2" />
      <CatalogItem Name="Bug Priority Chart" Type="2" />
    </CatalogItem>
    <CatalogItem Name="Impediment Report" Type="2" />
    <CatalogItem Name="Product Backlog Composition" Type="2" />
    <CatalogItem Name="Product Burndown Chart" Type="2" />
    <CatalogItem Name="Product Cumulative Flow" Type="2" />
    <CatalogItem Name="Retrospective Report" Type="2" />

The bad news, is that I am not sure if you can do it without adding the "ReportTree" function into your ReportServer. So, it depends on your access to that server. You may be able to get the function to work across databases, I did not try that.