How to create xml for all fields?

[zukes1966]

Is there any method to create xml for all fields for DB2 *file?

Something like

select xmlserialize (xmldocument (xmlrow (x. *)) as char (65000))
from x
where y ='1'

I was thinking about using SysColumns.
Has anyone solved such and/or similar a problem?

[B.Hauser]

Yes, Open Source ... https://github.com/BirgittaHauser/Generate-XML-and-JSON

BTW you cannot define a CHAR or VARCHAR field in SQL with 65000 Characters, maximum length for Char/VarChar is 32740. For longer character fields you have to define a CLOB (character large object)

Birgitta

[JonBoy]

Seems to me that Birgitta's tools would do exactly what you want.

https://github.com/BirgittaHauser/Generate-XML-and-JSON

[zukes1966]

Of course, Brigitta's code is great and I will keep it for learning new things.
However, I decided to take matters into my own hands and write the solution in SQLRPGLE. It seems possible even using BLOB variables

[JonBoy]

<sigh>Reinventing wheels may well be the death of us ... ;-)

[zukes1966]

but "Practice makes perfect"

[Scott Klement]

I would discourage you from using a BLOB. XML is a character format, it should be in a CLOB or DBCLOB. If you need to include binary data, it should be base64-encoded.

[zukes1966]

Because my solution is tailored to local requirements. I will describe for other colleagues the main points of how you can generate XML for any SQL in RPG.

1. Create temporary table based on sql request

Execute SQL statement 'create table '+qtemp/tsql + ' as ('+%trim(sql_)+') with no data';

2. Read temporary file fields description using cursor for following sql

      'select COLUMN_NAME from syscolumns2_session ' +
            'where TABLE_NAME='''+tsql+''' ' +
                'for read only';

3. Prepare list of fields from db2 to structure requeired by SQL XML commands. Result must be like:

fieldA as "name_filedA", filedB as "name_filedB ...

4. execute SQL

      DCL-PROC  l_sql_to_xml ;
       DCL-PI *N ;
         i_sql char(16000) const;
         i_row char(16000) const;
       END-PI;

       DCL-S sXML varchar(32000) inz;
       DCL-S lob   sqltype(clob :6000000);


       sXML = 'with xml_data as ' +
               '( '+
                  'Select ' +
                    'XMLROW ('+i_row+' option row "rowName") as elem ' +
                  'from (' + %trim(i_sql) + ') ' +
               ') ' +
              'select xmlserialize(' +
                        'xmldocument(' +
                          'xmlelement( NAME "arrayName", '+
                                      'xmlagg( xml_data.elem )) '+
                                   ') as clob ' +
                                 ') ' +
             'from xml_data';
       Exec Sql PREPARE mXML FROM :sXML;
       catch_sql_error () ;
       Exec Sql DECLARE cXML CURSOR FOR mXML;
       catch_sql_error () ;
       Exec Sql OPEN cXml ;
       catch_sql_error () ;
        Exec Sql FETCH cXML INTO :lob;
        catch_sql_error () ;

here we have xml in text format lob.data (up to 16MB. if you need more clob_locator )

       END-PROC;

Thanks to everyone for your suggestions.