You use this action to combine two data sets by specifying up to four column pairs. Similar to standard ANSI SQL joins, this join type can be defined as an inner or outer join.
The properties for this action are listed in the following table:
Property | Data Type | Access | Use |
ParentSource | String | In and out | The path to the parent XML document. |
ChildSource | String | In and out | The path to the child XML document. |
JoinType | String | In and out | Defines how the join is performed according to ANSI SQL rules. The type can be Inner or Outer. |
Parent Column 1-4 | String | In and out | The column in the parent source used for the join. |
Child Column 1-4 | String | In and out | The column in the child source used for the join. |
Output | XML | In and out | The XML output with the joined data set. |
Success | Boolean | Out | Indicates whether the action succeeded or failed. If it failed, errors are displayed in the server trace log. |
You want to join two queries by the category ID and return the combined data set. The parent source is a Northwind database query Select * From [Sales by Category]. The child source is a Northwind query Select CategoryID, Description From Categories.
To join two queries, do the following:
Create two SQL query templates that use Northwind as a data source and the two fixed queries mentioned above.
Create a new transaction.
Add a transaction property named XMLout and specify this property as an output of the transaction.
Add two SQL Query actions to the transaction.
Specify the query template for the first (parent source) action to the parent source template you created earlier. Specify the query template for the second (child source) action to the child source template you created earlier.
Add a new sequence to the transaction, and fill the sequence with a Joiner action and configure it.
Map the output of the Joineraction to the XMLout transaction property.
The output of the transaction is the joined data set.