Tutorial: Using XSLT SQL DB Lookups

This tutorial shows you how to use the SQL DB Lookup feature with XSLT translators in IWS.

To start, create a common XSLT Translation solution.

1. Launch IWS and login to your box.
2. In the Start Page window go to the Templates tab and choose Transform and Generic Load.
3. Enter a unique name for your solution and click OK.
   
4. Set XML file as Source format and EagleML-2-0 GenericSMF (under SMF section) as Destination format in the following window. 
   Note: the transformation should be xml2xml if you want to test XSLT translator.

5. Copy the following xml imitation and paste it to the Raw Data tab of the Source format.

   <n>
   <r></r>
   </n>

6. Click OK.
   IWS creates all necessary files and the main workflow worksheet comes to view.

7. Drop an SQL DB Lookup shape onto the worksheet.
   
8. You can now make up a data query. To do that, edit the OCI and OLEDB lines of the Lookup grid. 
9. Fill both lines if you are not sure which database type is used or if you want to make (follow appropriate expression format in each case).
   Let's try a simple SELECT for Oracle DB:
   select * from rulesdbo.price_status where security_alias=2953 and effective_date=:1
   Note: the expression is used without quotes.
   Note: to call variables use ":" 
   in_par_1 is referred as :1
   in_par_2 is referred as :2
   in_par_3 is referred as :3
   .. etc
10. If you need more variables, add them manually: in_par_6 and so on.
11. In this tutorial, the expression effective_date=:1 is the condition to filter data with effective_date matching the in_par_1 value.
    
12. Now, specify the data you are going to use in the Destination grid:
    
13. Now you can create data mapping by drag-and-dropping links between rows or edit destination cells code:
    ticker = count(:DBLookup_SQL.row:)
    cusip = test
    cusipPay = :DBLookup_SQL.row:
    cusipRecieve = :DBLookup_SQL.row/SECURITY_ALIAS:
    isin = :DBLookup_SQL.row/EFFECTIVE_DATE:
    sedol = :DBLookup_SQL.row[0]/EFFECTIVE_DATE:
    reuters = :DBLookup_SQL.row/SRC_INTFC_INST:
    bloombergId = :DBLookup_SQL.row[0]/SRC_INTFC_INST:
    sicovmId = :DBLookup_SQL.row[1]/SRC_INTFC_INST:
14. If you use an element on a specific position of the row, you should define its number manually.
    :DBLookup_SQL.row[1]/SRC_INTFC_INST:
    
    
15. Save all files and publish the solution to push it into your Eagle environment. 
    A stream with the name matching the solution name will be created.
16. Open the Message Center Editor, Message Center Streams section to find it.
    
17. Test it by sending a file into the stream.
    
18. Check the result in Message Center Console.