The Lookup Stored Procedure action treats data coming into the CData connector as input for stored procedures. The connector executes the selected stored procedure, then inserts the resulting values into an existing Arc message in the flow.

1. In the CData Arc flow, drag an arrow from the connector that will provide input to the CData connector.

2. Optionally, choose a Sample File. A sample file is recommended because it allows the connector to provide more context about the file structure and makes it easier to select input parameter values.

3. Once you have completed the configuration steps below, in the Arc flow, drag an arrow from the CData connector to the connector where you want your resulting XML or Header data to go.

Lookup Stored Procedure Configuration

Follow these steps to configure a stored procedure:

1. Select a stored procedure from the drop-down menu. The available stored procedures depend on the connector and cannot be customized from CData Arc.

2. Input Parameter: Add a Value for each input parameter. If you have uploaded a sample file, the editor displays a tree representation of the document so you can easily find the XPath you need, as shown in the following image.

   

3. Output Destination: select XPath to insert the stored procedure response value into an XML file or Header to insert the stored procedure response value into a message header.
   • XPath: When you choose XPath, the destination is in two parts. The Parent-Level XPath should already exist in the XML document. The New Node Name is the name of the node that is injected into the XML document to hold the result of the lookup. Each input parameter value has its own XML element (leaf level) in the XML element provided here.
   • Header: When you choose Header, the connector adds one header to the output message for each input parameter. The values are comma-separated for each record that is returned from the database.
4. Output Options: Stored procedure results might include result sets, return values, and/or output parameters. These two checkboxes let you fine-tune what to include in the output.
   • Check Include Output Parameters to include the output parameters.
   • Check Include Result Sets to include result sets in the output (only applicable when the Output Destination is XPath). Because multiple results sets with varying structures might be returned, result sets are injected into the input XML with an additional level of hierarchy.

5. Click Test Lookup to see what the structure of the input XML should look like.

   

6. Click Test to see what output will be generated. When you are satisfied with the test results, you can click Save Sample Data to make the data accessible to other connectors.