ESQL code Sample

In this post I will provide some example of ESQL code that could be useful.

The codes are made available using Gist.

TREE --> XML 

In the following gist, I provide an example on how to create a XML physical representation of a IIB in memory tree.

The principle is the following

1. Create an element of type XMLNSC parser
2. Copy or create a IIB tree
3. Use the function ASBITSTREAM to serialize the tree in bitstream using the parser (here XML)

	-- Create a folder "ExceptionList" in the LocalEnvironment tree. This Folder will hold the BLOB representation of the ExceptionList
	SET OutputLocalEnvironment.Variables.BitStream.Info = 'exceptionList';
	-- Define a XML parser element to be used to serialize the ExceptionList into bitstream
	CREATE LASTCHILD OF OutputLocalEnvironment.Variables.BitStream DOMAIN('XMLNSC') NAME 'XMLNSC';
	-- Attach or create the tree that has to be serialized under the XML parser element. here it is the ExceptionList
	SET OutputLocalEnvironment.Variables.BitStream.XMLNSC.ExceptionList = InputExceptionList;
	-- Create the BLOB representation of the ExceptinList using the function ASBITSTREAM
	SET OutputLocalEnvironment.Variables.BitStream.BLOB =
	ASBITSTREAM(OutputLocalEnvironment.Variables.BitStream.XMLNSC.ExceptionList OPTIONS FolderBitStream);

view raw gistfile1.sql hosted with ❤ by GitHub

BLOB --> XML

In the following gist, I provide an example on how to create a XML tree from a BLOB.
In the example the BLOB is provided as test in a hexadecimal representation. The code parses it to an XML and append it in the current XML.

	-- input of this sample is a XML message <XmlCollection><Collection>3c586d6c436f6c6c65637</Collection></XmlCollection>
	-- Collection is a XML data in hexadecimal (BLOB) representation
	--Create an element of type parser
	CREATE LASTCHILD OF OutputRoot DOMAIN 'XMLNSC' NAME 'XMLNSC';
	-- Create the root tag element of your output XML
	CREATE LASTCHILD OF OutputRoot.XMLNSC NAME 'RootTag';
	DECLARE ptrCollection REFERENCE TO InputRoot.XMLNSC.XmlCollection.Collection;
	WHILE LASTMOVE(ptrCollection) DO
	--Parse the BLOB into XML using the PARSE function
	CREATE LASTCHILD OF OutputRoot.XMLNSC.RootTag.RecSec PARSE(CAST(ptrCollection AS BLOB) OPTIONS FolderBitStream CCSID 1208 FORMAT 'XMLNSC');
	MOVE ptrCollection NEXTSIBLING;
	END WHILE;

view raw gistfile1.sql hosted with ❤ by GitHub