Use the Lookup Activity to retrieve rows of data from a selected database endpoint and use values from those rows to replace or add values to the current recurring element, based on an exact or approximate match of values from the current recurring element that is used as a key to perform the lookup.
To use the fuzzy lookup capability, you must use the Local Staging Database on the Integration Appliance. For non-fuzzy lookups, you can use any database endpoint that Studio supports.
Lookup activity requires a parameterized SQL query, an XPath query that indicates the recurring element, and an XPath query that indicates which value to use as the key for the lookup. A set of lookup values specifies how column values from the database result set should be mapped to elements or attributes of the current context node.
To complete a fuzzy lookup and approximately match the key, the Lookup activity uses the edit distance algorithm.
For example:
FirstName | LastName |
---|---|
Tony | Stark |
Bruno | Star |
Georgia | States |
Howard | Stark |
Issue the following query: select * from Names where Col_LastName='Stark'
In this example, the key_length = length (“Stark”) = 5
For comparing against the threshold, use the value (1 – ( edit_distance / key_length) )