-- <ScriptOptions statementTerminator="@" />
DROP PROCEDURE DASUSR1.TECHNOLUX_MAIN_PAGE@
CREATE PROCEDURE DASUSR1.TECHNOLUX_MAIN_PAGE(OUT MAIN_PAGE CLOB(200K))
LANGUAGE SQL
BEGIN ATOMIC
	DECLARE MP_NUM INT;
	DECLARE TEMPLATE_NUM INT;
	DECLARE TEMPLATE_XML XML;
	DECLARE HEADER_CLOB CLOB(4k);
	DECLARE FOOTER_CLOB CLOB(4k);
	DECLARE PAGE_CLOB CLOB(4k);
	DECLARE tracefile VARCHAR( 255 ) DEFAULT '/var/XML/test1.html';
	
	DECLARE RC_E INT;
	DECLARE STMT_E VARCHAR( 4096 );
	DECLARE STMT VARCHAR( 1000 );
	
	DECLARE SQLCODE INT default  0 ;
	DECLARE C1 CURSOR FOR S1;
	DECLARE UNDO handler for sqlexception
		BEGIN
			SET RC_E=SQLCODE;
			INSERT INTO DASUSR1.ERROR_LOG VALUES (DATE(CURRENT DATE), TIME(CURRENT TIME), RC_E, STMT_E);
		END;
	
-- ПОЛУЧАЕМ НОМЕР ГЛАВНОЙ СТРАНИЦЫ
	select XT.MP INTO MP_NUM FROM DASUSR1.PROJECTS T, xmltable('$c/project[@id="1"]' PASSING T.PROJECT AS "c" COLUMNS "MP" INT PATH '@main_page') XT;
-- ВЫДЕРГИВАЕМ НОМЕР ШАБЛОНА ДЛЯ СТРАНИЦЫ
	SET STMT ='select XT.TPL FROM DASUSR1.PAGES T, xmltable(''$c/page[@id="'||RTRIM(CHAR(MP_NUM))||'"]'' PASSING T.PAGE AS "c" COLUMNS "TPL" INT PATH ''@template'') XT';
	PREPARE S1 FROM STMT;
	OPEN C1;
	FETCH C1 INTO TEMPLATE_NUM;
	CLOSE C1;
	
	SET STMT_E = 'FETCH C1 INTO HEADER_CLOB, FOOTER_CLOB;';
-- строим xml код ИЗ ШАБЛОНА;
	SET STMT = 'SELECT T.HEADER, T.FOOTER FROM DASUSR1.TEMPLATES T WHERE (XMLQUERY(''$c/template[@id="'||RTRIM(CHAR(TEMPLATE_NUM))||'"]'' PASSING T.TEMPLATE AS "c" )) IS NOT NULL';
	PREPARE S1 FROM STMT;
	OPEN C1;
	FETCH C1 INTO HEADER_CLOB, FOOTER_CLOB;
	CLOSE C1;
	
	SET HEADER_CLOB = '<?xml version="1.0" encoding="utf-8"?><page>'||HEADER_CLOB||FOOTER_CLOB||'</page>';
	
	SET STMT_E = 'FETCH C1 INTO PAGE_CLOB;';
-- ПОЛУЧАЕМ XSL КОД СТРАНИЦЫ;	
	SET STMT = 'SELECT T.PAGE_CODE FROM DASUSR1.PAGES T WHERE (XMLQUERY(''$c/page[@id="'||RTRIM(CHAR(MP_NUM))||'"]'' PASSING T.PAGE AS "c" )) IS NOT NULL';
	PREPARE S1 FROM STMT;
	OPEN C1;
	FETCH C1 INTO PAGE_CLOB;
	CLOSE C1;
	
	SET PAGE_CLOB = '<?xml version="1.0" encoding="utf-8"?>'||PAGE_CLOB;
	SET STMT_E = 'XSLTRANSFORMTOCLOB';
-- Передаем на обработку SAXON
	SELECT DASUSR1.XSLT2ToClob(HEADER_CLOB, PAGE_CLOB, tracefile) INTO MAIN_PAGE FROM sysibm.SYSDUMMY1;
END@

SELECT DASUSR1.XSLT2ToClob(HEADER_CLOB, PAGE_CLOB, tracefile) INTO MAIN_PAGE FROM sysibm.SYSDUMMY1;

DROP PROCEDURE DASUSR1.TEC@
CREATE PROCEDURE DASUSR1.TEC(OUT MAIN_PAGE CLOB(8K))
LANGUAGE SQL
BEGIN
	DECLARE MP CLOB(4K);
	DECLARE TE CLOB(4K);
	
	SET MP = '<Здесь XML код, который мы передаем функции = HEADER_CLOB>';
	SET TE = '<Здесь XSL код, который мы передаем функции = PAGE_CLOB>';
	SELECT DASUSR1.XSLT2ToClob(MP, TE, '/var/XML/tec.log') INTO MAIN_PAGE FROM SYSIBM.SYSDUMMY1;
end@

javax.xml.transform.TransformerConfigurationException: Failed to compile stylesheet.  1  error detected.
	at net.sf.saxon.PreparedStylesheet.prepare(PreparedStylesheet.java: 153 )
	at net.sf.saxon.TransformerFactoryImpl.newTemplates(TransformerFactoryImpl.java: 137 )
	at SaxonXSLT.XSLTransformToClob(SaxonXSLT.java: 30 )

SELECT DASUSR1.XSLT2ToClob(HEADER_CLOB, PAGE_CLOB, tracefile) INTO MAIN_PAGE FROM sysibm.SYSDUMMY1;

SET MAIN_PAGE = HEADER_CLOB;

SET MAIN_PAGE = PAGE_CLOB;