In earlier releases of CData Arc the Lookup connector / Lookup action of Database connectors had a fairly simple interface to it. You can configure a single query to select a single column from a table in a database, and output the result of the lookup in a specified XPath in the XML, like so:
Simply put, if the Input file is:
<Items>
<Record>
<Name>James</Name>
</Record>
</Items>
The output file creates the result in a neighboring element:
<Items>
<Record>
<Name>James</Name>
<ID>0</ID>
</Record>
</Items>
In the 2023 release of CData Arc, a significant upgrade was provided to the Lookup action in a database. You can now select multiple columns in a single select statement, expect multiple results, and the experience is backed by an updated designer that allows you to build the query without having to hardcode table and column names:
This new format is objectively more desirable, but you will find that because the result set can now be much more complex, the output behavior changes. Now, if you test the lookup of a single Record, you’ll find that the Output destination is a parent element to the result set, not an exact match:
This allows you to select multiple columns in a single query, which would have required the use of multiple consecutive connectors in the earlier version.
How, then, is it possible to retain the old behavior?
In the Advanced->Other Settings section of the connector, there is a hidden configuration setting,
singlecolumnlookupxpathincludesleaf=true
that you can set to output the result of a single column lookup to the element of the matching name instead of outputting to a parent element:
With this set, the old behavior is observed:
