Receiving a Process BOD and sending an Acknowledge BOD

The stored procedure must accept an XML as input and provide XML as output. Both are a single DataArea.

The stored procedure can contain this code:

BEGIN
  -- Prevent extra result sets from interfering with SELECT statements
  SET NOCOUNT ON;

  -- Parse XML
  DECLARE @parsedXmlData int;
  EXEC sp_xml_preparedocument @parsedXmlData OUTPUT, @xmlData;

  -- Insert order
  INSERT INTO Orders (orderNumber, orderStatus, orderDateTime, customer)
    SELECT orderNumber, orderStatus, orderDateTime, customer
    FROM OPENXML (@parsedXmlData, '/DataArea/MySalesOrder', 2)
    WITH (orderNumber   nvarchar(20) 'orderID',
          orderStatus   nvarchar(20) 'status',
          orderDateTime datetime     'orderDateTime',
          customer      nvarchar(20) 'customer');

  -- Insert lines
  INSERT INTO orderLines (orderNumber, lineNumber, item, quantity, price)
    SELECT orderNumber, lineNumber, item, quantity, price
    FROM OPENXML (@parsedXmlData, '/DataArea/MySalesOrder/MySalesOrderLine', 2)
    WITH (orderNumber nvarchar(20) '../orderID',
          lineNumber  int          'lineNumber',
          item        nvarchar(20) 'item',
          quantity    int          'quantity',
          price       decimal      'price');

  -- Get output XML for Acknowledge
  DECLARE @docId nvarchar(20);
  SET @docId =
    (SELECT orderNumber
     FROM   OPENXML (@parsedXmlData, '/DataArea/MySalesOrder', 2)
     WITH   (orderNumber   nvarchar(20) 'orderID'));
  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 = @docId
  FOR XML AUTO, ELEMENTS, root('DataArea');
END

In this case, the document is not changed during insert; for example, no ID is generated and no default values are used. Therefore, you maybe can get the Acknowledge DataArea in a more efficient way, using the MySalesOrder instance from the DataArea that is input for the stored procedure.

In ION, this statement is defined, assuming the name of the stored procedure is 'ProcessOrders':

EXEC ProcessOrders @xmlData='[Data]';

The [Data] token is a placeholder for the incoming data. At runtime this is replaced with the DataArea XML of the incoming BOD.