How Do I Link Independent Sources to Order/Lines Structure? (Magic xpi 4.9)
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:
-
Drag a Data Mapper utility to the flow area.
-
From the Toolbox's Mapper Schemas section, drag a Flat File type and a Database type into the Source area of the Data Mapper window.
-
Define the Flat File and Database Sources according to the given Flat File and table structures.
-
Go to the database Source properties and start the wizard. Select the 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.
