ESQL Exercise -1

In a series of ESQL exercises which makes anyone to start writing ESQL very simple and efficient, here is a sample program to demonstrate the same.

The requirement is, you will get a message which will have multiple items inside a complex type called Purchases. Now you need to send the same message as output but Purchases should have only one item at a time. In other words, you need to split the items and each message will have one item.

The input message is

<?xml version="1.0" encoding="UTF-8"?>
<Invoice>
 <InvoiceNo>7</InvoiceNo>
 <InvoiceDate>2000-12-07</InvoiceDate>
 <InvoiceTime>12:40:00</InvoiceTime>
 <TillNumber>3</TillNumber>
 <Cashier StaffNo='089'>Mary</Cashier>
 <Customer>
   <FirstName>Andrew</FirstName>
   <LastName>Smith</LastName>
   <Title>Mr</Title>
   <DOB>20-01-70</DOB>
   <PhoneHome>01962818000</PhoneHome>
   <PhoneWork />
   <Billing>
     <Address>14 High Street</Address>
     <Address>Hursley Village</Address>
     <Address>Hampshire</Address>
     <PostCode>SO213JR</PostCode>
   </Billing>
 </Customer>
 <Payment>
   <CardType>Visa</CardType>
   <CardNo>4921682832258418</CardNo>
   <CardName>Mr Andrew J. Smith</CardName>
   <Valid>1200</Valid>
   <Expires>1101</Expires>
 </Payment>
 <Purchases>
   <Item>
     <Title Category='Computer' Form='Paperback' Edition='2'>The XML Companion</Title>
     <ISBN>0201674866</ISBN>
     <Author>Neil Bradley</Author>
     <Publisher>Addison-Wesley</Publisher>
     <PublishDate>October 1999</PublishDate>
     <UnitPrice>27.95</UnitPrice>
     <Quantity>2</Quantity>
   </Item>
   <Item>
     <Title Category='Computer' Form='Paperback' Edition='2'>A Complete Guide to DB2 Universal Database</Title>
     <ISBN>0201674866</ISBN>
     <Author>Neil Bradley</Author>
     <Publisher>Morgan Kaufmann Publishers</Publisher>
     <PublishDate>April 1998</PublishDate>
     <UnitPrice>42.95</UnitPrice>
     <Quantity>1</Quantity>
   </Item>
   <Item>
     <Title Category='Computer' Form='Hardcover' Edition='0'>JAVA 2 Developers Handbook</Title>
     <ISBN>0782121799</ISBN>
     <Author>Philip Heller, Simon Roberts </Author>
     <Publisher>Sybex, Inc.</Publisher>
     <PublishDate>September 1998</PublishDate>
     <UnitPrice>59.99</UnitPrice>
     <Quantity>1</Quantity>
   </Item>
 </Purchases>
 <StoreRecords/>
 <DirectMail/>
 <Error/>
</Invoice>

and the above message contains, three items under Purchases.

<Purchases>
   <Item>
     <Title Category='Computer' Form='Paperback' Edition='2'>The XML Companion</Title>
     <ISBN>0201674866</ISBN>
     <Author>Neil Bradley</Author>
     <Publisher>Addison-Wesley</Publisher>
     <PublishDate>October 1999</PublishDate>
     <UnitPrice>27.95</UnitPrice>
     <Quantity>2</Quantity>
   </Item>
   <Item>
     <Title Category='Computer' Form='Paperback' Edition='2'>A Complete Guide to DB2 Universal Database</Title>
     <ISBN>0201674866</ISBN>
     <Author>Neil Bradley</Author>
     <Publisher>Morgan Kaufmann Publishers</Publisher>
     <PublishDate>April 1998</PublishDate>
     <UnitPrice>42.95</UnitPrice>
     <Quantity>1</Quantity>
   </Item>
   <Item>
     <Title Category='Computer' Form='Hardcover' Edition='0'>JAVA 2 Developers Handbook</Title>
     <ISBN>0782121799</ISBN>
     <Author>Philip Heller, Simon Roberts </Author>
     <Publisher>Sybex, Inc.</Publisher>
     <PublishDate>September 1998</PublishDate>
     <UnitPrice>59.99</UnitPrice>
     <Quantity>1</Quantity>
   </Item>
 </Purchases>

So, there should be three output messages each containing one item and the message structure should be same as Input. Note the Purchases should be after Payment and the order should not be altered.

The below code ESQL does the splitting and sends the output in the desired format.

BROKER SCHEMA com.vaithu.iib
CREATE COMPUTE MODULE ESQLGuru_Compute
  CREATE FUNCTION Main() RETURNS BOOLEAN
  BEGIN
    
    DECLARE ipRef REFERENCE TO InputRoot.[<].[<];
    DECLARE opRef REFERENCE TO ipRef;
    
    FOR I AS ipRef.Purchases.[] DO
      
      CALL CopyEntireMessage();
      MOVE opRef TO OutputRoot.[<].[<].{'Purchases'};
      MOVE opRef PREVIOUSSIBLING;
      SET OutputRoot.[<].[<].{'Purchases'} = NULL;
      CREATE NEXTSIBLING OF opRef AS opRef NAME 'Purchases';
      SET opRef.{FIELDNAME(I)} = I;
      PROPAGATE;
      
    END FOR;
    
    RETURN FALSE;
  END;

  CREATE PROCEDURE CopyMessageHeaders() BEGIN
    DECLARE I INTEGER 1;
    DECLARE J INTEGER;
    SET J = CARDINALITY(InputRoot.*[]);
    WHILE I < J DO
      SET OutputRoot.*[I] = InputRoot.*[I];
      SET I = I + 1;
    END WHILE;
  END;

  CREATE PROCEDURE CopyEntireMessage() BEGIN
    SET OutputRoot = InputRoot;
  END;
END MODULE;

If you are looking for ESQL training, support and optimization tricks please connect with us at support@vaithu.com/WhatsApp @ +1 6123058684.