如何在SQL Server中复用XQuery value()查询结果关联SSRS RDL文件

Got it, let's solve this. You already have a query that pulls dataset details from your SSRS catalog, and now you want to map each dataset's DataSourceName to its actual DataSourceReference from the RDL's <DataSources> section. Here's how to modify your query to make that happen:

Step-by-Step Adjustment #

The key is to extract the data source mappings first, then join them with your existing dataset results. Since the <DataSources> and <DataSets> nodes are siblings in the RDL XML, we can use two separate CROSS APPLY calls to parse each section, then link them via the matching DataSourceName.

Modified SQL Query #

IF OBJECT_ID('tempdb..#catalogtemp') IS NOT NULL DROP TABLE #catalogtemp
GO
-- Load catalog data into temp table with XML parsed
SELECT 
    Path, 
    CONVERT(XML, CONVERT(VARBINARY(MAX), Content)) XmlColumn
INTO #catalogtemp
FROM Catalog
WHERE Type = 2; -- Type 2 = SSRS Report

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' as rdl10)
-- CTE to extract DataSource Name and its Reference
, DataSourceCTE AS (
    SELECT 
        Path AS ReportPath,
        ds.datasource.value('./@Name','nvarchar(max)') AS DataSourceName,
        ds.datasource.value('(./rdl10:DataSourceReference)[1]','nvarchar(max)') AS DataSourceReference
    FROM #catalogtemp
    CROSS APPLY XmlColumn.nodes('//rdl10:DataSource') ds(datasource)
)
-- Join dataset details with DataSource reference
SELECT 
    dscte.ReportPath,
    ds.dataset.value('./@Name','nvarchar(max)') AS DatasetName,
    ds.dataset.value('(.//rdl10:CommandText)[1]','nvarchar(max)') AS DatasetQuery,
    ds.dataset.value('(.//rdl10:DataSourceName)[1]','nvarchar(max)') AS DataSourceName,
    dscte.DataSourceReference
FROM #catalogtemp
CROSS APPLY XmlColumn.nodes('//rdl10:DataSet') ds(dataset)
LEFT JOIN DataSourceCTE dscte 
    ON dscte.ReportPath = #catalogtemp.Path
    AND dscte.DataSourceName = ds.dataset.value('(.//rdl10:DataSourceName)[1]','nvarchar(max)')
ORDER BY ReportPath, DatasetName;

What This Does #

1. DataSourceCTE: This common table expression parses the <DataSources> section of each RDL to get the pairing of DataSourceName (like DataSource1) and its DataSourceReference (like DataSourceReference1).
2. Main Query: We keep your original dataset parsing, then left join it with the DataSourceCTE using the report path and data source name to bring in the corresponding reference.

Expected Output for Your Sample XML #

For the RDL snippet you provided, this query would return:

This way, you get a complete view of each dataset's query, its linked data source, and that data source's reference value.

内容的提问来源于stack exchange，提问作者jhnpckr