Split a string by delimiter in ESQL (extended-sql) and store splitted parts under a root XML element.

splitStringByDelimiter.esql

CREATE FUNCTION Main() RETURNS BOOLEAN
	BEGIN
		DECLARE message CHARACTER;
		
		-- \n in hex is 0x0a, we need to represent it as '\n'
		-- and encode it as UTF-8 whose CodingCharSetId is 1208
		DECLARE newlineChar CHAR CAST(CAST('X''0A''' AS BLOB) AS CHAR CCSID 1208);

		
		SET message = InputRoot.XMLNSC.text;
		
		CREATE FIELD OutputRoot.XMLNSC.message.elements;
		DECLARE pointerToElements REFERENCE TO OutputRoot.XMLNSC.message.elements;
		
		CALL splitString(message, newlineChar, pointerToElements, 'line');
	
		RETURN TRUE;
	END;


CREATE PROCEDURE splitString(IN text CHARACTER, IN delimiter CHARACTER, IN rootElement REFERENCE, IN nameOfNewElement CHAR)
	BEGIN
		DECLARE index INTEGER 1;
		DECLARE remainingText CHARACTER text;
		
		WHILE LENGTH(remainingText) > 0 DO
			IF CONTAINS(remainingText, delimiter) THEN
				-- Get data before delimiter
				SET rootElement.{nameOfNewElement}[index] = SUBSTRING (remainingText BEFORE delimiter);
				-- Remove data already stored data before element
				SET remainingText = SUBSTRING (remainingText AFTER delimiter);
				 
			ELSE
				-- LAST ELEMENT CASE
				SET rootElement.{nameOfNewElement}[index] = remainingText;
				SET remainingText = ''; -- Set to empty string to stop loop
			END IF;
			
			-- Increment counter
			SET index = index + 1;
		END WHILE;
	END;