Tutorial: Using XSLT SQL DB Lookups

Owned by Natalie Gesin (Deactivated)
Last updated: Aug 01, 2024 by Pamela Totas (Deactivated)
3 min read

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>

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

  6. Drop anĀ SQL DB LookupĀ shape onto the worksheet.

  7. You can now make up a data query. To do that,Ā edit the OCI and OLEDB lines of the LookupĀ grid.Ā 

  8. 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

  9. If you need more variables, add them manually:Ā in_par_6 and so on.

  10. In this tutorial, the expression effective_date=:1 is the condition to filter data with effective_date matching the in_par_1 value.

    DB Lookup SQL

  11. Now, specify the data you are going to use in theĀ DestinationĀ grid:

    Destination grid example

  12. 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:

  13. 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:


  14. 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.

  15. Open the Message Center Editor, Message Center Streams section to find it.

    Message Center Editor, Message Center Streams section

  16. Test it by sending a file into the stream.

    Example of Sending file into Stream

  17. Check the result in Message Center Console.

    Message Center Console

Ā