Delimiter //
/**
 * Author:  armen
 * Created: 23 juin 2020
 */

Drop Procedure if exists LOADER_GET_ALL_INFO_FORMATED//
Create Procedure LOADER_GET_ALL_INFO_FORMATED (
	In format varchar(5)
	, In batchNum int
	, out formatedData text
)
Begin
    set @lgaif_batchNum = batchNum;
    if format = 'json' then
        Set formatedData = (
                    Select
                       Concat(
                                '{'
                                , Group_concat(
                                    Concat(
                                        '"' , LOADER_INFO_NAME, '":"' 
                                            , LOADER_INFO_VALUE
                                        , '"' 
                                    )
                                    separator ','
                                ) 
                                , '</LOADER_CONTEXT>'
                            ) json_context
                    from LOADER_INFO
                    where LOADER_INFO_BATCH_NUM = @lgaif_batchNum
                )
            ;
    End if;
    if format = 'xml' then
        Set formatedData = (
                    Select
                        Concat(
                                '<?xml version="1.0" encoding="utf-8"?><LOADER_CONTEXT>'
                                , Group_concat(
                                    Concat(
                                        '<' , LOADER_INFO_NAME, '>' 
                                            , LOADER_INFO_VALUE
                                        , '</' , LOADER_INFO_NAME, '>' 
                                    )
                                    separator ''
                                ) 
                                , '</LOADER_CONTEXT>'
                            ) xml_context
                    from LOADER_INFO
                    where LOADER_INFO_BATCH_NUM = @lgaif_batchNum
                )
            ;
    End if;
    
end ;