Use the result of an XQuery value() query in another XQuery value() query in SQL Server

Question

I'm trying to query the SSRS .rdl files which are in xml format. Here is an example of the relevant parts of the xml:

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
  <DataSources>
    <DataSource Name="DataSource1">
      <DataSourceReference>DataSourceReference1</DataSourceReference>
    </DataSource>
    <DataSource Name="DataSource2">
      <DataSourceReference>DataSourceReference2</DataSourceReference>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>SELECT a from b</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="DataSet2">
      <Query>
        <DataSourceName>DataSource2</DataSourceName>
        <CommandText>SELECT c from d</CommandText>
      </Query>
    </DataSet>
  </DataSets>
...

And here is my SQL query so far:

IF OBJECT_ID('tempdb..#catalogtemp') IS NOT NULL DROP TABLE #catalogtemp 
GO

SELECT Path, CONVERT(XML, CONVERT(VARBINARY(MAX), Content)) XmlColumn
INTO #catalogtemp
FROM Catalog WHERE Type=2;

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' as rdl10)

SELECT Path as ReportPath,
T1.dataset.value('./@Name','nvarchar(max)') DatasetName,
T1.dataset.value('(.//rdl10:CommandText)[1]','nvarchar(max)') as DatasetQuery,
T1.dataset.value('(.//rdl10:DataSourceName)[1]','nvarchar(max)') as DataSourceName
FROM #catalogtemp
CROSS APPLY xmlColumn.nodes('//rdl10:DataSet') T1(dataset)

For the example xml above this would return:

ReportPath        DatasetName   DatasetQuery      DataSourceName
/path/to/report   DataSet1      SELECT a from b   DataSource1
/path/to/report   DataSet2      SELECT c from d   DataSource2

What I would like to do is add another column that looks up the DataSourceReference value for the DataSource node whose Name attribute matches the DataSourceName value of the DataSet. So the result of the query would look like this:

ReportPath        DatasetName   DatasetQuery      DataSourceName   DataSourceReference
/path/to/report   DataSet1      SELECT a from b   DataSource1      DataSourceReference1
/path/to/report   DataSet2      SELECT c from d   DataSource2      DataSourceReference2

I know that the following XQuery will return the DataSourceReference value for the DataSource node whose Name attribute is 'DataSource1':

T1.dataset.value('(../..//rdl10:DataSource[@Name="DataSource1"]//rdl10:DataSourceReference)[1]','nvarchar(max)')

but how do I write the query to look up the correct DataSourceName each time?

Answer 1

IMHO since <DataSources> are not included inside <DataSets> tags and viceversa, you need a subquery:

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' as rdl10)

SELECT ct.Path as ReportPath,
       T1.dataset.value('./@Name','nvarchar(max)') DatasetName,
       T1.dataset.value('(.//rdl10:CommandText)[1]','nvarchar(max)') as DatasetQuery,
       T1.dataset.value('(.//rdl10:DataSourceName)[1]','nvarchar(max)') as DataSourceName
       ,t.DSR
FROM catalogtemp ct
CROSS APPLY xmlColumn.nodes('//rdl10:DataSet') T1(dataset)
JOIN (SELECT Path,
             T2.dsrc.value('(./@Name)[1]', 'nvarchar(max)') as DSName,
             T2.dsrc.value('(.//rdl10:DataSourceReference)[1]', 'nvarchar(max)') as DSR
      FROM   catalogtemp
      CROSS APPLY xmlColumn.nodes('//rdl10:DataSource') T2(dsrc)) t
ON    t.Path = ct.Path
AND   t.DSName = T1.dataset.value('(.//rdl10:DataSourceName)[1]','nvarchar(max)')
WHERE ct.Path = 'my report'
GO

ReportPath | DatasetName | DatasetQuery    | DataSourceName | DSR                 
:--------- | :---------- | :-------------- | :------------- | :-------------------
my report  | DataSet1    | SELECT a from b | DataSource1    | DataSourceReference1
my report  | DataSet2    | SELECT c from d | DataSource2    | DataSourceReference2

dbfiddle here