How Do I Link Independent Sources to Order/Lines Structure? (Magic xpi 3.x)
To create a logical link between two independent multi-occurrence Sources, and to extract combined data that matches the logical criteria, run through the Flat File multi-occurrence Source. For each iteration, a SELECT statement is sent to the database for the multi-occurrence Lines Source with a filter value from the flat file.
Sample Source Flat File:
Sample Database Table:
Destination XSD:
-
Create a Data Mapper step.
-
In the Source/Destination Management screen, define Flat File and Database Sources, based on the given Flat File and table structures.
-
Go to the database Source properties, start the wizard, select itemID, name and qty fields and define the WHERE to orderID=C.UserString. The result SQL statement should look similar to this one:
SELECT [OrderLines].itemID , [OrderLines].name , [OrderLines].qty FROM dbo.[OrderLines] WHERE [OrderLines].orderID=<?C.UserString?>
-
Define the Destination as XML based on the given XSD.
-
Map Flat File Source simple elements to the Destination Order compound simple elements.
-
Update the C.UserString with the OrderID in the Advanced tab of the flat file to the Order compound properties.
-
Map the database simple elements to the OrderLines compound simple elements.
This is an example of the output from this process:
For each iteration on the Flat File record, a SELECT statement is sent to the database with the WHERE clause containing the value of the orderID, which we updated in the C.UserString. The database is the one doing the filtering and we are left with the mapping of the selected records to the Destination compound.
