RPG/LE SQL Result Sets - A real Challenge

[Arturo_Duarte@xxxxxxxxxxxxxxx]

I have a need to return a result set, that unto itself will have five (5) 
imbedded result sets.  The need is to provide, to an XML document, related 
but dissimilar sets of data.  Each set is independent of the other 
(different format, different fields for each set, different field sizes 
for each set, different number of rows, and some without rows at all). 
When the entire set is returned, it will be applied to an XML Web Page 
based on the following structure:

TopLevel 0 - 0 to many rows:
Level 1 - 0 to many rows.
        Level 2 - 0 to many rows.
        Level 3 - 0 to many rows.
        Level 4 - 0 to many rows.

Somehow, when the data is returned to the caller as a single unit of work, 
it must return with properly imbedded data to define a result set for each 
level.  I've thought of providing the data in a single very long row, and 
have multiple rows as required for each set, but this complicates the Web 
side beyond measure.  I've also reviewed the option of having multiple 
arrays defined in a single data structure result set, with each array 
(same count of rows) of different length/attributes.  This then returns a 
single string (the size of the data structure) without allowances for the 
different row count (i.e. 0 to many array elements) and each data element 
of the set must then be interrogated to determine how many rows and their 
content.  Each row must return with values that indicate the actual number 
of rows in each sub set.

I've examined the statement:

Set Result Sets Array :dsArray1 for :dsArrayRows1 rows,
                                Array :dsArray2 for :dsArrayRows2 rows,
                                Array :dsArray3 for :dsArrayRows3 rows,
                                Array :dsArray4 for :dsArrayRows4 rows,
                                Array :dsArray5 for :dsArrayRows5 rows

It obviously doesn't work - although the manual implies that it will work 
in the syntax diagram image (below it does state that only one "Array" can 
be specified).  Some how, a dynamic result set needs to be defined and 
returned to the caller.  I just don't know how to do this.

We are V5R1, with plans to update to V5R2 "sometime" in the future.  This 
eliminates the "multidimensional" aspect of data structures that are 
provided in V5R2. 


Jim Low
Software Systems Engineer, Sr.
Data Integrity