A simple query

In the database connection point, you can define a statement for retrieving data from a database. If it is sufficient to read the same set of data each time again, instead of only the new or changed data, you can use a query directly in ION.

The query must provide an XML result set being the DataArea for the BOD to be published.

If multiple BODs must be published, the XML result set must contain multiple DataAreas.

See Publishing multiple BODs.

When using SQL Server, the statement is as follows:

SELECT Orders.orderNumber, Orders.orderStatus, 
       Orders.orderDateTime, Orders.customer,
       OrderLines.lineNumber, OrderLines.item, 
       OrderLines.quantity, OrderLines.price
FROM   Orders, OrderLines
WHERE  OrderLines.orderNumber = Orders.orderNumber
FOR XML AUTO, ELEMENTS, root('DataArea');

You can use a JOIN statement instead of a where clause.

Note: Ensure that your query does not return too much data at once. Otherwise the data cannot be handled in the available system memory. Returning too much data at once results in an exception. To limit the amount of data returned at once, you can for example use the SELECT TOP 20 statement. The number of objects that can be handled successfully depends on the size of the objects and the available memory in the system.

To use tag names that differ from the table or column names, you can use aliases:

SELECT MySalesOrder.orderNumber as orderID, MySalesOrder.orderStatus, 
       MySalesOrder.orderDateTime, MySalesOrder.customer,
       MySalesOrderLine.lineNumber, MySalesOrderLine.item, 
       MySalesOrderLine.quantity, MySalesOrderLine.price
FROM   Orders AS MySalesOrder, OrderLines AS MySalesOrderLine
WHERE  MySalesOrderLine.orderNumber = MySalesOrder.orderNumber
FOR XML AUTO, ELEMENTS, root('DataArea');

The name of the top-level elements inside the data area is the document name. In this example, the document name is MySalesOrder. Therefore, if the Sync verb is used, the produced BOD is SyncMySalesOrder.

In that case, the resulting BOD message is:

<?xml version='1.0' encoding='UTF-8'?>
<SyncMySalesOrder>
  <ApplicationArea>
    <Sender>
      <LogicalID>lid://infor.database.orderdb</LogicalID>
      <ConfirmationCode>OnError</ConfirmationCode>
      <ComponentID>External</ComponentID>
    </Sender>
    <CreationDateTime>2011-03-29T14:20:00Z</CreationDateTime>
  </ApplicationArea>
  <DataArea>
    <Sync>
      <TenantID>Infor</TenantID>
      <AccountingEntityID/>
      <LocationID/>
      <ActionCriteria>
        <ActionExpression actionCode="Replace"/>
      </ActionCriteria>
    </Sync>
    <MySalesOrder>
      <orderID>1</orderID>
      <orderStatus>Planned</orderStatus>
      <dateTime>2011-03-28T08:05:30Z</dateTime>
      <customer>Smith</customer>
      <MySalesOrderLine>
        <lineNumber>1</lineNumber>
        <item>Chair</item>
        <quantity>4</quantity>
        <price>25.00</price>
      </MySalesOrderLine>
      <MySalesOrderLine>
        <lineNumber>2</lineNumber>
        <item>Table</item>
        <quantity>1</quantity>
        <price>225.00</price>
      </MySalesOrderLine>
    </MySalesOrder>
    <MySalesOrder>
      <orderID>2</orderID>
      <orderStatus>Pending</orderStatus>
      <dateTime>2011-03-29T14:10:00Z</dateTime>
      <customer>Johnson</customer>
      <MySalesOrderLine>
        <lineNumber>1</lineNumber>
        <item>Laptop</item>
        <quantity>1</quantity>
        <price>600.00</price>
      </MySalesOrderLine>
    </MySalesOrder>
  </DataArea>

Note:

The ApplicationArea is always created automatically by the database connector.
The verb area, Sync in this example, is generated automatically if it is not included in the result set that is output returned by your stored procedure. If you must use specific values for AccountingEntityID, LocationID, or the actionCode attribute for example, you can provide the verb area from the stored procedure. Alternatively, you can use a Mapping activity in the document flow to map a data field from MySalesOrder to one of the elements in the verb area.
If the result set is empty, no BOD is published.

Caution:

We recommend that you publish a separate BOD for each object instance (such as a sales order).

See Publishing multiple BODs.

Including multiple object instances in a single BOD can result in failures when you send such a BOD to an application, and can give unexpected results when you use content-based routing or filtering.