Reading from the database

In MySQL, you cannot convert a query to xml data from a SELECT statement. Therefore, you must perform the conversion through a stored function. This function must have a return parameter of type varchar:

Helper functions/procedures:
-- this procedure adds a tag to parameter result

PROCEDURE `addStartTag`(INOUT result blob, tag varchar(15))
BEGIN
    set result = concat(result,"<", tag, ">" );
END
 
-- this procedure adds a value to the parameter result

PROCEDURE `addValue`(INOUT result blob, tagValue blob)
BEGIN
    set result = concat(result, tagValue);
END

 
-- this procedure adds an end tag to the parameter result

PROCEDURE `addEndTag`(inout result blob, tag varchar(15))
BEGIN
    set result = concat(result, "</", tag, ">");
END
 
-- this procedure add a start tag, its value and the end tag to parameter result
-- example <tag>tagvalue</tag>

PROCEDURE `addTag`(INOUT result blob, tagName varchar(15), tagValue varchar(50))
BEGIN
    declare startTag varchar(20) default "";
    declare endTag varchar(20) default "";

    call addStartTag(result, tagName);
    call addValue(result, tagValue);
    call addEndTag(result, tagName);

END
 
The read function:
 -- this function reads a sales order and its line from the database and puts the
-- result set into a xml string with a root tag 'DataArea' and per line a MySalesOrder tag

FUNCTION `readSalesOrder`() RETURNS varchar(10000) CHARSET latin1
begin

    declare done int default 0;
    declare orderID int;
    declare orderStatus varchar(10);
    declare lineNumber int;
    declare item varchar(30);
    declare quantity decimal(10,0);
    declare price decimal(10,2);
    declare result varchar(10000) default "";

    -- create cursor to loop over the query
    declare cur1 cursor for
	  SELECT o.orderNumber as orderID, o.orderStatus, line.lineNumber, line.item,
           line.quantity, line.price
    FROM	Orders as o, OrderLine as line
    WHERE o.orderNumber = line.orderNumber;

    --  set the 'done' variable after the cursor declaration
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    open cur1;

    --  set the root tag
    call addStartTag(result, "DataArea");
    set result = ("<DataArea>");

    fetch cur1 into orderID, orderStatus, lineNumber, item, quantity, price;
    while done = 0 do

        -- set tag row
        call addStartTag(result, "MySalesOrder");

        -- set the query results into their tag's
        call addTag(result, "orderID", orderID);
        call addTag(result, "orderStatus", orderStatus);
        call addTag(result, "lineNumber", lineNumber);
        call addTag(result, "item", item);
        call addTag(result, "quantity", quantity);
        call addTag(result, "price", price);


        -- set end tag row
        call addEndTag(result, "MySalesOrder");

        fetch cur1 into orderID, orderStatus, lineNumber, item, quantity, price;
    end while;

    -- set end tag root
    call addEndTag(result, "DataArea");

    -- close the cursor
    close cur1;

 RETURN result;
End

This is the Result xml:

<?xml version="1.0" encoding="utf-16"?>
<Messages>
  <SyncMySalesOrder>
    <ApplicationArea>
      <Sender>
        <LogicalID>infor.database.connectiontest</LogicalID>
        <ConfirmationCode>OnError</ConfirmationCode>
        <ComponentID>External</ComponentID>
      </Sender>
      <CreationDateTime>2011-07-08T06:01:04.100Z</CreationDateTime>
      <BODID>infor.database.connectiontest:1310104864100:0</BODID>
    </ApplicationArea>
    <DataArea>
      <Sync>
        <TenantID>infor.test.connection</TenantID>
        <AccountingEntityID />
        <LocationID />
        <ActionCriteria>
          <ActionExpression actionCode="Replace" />
        </ActionCriteria>
      </Sync>
      <MySalesOrder>
        <orderID>1</orderID>
        <orderStatus>new</orderStatus>
        <lineNumber>1</lineNumber>
        <item>VW Polo</item>
        <quantity>1</quantity>
        <price>10000.00</price>
      </MySalesOrder>
      <MySalesOrder>
        <orderID>2</orderID>
        <orderStatus>in process</orderStatus>
        <lineNumber>1</lineNumber>
        <item>hubcap</item>
        <quantity>4</quantity>
        <price>100.00</price>
      </MySalesOrder>
      <MySalesOrder>
        <orderID>2</orderID>
        <orderStatus>in process</orderStatus>
        <lineNumber>2</lineNumber>
        <item>tire</item>
        <quantity>4</quantity>
        <price>400.00</price>
      </MySalesOrder>
      <MySalesOrder>
        <orderID>3</orderID>
        <orderStatus>completed</orderStatus>
        <lineNumber>1</lineNumber>
        <item>battery</item>
        <quantity>1</quantity>
        <price>90.00</price>
      </MySalesOrder>
    </DataArea>
  </SyncMySalesOrder>
</Messages>