Fetching sql cursor into xml_clob_file problem

[zukes1966]

I have cursor for SQL as follow

With CTE AS ( Select EOYB10P.*, EOYB11P.* from EOYB10P join        
EOYB11P on B12YEAR = E11YEAR  and B12CIF = E11CIF where           
B12DTYP <> 'E' )                                                 
select    
       xmlelement(NAME "End"                     
, xmlnamespaces( 'http://www.w3.org/2001/                          
XMLSchema-instance' as "xsi" ), xmlattributes( 'End.xsd'  
as "xsi:noNamespaceSchemaLocation" ), xmlrow(RTRIM(               
B12CIF) AS "CIF", B12BL AS "businessLine", RTRIM(B12DATE)          
AS "docDate", RTRIM(B12BCC) AS "barCodeCif", B12ZIP AS             
"docZipCode", RTRIM(B12IPF) AS "inPostFlag", RTRIM(B12FF) AS       
"foreignFlag", RTRIM(B12OF) AS "opustFlag", RTRIM(B12ADR1) AS      
"docAddress1", RTRIM(B12ADR2) AS "docAddress2", RTRIM(B12ADR3)     
AS "docAddress3", RTRIM(B12ADR4) AS "docAddress4", RTRIM(         
B12ADR5) AS "docAddress5", RTRIM(B12ADR6) AS "docAddress6",        
RTRIM(B12ADR7) AS "docAddress7", RTRIM(B12ADR8) AS "docAddress8",  
RTRIM(B12CAD1) AS "corpAddress1", RTRIM(B12CAD2) AS                
"corpAddress2", RTRIM(B12CAD3) AS "corpAddress3", RTRIM(B12CAD4)   
AS "corpAddress4", RTRIM(B12CAD5) AS "corpAddress5",              
RTRIM(B12MMC6) AS "marketingMessageCode6", RTRIM(B12MMC7) AS       
"marketingMessageCode7", RTRIM(B12MMC8) AS "marketingMessageCode8"
, RTRIM(B12MMC9) AS "marketingMessageCode9", xmlagg(xmlrow(        
E11ACCT AS "account", E11APL AS "accountApplication", E11TYP       
AS "accountTyp", E11PROD AS "accountProduct", E11SUBC AS          
"accountSubCode", RTRIM(E11PRNA) AS "productName", RTRIM(E11IBAN)  
AS "accountIBAN", RTRIM(E11CYNA) AS "currencyName", E11EOYB       
AS "accountEndOfYearBalance" OPTION ROW "AccountElement"           
) ) as "AccountListArray" OPTION ROW "EndOfYearBalancesDocElement"
) )                                                                                                         
from CTE group by B12CIF, B12BL, B12DATE, B12BCC,                 

B12ZIP, B12IPF, B12FF, B12OF, B12ADR1, B12ADR2, B12ADR3,          
B12ADR4, B12ADR5, B12ADR6, B12ADR7, B12ADR8, B12CAD1, B12CAD2,     
B12CAD3, B12CAD4, B12CAD5, B12CAD6, B12CAD7, B12CAD8, B12MMC0     
, B12MMC1, B12MMC2, B12MMC3, B12MMC4, B12MMC5, B12MMC6,            
B12MMC7, B12MMC8, B12MMC9  order by B12Cif

If I execute above sql from strsql I receive plenty records.

But If I create xml cursor and execute in RPGLE code

dcl-s outfile sqltype(xml_clob_file)
exec sql fetch cXML into :outfile

In result file I have only 1 (one) record.



Any idea?

[B.Hauser]

What is the SQLCODE or SQLSTATE after your FETCH Statement?
Did you change the File Operation _FO after the first FETCH to SQFAPP?

I'm not quite sure if you can split a XML document into multiple steps when writing to an IFS file.
IMHO the XML Data is checked if it is a wellformed XML document.

So I think, you need to generate the complete XML document in a single SQL statement.
Currently I'm missing the XMLAGG function in your SQL Statement.

If you generate the complete SQL Statement in a single step, you do not need a cursor, SELECT .... INTO is enough.

Birgitta

[zukes1966]

SQLCODE = 0 SQLSTMT='0000'

When I tried use THIS cursor in loop new data apears but xml was broken (each time iSEries header)

For simple SQL like select * from a such method works perfectly.

It means the problem is that cursor do not return all data for
exec sql fetch cXML into outfile.


and I have to use cursor because SQl is built dynamically in the xml section