SSRS: Merge data from different data sources into one data set inside SSRS report using XML Connection

SSRS: Merge data from different data sources into one data set inside SSRS report using XML Connection

Introduction and Problem


Quite often we consider the task to merge data from different data sources inside SSRS report.
Usually  we are advised to use workarounds like SQL Server linked servers or SSIS which uses non ssrs datasources to extract data to be merged or SSRS lookup functions to achieve desired result outside of the report data retrieval stage.
Recent 
discussion on technet forum revealed that the technique of merging data from different SSRS data sources into one SSRS data set is still not widespread.
In previous article we considered a technique which uses external connection to merge the data. In this article we use XML Connection
 with Embedded XML document to merge the data what leaves the merge process on the current server.

Alternative solutions (workarounds)

External Data Sources

  • SQL Server Linked Server
  • T-SQL OPENQURY statement
  • SQL Server Integration Services (SSIS)

After data retrieval stage approaches

  • SSRS lookup functions

Solution

In sample report we have 2 external data sources - RemoteDataSource and RemoteDataSource2 with corresponding datasets - MainDataSet and SecondaryDataSet. To merge the data we add XML Connection - ResultDataSource to the report and fill the corresponding data set's (ResultDataSetEmbedded XML document with data obtained from the source data sets. To pass the data between data sets we use auxiliary parameters MainDataXML and SecondaryDataXML.

1. Add SecondaryDataXML calculated field to SecondaryDataset to construct secondary dataset's XML data parts in the ResultDataSet.
2. Add multi-value SecondaryDataXML parameter with default values from SecondaryDataXML field of the SecondaryDataset.
Repeat steps 1-2 for main data.
3. Add metadata definition query to the ResultDataset query text:
<Query>
<ElementPath>rows{}/row{@MainKey(Integer),@MainData(string),@SecondaryData1(Decimal),@SecondaryData2(Decimal),@SecondaryData3(Decimal)}</ElementPath>
<XmlData>
<rows>
</rows>
</XmlData>
</Query>
Here we define field names and data types. For more information please see article Element Path Syntax for XML Report Data (SSRS).
Replace static empty Embedded XML document with dynamic expression where we merge the data:
="<Query>"+
"<ElementPath>"+
"rows{}/row{"+
"@MainKey(Integer),@MainData(string),@SecondaryData1(Decimal),"+
"@SecondaryData2(Decimal),@SecondaryData3(Decimal)}"+
"</ElementPath>"+
"<XmlData>"+
"<rows>"+
Join(Parameters!MainDataXML.Value,"")+
Join(Parameters!SecondaryDataXML.Value,"")+
"</rows>"+
"</XmlData>"+
"</Query>"
Sample report execution result is depicted below: as a result we have native ability to make aggregations over merged data:

Usage and Limits

  • Tests were made with 2 external data sets (as for now there is no considerations about a restriction for the number of source datasets) with 32000 records in each (some considerations about the restriction for number of rows can be found in the following discussion) - full report execution takes several seconds on first surface pro tablet (SSRS 2014 native mode). 
  • As with previous approach each of source data sets should not be empty - you can use a technique like this to add a record to dataset.
  • If to compare to previous approach there is no automatic metadata construction (fields refresh) for result dataset.


Sample Report Definition XML

<?xml version="1.0" encoding="utf-8"?>
  <Body>
    <ReportItems>
      <Tablix Name="Tablix3">
        <TablixBody>
          <TablixColumns>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1.41667in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
          </TablixColumns>
          <TablixRows>
            <TablixRow>
              <Height>0.46875in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox21">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>Main Key</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style>
                            <TextAlign>Center</TextAlign>
                          </Style>
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox21</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox16">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>Main Data</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style>
                            <TextAlign>Center</TextAlign>
                          </Style>
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox16</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox18">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>Secondary Data1</Value>
                              <Style>
                                <Format>### ### ###</Format>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style>
                            <TextAlign>Center</TextAlign>
                          </Style>
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox18</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox22">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>Secondary Data2</Value>
                              <Style>
                                <Format>### ### ###</Format>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style>
                            <TextAlign>Center</TextAlign>
                          </Style>
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox22</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox3">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>Secondary Data3</Value>
                              <Style>
                                <Format>### ### ###</Format>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style>
                            <TextAlign>Center</TextAlign>
                          </Style>
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox3</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
            <TablixRow>
              <Height>0.61458in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox20">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>Rows Count: </Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=CountRows()</Value>
                              <Style>
                                <Format>### ### ### ###</Format>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style>
                            <TextAlign>Right</TextAlign>
                          </Style>
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox19</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>LightSteelBlue</BackgroundColor>
                        <VerticalAlign>Middle</VerticalAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox19">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>Main Data Rows:</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Count(Fields!MainKey.Value)</Value>
                              <Style>
                                <Format>### ### ### ###</Format>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style>
                            <TextAlign>Right</TextAlign>
                          </Style>
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox19</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>LightSteelBlue</BackgroundColor>
                        <VerticalAlign>Middle</VerticalAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox14">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Sum(Fields!SecondaryData1.Value)</Value>
                              <Style>
                                <Format>### ### ### ###</Format>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox14</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>LightSteelBlue</BackgroundColor>
                        <VerticalAlign>Middle</VerticalAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox15">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Sum(Fields!SecondaryData2.Value)</Value>
                              <Style>
                                <Format>### ### ### ###</Format>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox15</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>LightSteelBlue</BackgroundColor>
                        <VerticalAlign>Middle</VerticalAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox17">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Sum(Fields!SecondaryData3.Value)</Value>
                              <Style>
                                <Format>### ### ### ###</Format>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox17</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>LightSteelBlue</BackgroundColor>
                        <VerticalAlign>Middle</VerticalAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
            <TablixRow>
              <Height>0.25in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="MainKey">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!MainKey.Value</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>MainKey</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="MainData">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!MainData.Value</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>MainData</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="SecondaryData1">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!SecondaryData1.Value</Value>
                              <Style>
                                <Format>### ### ###</Format>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>SecondaryData1</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="SecondaryData2">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!SecondaryData2.Value</Value>
                              <Style>
                                <Format>### ### ###</Format>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>SecondaryData2</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="SecondaryData3">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!SecondaryData3.Value</Value>
                              <Style>
                                <Format>### ### ###</Format>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>SecondaryData3</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
          </TablixRows>
        </TablixBody>
        <TablixColumnHierarchy>
          <TablixMembers>
            <TablixMember />
            <TablixMember />
            <TablixMember />
            <TablixMember />
            <TablixMember />
          </TablixMembers>
        </TablixColumnHierarchy>
        <TablixRowHierarchy>
          <TablixMembers>
            <TablixMember>
              <KeepWithGroup>After</KeepWithGroup>
            </TablixMember>
            <TablixMember>
              <KeepWithGroup>After</KeepWithGroup>
            </TablixMember>
            <TablixMember>
              <Group Name="Details" />
            </TablixMember>
          </TablixMembers>
        </TablixRowHierarchy>
        <DataSetName>ResultDataSet</DataSetName>
        <Height>1.33333in</Height>
        <Width>5.41667in</Width>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
        </Style>
      </Tablix>
    </ReportItems>
    <Height>1.46875in</Height>
    <Style />
  </Body>
  <Width>5.41667in</Width>
  <Page>
    <LeftMargin>1in</LeftMargin>
    <RightMargin>1in</RightMargin>
    <TopMargin>1in</TopMargin>
    <BottomMargin>1in</BottomMargin>
    <Style />
  </Page>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="RemoteDataSource">
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=.;Initial Catalog=dbe_loc</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>
      <rd:SecurityType>Integrated</rd:SecurityType>
      <rd:DataSourceID>3bb7e75d-13a7-4c0a-a8b5-c4e08adc2e5e</rd:DataSourceID>
    </DataSource>
    <DataSource Name="ResultDataSource">
      <ConnectionProperties>
        <DataProvider>XML</DataProvider>
        <ConnectString />
      </ConnectionProperties>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>30574aec-4b78-49fa-93e0-6b6ad9858f39</rd:DataSourceID>
    </DataSource>
    <DataSource Name="RemoteDataSource2">
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=.;Initial Catalog=dbe_loc</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>
      <rd:SecurityType>Integrated</rd:SecurityType>
      <rd:DataSourceID>b3fed140-036f-46c3-9675-2d3d1c02b8a4</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="SecondaryDataSet">
      <Query>
        <DataSourceName>RemoteDataSource2</DataSourceName>
        <CommandText>declare @N as int = 32767;
WITH NRecs
AS (
    SELECT 1 AS SecondaryData1
        ,2*@N AS SecondaryData2
        ,4*@N AS SecondaryData3
     
    UNION ALL
     
    SELECT SecondaryData1 + 1
        ,SecondaryData2 + 1
        ,SecondaryData3 + 1
    FROM NRecs
    WHERE SecondaryData1 < @N
    )
SELECT *
FROM NRecs
OPTION (Maxrecursion 32767)</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="SecondaryDataXML">
          <Value>="<row MainKey="""" MainData="""" SecondaryData1="""+
CStr(Fields!SecondaryData1.Value)+
""" SecondaryData2="""+
CStr(Fields!SecondaryData2.Value)+
""" SecondaryData3="""+
CStr(Fields!SecondaryData3.Value)+
""" />"</Value>
        </Field>
        <Field Name="SecondaryData1">
          <DataField>SecondaryData1</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="SecondaryData2">
          <DataField>SecondaryData2</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="SecondaryData3">
          <DataField>SecondaryData3</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="MainDataSet">
      <Query>
        <DataSourceName>RemoteDataSource</DataSourceName>
        <CommandText>DECLARE @N AS INT = 11;
 
WITH NRecs
AS (
    SELECT 1 AS MainKey
        ,'main data ' + CAST(1 AS VARCHAR(MAX)) AS MainData
        ,2 * @N AS SecondaryData1
        ,4 * @N AS SecondaryData2
        ,5 * @N AS SecondaryData3
     
    UNION ALL
     
    SELECT MainKey + 1
        ,'main data ' + CAST(MainKey + 1 AS VARCHAR(MAX)) AS MainData
        ,SecondaryData1 + 1
        ,SecondaryData2 + 1
        ,SecondaryData3 + 1
    FROM NRecs
    WHERE MainKey < @N
    )
SELECT *
FROM NRecs
OPTION (MAXRECURSION 32767)</CommandText>
      </Query>
      <Fields>
        <Field Name="MainKey">
          <DataField>MainKey</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="MainData">
          <DataField>MainData</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="SecondaryData1">
          <DataField>SecondaryData1</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="SecondaryData2">
          <DataField>SecondaryData2</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="SecondaryData3">
          <DataField>SecondaryData3</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="MainDataXML">
          <Value>="<row MainKey="""+
CStr(Fields!MainKey.Value)+
""" MainData="""+
CStr(Fields!MainData.Value)+
""" SecondaryData1="""+
CStr(Fields!SecondaryData1.Value)+
""" SecondaryData2="""+
CStr(Fields!SecondaryData2.Value)+
""" SecondaryData3="""+
CStr(Fields!SecondaryData3.Value)+
""" />"</Value>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="ResultDataSet">
      <Query>
        <DataSourceName>ResultDataSource</DataSourceName>
        <CommandText>="<Query>"+
"<ElementPath>"+
"rows{}/row{"+
"@MainKey(Integer),@MainData(string),@SecondaryData1(Decimal),"+
"@SecondaryData2(Decimal),@SecondaryData3(Decimal)}"+
"</ElementPath>"+
"<XmlData>"+
"<rows>"+
Join(Parameters!MainDataXML.Value,"")+
Join(Parameters!SecondaryDataXML.Value,"")+
"</rows>"+
"</XmlData>"+
"</Query>"</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="MainKey">
          <DataField>MainKey</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="MainData">
          <DataField>MainData</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="SecondaryData1">
          <DataField>SecondaryData1</DataField>
          <rd:TypeName>System.Decimal</rd:TypeName>
        </Field>
        <Field Name="SecondaryData2">
          <DataField>SecondaryData2</DataField>
          <rd:TypeName>System.Decimal</rd:TypeName>
        </Field>
        <Field Name="SecondaryData3">
          <DataField>SecondaryData3</DataField>
          <rd:TypeName>System.Decimal</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <ReportParameters>
    <ReportParameter Name="MainDataXML">
      <DataType>String</DataType>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>MainDataSet</DataSetName>
          <ValueField>MainDataXML</ValueField>
        </DataSetReference>
      </DefaultValue>
      <Hidden>true</Hidden>
      <MultiValue>true</MultiValue>
    </ReportParameter>
    <ReportParameter Name="SecondaryDataXML">
      <DataType>String</DataType>
      <DefaultValue>
        <DataSetReference>
          <DataSetName>SecondaryDataSet</DataSetName>
          <ValueField>SecondaryDataXML</ValueField>
        </DataSetReference>
      </DefaultValue>
      <Hidden>true</Hidden>
      <MultiValue>true</MultiValue>
    </ReportParameter>
  </ReportParameters>
  <Language>en-US</Language>
  <ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>5b905a47-e0c6-4dc3-993e-655cc3ac68dd</rd:ReportID>
</Report>


Steps to Reproduce

1. Create empty text file via notepad and fill the content with Sample Report Defenition XML, change file extension to RDL
2. Open report obtained in previous step in Report Builder or SSDT-BI and point RemoteDataSource and RemoteDataSource2 to your instance of SQL Server.
Now you can launch the report in development environment or deploy it to your SSRS server.

Resources

Troubleshooting Reports: Report Performance
Managing Report ProcessingReporting Services Concepts (SSRS)
Length and Count Limitations of a Multi-Valued Text Parameters in SSRS 2008
XML Query Syntax for XML Report Data (SSRS)
XML Connection Type (SSRS)
Element Path Syntax for XML Report Data (SSRS)
Reports in SSRS with Multi Value Parameters and NULL Values

SeeAlso

SSRS: Merge data from different datasources into one dataset inside SSRS report 
SSRS: Join data from different SSRS data sources into data set