How Do I Connect to Multiple Data Sources?
This article will explain how to connect to multiple different data sources at once in a template.
We will create an example of an Outer ForEach Tag loop that retrieves the Company IDs and Names from an XML data source.
These values are then used in an Inner ForEach Tag, that pulls data from a different data source to group together Product IDs and Unit Prices retrieved from a SQL data source.
The resulting output will first return the data value (Company Name) for the outer loop and then return all the related data values (Product ID, Unit Price) for the inner loop before advancing to the next data value returned by the outer loop.
Windward has another wiki article that describes how to combine data from distinct data sources of the same type.
Remember, as long as the data sources contain a common data element, data sources of different types can be combined into a single list or table.
How to Connect
- Connect to your first data source. Add your connection string/credentials, then click Connect.
2. Connect to your second data source. Add your connection string/credentials, then click Connect.
3. Create the Customers Outer ForEach Loop.
4. In the Customers Outer loop, query the XML data source for Company Names and IDs.
- Click the Wizard button, then click the Select Wizard in the dropdown.
- Select "click here to add a node", then expand the data source Customers table and select the "Customer" node.
- Select "Click here to add an order by".
- Expand the Customer table, then select "Customer ID".
- Click OK, and OK again to exit the XPath Wizard.
- Click Save Tag.
5. Then create an Orders Inner ForEach loop, and query the SQL data source using the Customer ID retrieved by the outer loop stored in a variable.
- Click on the Wizard, and then click the Select Wizard from the dropdown.
- Expand the Tables view, then expand the Order Details table.
- Drag "ProductID" and "UnitPrice" to the Columns section.
- Select "click here to add a group" under the Filter section.
- Then "click here to add a filter".
- Then "click here to select a node".
- Expand the Orders table in the new window, and select the "CustomerID" column.
- Click OK.
- Select "click here to set the value".
- Expand the Customer table in the new window, and double click the "CustomerID" column.
- Click OK to exit the SQL Select Wizard.
- Click Save Tag.
6. Generate output, and you will see the data pulled from two distinct data sources, combined into a single table.