Viewing the ContentXML values in SSMS's built-in XML editor helped me to set up our query.
The XML query I came up with is made up of two parts: the node query, which brings back a list of ReportName nodes and a value query which pulls the text value from each of those nodes.
Subreports can be embedded at any point within a report, so the simplest way to locate them is to use a double-slash prefix in the node query to search the entire document ('//*:Subreport/*:ReportName'). The asterisks are included to avoid problems with namespaces. The value query only needs to pull the text from within the ReportName tag, which is identified with '.'.
Here's the complete query:
-- Pull the RDL contents as varbinary(max)
WITH ItemContentBinaries AS (
SELECT
ItemID,
[Name],
[Path],
CONVERT(varbinary(max), [Content]) AS [Content]
FROM dbo.Catalog
WHERE Type IN (2, 7) -- Report or Report Part
)
-- strip off the BOM if it exists
, ItemContentNoBOM AS (
SELECT
ItemID,
[Name],
[Path],
CASE
WHEN
LEFT(Content, 3) = 0xEFBBBF
THEN
CONVERT(varbinary(max), SUBSTRING(Content, 4, LEN(Content)))
ELSE
Content
END
AS Content
FROM ItemContentBinaries
)
-- convert to XML
, ItemContentXML AS (
SELECT
ItemID,
[Name],
[Path],
CONVERT(xml, Content) AS ContentXML
FROM ItemContentNoBOM
)
-- query the xml doc for path /Subreport/ReportName
SELECT
[Name] AS ReportName,
Subreport.value('.', 'nvarchar(max)') AS SubreportName,
REPLACE([Path], [Name], Subreport.value('.', 'nvarchar(max)')) AS SubreportPath
FROM ItemContentXML
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Subreport/*:ReportName') AS Subreports(Subreport)
