ESQL

There are situations where we need mimic simulation for various scenario like  success, error, failure etc. We do the same by creating a mock test flow which drops files into the destination randomly and here is the ESQL code to pick those random files.

CREATE COMPUTE MODULE RandomFiles
  CREATE FUNCTION Main() RETURNS BOOLEAN
  BEGIN

    DECLARE R DECIMAL RAND(1);
    DECLARE fileName CHARACTER getRootElement(InputRoot);

    SET fileName = CASE

    WHEN R > 0.0 AND R < 0.3 THEN fileName ||'_success.txt'
    WHEN R > 0.3 AND R < 0.6 THEN fileName ||'_error.txt'
    ELSE fileName ||'_timeout.txt'
    END;

    CALL SetDestinationFileName(OutputLocalEnvironment, fileName);

    RETURN TRUE;
  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;

CREATE PROCEDURE SetDestinationFileName(IN LocalEnvironment REFERENCE,IN Name CHARACTER)
BEGIN
  SET LocalEnvironment.Destination.File.Name = Name;
END;

CREATE FUNCTION getRootElement (In ipRef REFERENCE ) RETURNS CHARACTER
BEGIN
  RETURN FIELDNAME(ipRef.*:*[<].*:*[<]);
END;

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

Every time, we either Google or go to Help to find the statement to set dynamically destination queue name, file name, file directory and web service url etc and thought of creating a library which contains all these procedures so that it would be very handy and saves lot of time. Here is a list which you can use freely and save your precious time.

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

-- COMPLETE ESQL TO Update
-- LocalEnvironment.Variables,
-- LocalEnvironment.Destination
-- LocalEnvironment.WrittenDestination
-- LocalEnvironment.Adapter
-- LocalEnvironment.Database
-- LocalEnvironment.CD and LocalEnvironment.CD.Transfer
-- LocalEnvironment.DecisionServices
-- LocalEnvironment.File
-- LocalEnvironment.FTE and LocalEnvironment.FTE.Transfer
-- LocalEnvironment.JMS
-- LocalEnvironment.Mapping
-- LocalEnvironment.ServiceRegistry
-- LocalEnvironment.SOAP
-- LocalEnvironment.TCPIP 
-- LocalEnvironment/Aggregation/Timeout'
-- MQ NODE OVVERRIDES STARTS---

CREATE PROCEDURE SetQManager (IN ref REFERENCE,IN myQManagerName CHARACTER)
BEGIN
  SET ref.Destination.MQ.DestinationData.queueManagerName = myQManagerName;
END;	 


CREATE PROCEDURE SetQName(IN ref REFERENCE,IN myQueueName CHARACTER)
BEGIN
  SET ref.Destination.MQ.DestinationData.queueName = myQueueName;
END;
---------------------------------------------------------------------------------------------
-- MQGet node - QueueName InitialBufferSize MQGMO.*

CREATE PROCEDURE SetMQGetQName (IN InputLocalEnvironment REFERENCE,IN MQGETQueue CHARACTER)
BEGIN
  SET InputLocalEnvironment.MQ.GET.QueueName = MQGETQueue;
END;

CREATE PROCEDURE SetMQGETWaitInter(IN InputLocalEnvironment REFERENCE,IN InterWaitTime CHARACTER)
BEGIN
  SET InputLocalEnvironment.MQ.GET.MQGMO.WaitInterval = InterWaitTime;
END;

CREATE PROCEDURE SetMQGETOptions(IN InputLocalEnvironment REFERENCE, IN MQGMO_ACCEPT_TRUNCATED_MSG CHARACTER)
BEGIN
  SET InputLocalEnvironment.MQ.GET.MQGMO.Options = MQGMO_ACCEPT_TRUNCATED_MSG;
END;

CREATE PROCEDURE SetMQGETBuffer(IN InputLocalEnvironment REFERENCE, IN BufferSize CHARACTER)
BEGIN
  SET InputLocalEnvironment.MQ.GET.InitialBufferSize = BufferSize;
END;
-- MQGet node Overrides ----ENDS-----
-- HTTPAsyncRequest node Ovverrides
-- Compression Overrides the Use compression property
CREATE PROCEDURE SetHTTPAsyReqeqCompression(IN OutputLocalEnvironment REFERENCE,IN gzip CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.Compression = gzip;
END;
-- To set a minimum size (in bytes) at which compression is applied, use the following override:
CREATE PROCEDURE SetHTTPAsyReqeqMinCompressionSize(IN OutputLocalEnvironment REFERENCE,IN CompressionSize CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.MinimumCompressionSize = CompressionSize;
END;
--RequestLine.RequestURI Overrides the RequestURI, which is the path after the URL and port
--SET OutputLocalEnvironment.Destination.HTTP.RequestLine.RequestURI = '/abc/def';

CREATE PROCEDURE SetHTTPAsyReqRequestURI(IN OutputLocalEnvironment REFERENCE ,IN URI CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestLine.RequestURI = URI;
END;
--RequestLine.HTTPVersion Overrides the HTTP version property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestLine.HTTPVersion = 'HTTP/1.1';
CREATE PROCEDURE SetHTTPAsyReqHTTPVersion(IN OutputLocalEnvironment REFERENCE ,IN HTTPVersion CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestLine.HTTPVersion = HTTPVersion;
END;
--RequestLine.Method Overrides the HTTP method property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestLine.Method = 'GET';

CREATE PROCEDURE SetHTTPAsyReqMethod(IN OutputLocalEnvironment REFERENCE ,IN GetorPost CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestLine.Method = GetorPost;
END;
--RequestURL Overrides the Web service URL property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestURL = 'http://ibm.com/abc/';
CREATE PROCEDURE SetHTTPAsyReqRequestURL(IN OutputLocalEnvironment REFERENCE ,IN RequestURL CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestURL = RequestURL;
END;
--SSL authentication alias Overrides the SSL authentication alias for the client-side of an HTTP connection on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.KeyAlias = 'Key1Alias';
CREATE PROCEDURE SetHTTPAsyReqKeyAlias(IN OutputLocalEnvironment REFERENCE ,IN KeyAlias CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.KeyAlias = KeyAlias;
END;
--SSLProtocol Overrides the SSLProtocol. For example:
--SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = 'TLS';
--Valid values are: SSL, SSLv3, and TLS.
CREATE PROCEDURE SetHTTPAsyReqSSLProtocol(IN OutputLocalEnvironment REFERENCE ,IN SSL0rSSLv3orTLS CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = SSL0rSSLv3orTLS;
END;
--SSLCiphers Overrides the Allowed SSL Ciphers property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.SSLCiphers =
-- 'SSL_RSA_FIPS_WITH_3DES_EDE_CBC_SHA';

CREATE PROCEDURE SetHTTPAsyReqSSLCiphers(IN OutputLocalEnvironment REFERENCE ,IN SSLCiphers CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = SSLCiphers;
END;
--UserContext You can store BLOB context data in the following location in the local environment. The HTTPAsyncResponse node can later retrieve this data.
--SET OutputLocalEnvironment.Destination.HTTP.UserContext = x'aabbccddeeff11223344556677889900';
--Data stored in the UserContext must be in BLOB format.
CREATE PROCEDURE SetHTTPAsyReqUserContext(IN OutputLocalEnvironment REFERENCE ,IN UserContext BLOB)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = UserContext;
END;
--Enable CRL checking Overrides the Enable Certificate Revocation List checking property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.EnableCRLCheck = 'true';
CREATE PROCEDURE SetHTTPAsyReqEnableCRLCheck(IN OutputLocalEnvironment REFERENCE ,IN EnableCRLCheck CHARACTER)
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = EnableCRLCheck;
END;

--ProxyConnectHeaders Specifies additional headers that are used if the outbound request is an SSL connection through a proxy. These additional headers are sent with the initial CONNECT request to the proxy. For example, you can send proxy authentication information to a proxy server when you are using SSL. You can send multiple headers but each one must be separated by a carriage return and a line feed (ASCII 0x0D 0x0A), in accordance with RFC2616; for example:
--DECLARE CRLF CHAR CAST(X'0D0A' AS CHAR CCSID 1208);
--SET OutputLocalEnvironment.Destination.HTTP.ProxyConnectHeaders =
--'Proxy-Authorization: Basic Zm5lcmJsZTpwYXNzd29yZA==' || CRLF ||
--'Proxy-Connection: Keep-Alive' || CRLF;
--This setting is used only if the request is an SSL request through a proxy server. To send proxy authentication information for a non-SSL request, specify the individual headers in the HTTPRequestHeader folder, as shown in the following example:
--SET OutputRoot.HTTPRequestHeader."Proxy-Authorization" =
-- 'Basic Zm5lcmJsZTpwYXNzd29yZA==';
--SET OutputRoot.HTTPRequestHeader."Proxy-Connection" = 'Keep-Alive';
--ProxyURL Overrides the HTTP(S) proxy location property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.ProxyURL = 'my.proxy';
--QueryString Allows the setting of outbound query string parameters. Each parameter must be set individually. For example:
--SET OutputLocalEnvironment.Destination.HTTP.QueryString.param1 = 'my"Value"1';
--SET OutputLocalEnvironment.Destination.HTTP.QueryString.param2 = 'my"Value"2';
--The above ESQL results in the following query string being encoded (according to http://tools.ietf.org/html/rfc3986) and sent with the outbound request:
--?param1=my%22Value%221&param2= my%22Value%222
--If the destination URL already has one or more query parameters, additional parameters specified here are appended to the existing list.
--QueryStringCCSID Specifies that, before encoding, the query string parameters must be converted into a character set other than the default, which is UTF-8. Any query string parameters are first converted into the specified CCSID before the resulting string is encoded, according to RFC3986. For example:
--SET OutputLocalEnvironment.Destination.HTTP.QueryStringCCSID = 943;
--The above ESQL results in any QueryString parameters being converted to the 943 code page before they are encoded. Note: Any query string parameters must contain the data in unicode.
-- HTTPAsyncRequest node Ovverrides ENDS ----------------------
-- HTTPAsyncResponse node Overrides
--You can retrieve context data that was stored by the HTTPAsyncRequest node from the following location in the local environment:
--SET myVar = InputLocalEnvironment.Destination.HTTP.UserContext;
--Context data is stored as a BLOB. To retrieve context data, assign the variable as a BLOB type or use a CAST. For example:
--DECLARE myVar BLOB;
--SET myVar = InputLocalEnvironment.Destination.HTTP.UserContext;
CREATE FUNCTION GETHTTPResUserContext(IN InputLocalEnvironment REFERENCE) RETURNS BLOB
BEGIN
  RETURN InputLocalEnvironment.Destination.HTTP.UserContext;
END;
-- HTTPAsyncResponse node Overrides ENDS ------------------------
-- HTTPRequest node Overrides
--RequestURL Overrides the Web service URL property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestURL = 'http://ibm.com/abc/';
CREATE PROCEDURE SetHTTPReqRequestURL (IN OutputLocalEnvironment REFERENCE, IN RequestURL CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestURL = RequestURL;
END;
--Timeout Overrides the Request timeout (sec) property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.Timeout = 42;
CREATE PROCEDURE SetHTTPReqTimeout (IN OutputLocalEnvironment REFERENCE, IN Timeout CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.Timeout = Timeout ;
END;
--TimeoutMillis Overrides the Request timeout (sec) property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.TimeoutMillis = 5000;
--This property defines the timeout in milliseconds. The value of TimeoutMillis overrides the value for Timeout if both values are set.
CREATE PROCEDURE SetHTTPReqTimeoutMillis (IN OutputLocalEnvironment REFERENCE, IN TimeoutMillis CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.TimeoutMillis = TimeoutMillis;
END;
--ProxyURL Overrides the HTTP(S) proxy location property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.ProxyURL = 'my.proxy';
CREATE PROCEDURE SetHTTPReqProxyURL (IN OutputLocalEnvironment REFERENCE, IN ProxyURL CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.ProxyURL = ProxyURL;
END;
--RequestLine.RequestURI Overrides the RequestURI, which is the path after the URL and port. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestLine.RequestURI = '/abc/def';
CREATE PROCEDURE SetHTTPReqRequestURI (IN OutputLocalEnvironment REFERENCE, IN RequestURI CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTPRequestLine.RequestURI = RequestURL;
END;
--RequestLine.HTTPVersion Overrides the HTTP version property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestLine.HTTPVersion = 'HTTP/1.1';
CREATE PROCEDURE SetHTTPReqHTTPVersion (IN OutputLocalEnvironment REFERENCE, IN HTTPVersion CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestLine.HTTPVersion = HTTPVersion;
END;
--KeepAlive Overrides the Enable HTTP/1.1 keep-alive property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.KeepAlive = TRUE;
CREATE PROCEDURE SetHTTPReqKeepAlive (IN OutputLocalEnvironment REFERENCE, IN KeepAlive BOOLEAN )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.KeepAlive = KeepAlive;
END;
--RequestLine.Method Overrides the HTTP method property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.RequestLine.Method = 'GET';
CREATE PROCEDURE SetHTTPReqMethod (IN OutputLocalEnvironment REFERENCE, IN Method CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestLine.Method = Method;
END;
--SSLProtocol Overrides the SSLProtocol. For example:
--SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = 'TLS';
--Valid values are: SSL, SSLv3, and TLS.
CREATE PROCEDURE SetHTTPReqSSLProtocol(IN OutputLocalEnvironment REFERENCE, IN SSLorSSLv3orTLS CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.SSLProtocol = SSLorSSLv3orTLS;
END;
--SSLCiphers Overrides the Allowed SSL Ciphers property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.SSLCiphers =
-- 'SSL_RSA_FIPS_WITH_3DES_EDE_CBC_SHA';
CREATE PROCEDURE SetHTTPReqSSLCiphers (IN OutputLocalEnvironment REFERENCE, IN SSLCiphers CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.SSLCiphers = SSLCiphers;
END;
--ProxyConnectHeaders Specifies additional headers that are used if the outbound request is an SSL connection through a proxy. These additional headers are sent with the initial CONNECT request to the proxy. For example, you can send proxy authentication information to a proxy server when you are using SSL. You can send multiple headers but each one must be separated by a carriage return and a line feed (ASCII 0x0D 0x0A), in accordance with RFC2616; for example:
--DECLARE CRLF CHAR CAST(X'0D0A' AS CHAR CCSID 1208);
--SET OutputLocalEnvironment.Destination.HTTP.ProxyConnectHeaders =		
--'Proxy-Authorization: Basic Zm5lcmJsZTpwYXNzd29yZA==' || CRLF ||
--'Proxy-Connection: Keep-Alive' || CRLF;
--This setting is used only if the request is an SSL request through a proxy server. To send proxy authentication information for a non-SSL request, specify the individual headers in the HTTPRequestHeader folder, as shown in the following example:
--SET OutputRoot.HTTPRequestHeader."Proxy-Authorization" =
-- 'Basic Zm5lcmJsZTpwYXNzd29yZA==';
--SET OutputRoot.HTTPRequestHeader."Proxy-Connection" = 'Keep-Alive';
--UseFolderMode Sets the UseFolderMode. Use for bitstream generation; for certain parsers this changes the output bitstream. For example:
--SET OutputLocalEnvironment.Destination.HTTP.UseFolderMode = TRUE;
CREATE PROCEDURE SetHTTPReqUseFolderMode (IN OutputLocalEnvironment REFERENCE, IN UseFolderMode BOOLEAN )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.RequestURL = UseFolderMode;
END;
--QueryString Allows the setting of outbound query string parameters. Each parameter must be set individually. For example:
--SET OutputLocalEnvironment.Destination.HTTP.QueryString.param1 = 'my"Value"1';
--SET OutputLocalEnvironment.Destination.HTTP.QueryString.param2 = 'my"Value"2';
--The above ESQL results in the following query string being encoded (according to http://tools.ietf.org/html/rfc3986) and sent with the outbound request:
--?param1=my%22Value%221&param2= my%22Value%222
--If the destination URL already has one or more query parameters, additional parameters specified here are appended to the existing list.
--QueryStringCCSID Specifies that, before encoding, the query string parameters must be converted into a character set other than the default, which is UTF-8. Any query string parameters are first converted into the specified CCSID before the resulting string is encoded, according to RFC3986. For example:
--SET OutputLocalEnvironment.Destination.HTTP.QueryStringCCSID = 943;
--The above ESQL results in any QueryString parameters being converted to the 943 code page before they are encoded. Note: Any query string parameters must contain the data in unicode.
--Compression Overrides the Use compression property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.Compression =
-- 'gzip';
CREATE PROCEDURE SetHTTPReqCompression (IN OutputLocalEnvironment REFERENCE, IN Compression CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.Compression = Compression;
END;
--To set a minimum size (in bytes) at which compression is applied, use the following override:
--SET OutputLocalEnvironment.Destination.HTTP.MinimumCompressionSize = 1048576;
--SSL authentication alias Overrides the SSL authentication alias property for the client-side of an HTTP connection on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.KeyAlias = 'Key1Alias';
CREATE PROCEDURE SetHTTPReqKeyAlias(IN OutputLocalEnvironment REFERENCE, IN KeyAlias CHARACTER )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.KeyAlias = KeyAlias;
END;
--Enable CRL checking Overrides the Enable Certificate Revocation List checking property on the node. For example:
--SET OutputLocalEnvironment.Destination.HTTP.EnableCRLCheck = 'true';
CREATE PROCEDURE SetHTTPReqEnableCRLCheck (IN OutputLocalEnvironment REFERENCE, IN EnableCRLCheck BOOLEAN )
BEGIN
  SET OutputLocalEnvironment.Destination.HTTP.EnableCRLCheck = EnableCRLCheck ;
END;
-- HTTPRequest node Overrides ENDS -----------


--LocalEnvironment.File
--LocalEnvironment.File.Read
--LocalEnvironment.WrittenDestination.File
--LocalEnvironment.Destination.File
--LocalEnvironment.Destination.File.Remote
--LocalEnvironment.Wildcard.WildcardMatch
--LocalEnvironment.FTE
--LocalEnvironment.WrittenDestination.FTE
--LocalEnvironment.Destination.FTE
--LocalEnvironment.CD
--LocalEnvironment.CD.Transfer
--LocalEnvironment.Destination.CD
--LocalEnvironment.WrittenDestination.CD

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.