How to transform a query output into xml in Informix?

Question

It is possible to take any output of a query directly from the bank and
bring the result already formatted in XML text ?

This using IBM Informix v11.50 database and natively in the database.

Where instead of the database returning a dataset with each field in its format will return a TXT in XML.

Answer 1

From Seat 11.10 it is possible yes.
To find out which version of the database you are using, run this SQL:

select dbinfo('version', 'full') from systables where tabid = 1;

There are some specific functions for this, but there are limitations that to circumvent them can be a little laborious and even depend on certain settings in the bank (SMARTBLOB Spaces).

The basic function for this is the genxml() where it will return in only 1 row all query records. As this function returns an LVARCHAR it is limited in 32kbytes what little for an XML, so you have to be careful how you select a lot of data otherwise you will have error 8368 (see at the end of the example below), to get around this problem you have to use other functions .

A list of existing functions (you will find details in the manual links below): genxml, genxmlelem, genxmlschema, genxmlquery, genxmlqueryhdr, extract, extractvalueclob, existsnode, idsxmlparse.

For reference in an older version of the database see this linkof version 11.50
For reference in the latest version of the database see this link of version 12.10

Example of an sql run in dbaccess.

database sysmaster;
Database selected.

select genxml(systables, "tabela") from systables where tabid = 1 ;
genxml  <tabela tabname="systables" owner="informix                        " pa
        rtnum="1048800" tabid="1" rowsize="497" ncols="24" nindexes="2" nrows="
        245.0000000000000000" created="21/11/2011" version="65539" tabtype="T"
        locklevel="R" npused="9.0000000000000000" fextsize="32" nextsize="32" f
        lags="0" type_xid="0" am_id="0" pagesize="4096" ustlowts="2011-11-21 11
        :23:13.00000" secpolicyid="0" protgranularity=" "/>
1 row(s) retrieved.


select genxmlclob(systables, "tabela") from systables where tabid = 1 ;
genxmlclob
<tabela tabname="systables" owner="informix                        " partnum="1048800" tabid="1" rowsize="497" ncols="24" nindexes="2" nrows="245.0000000000000000" created="21/11/2011" version="65539" tabtype="T" locklevel="R" npused="9.0000000000000000" fextsize="32" nextsize="32" flags="0" type_xid="0" am_id="0" pagesize="4096" ustlowts="2011-11-21 11:23:13.00000" secpolicyid="0" protgranularity=" "/>
1 row(s) retrieved.


select genxmlclob(systables, "tabela") from systables where tabid <=5 ;
genxmlclob
<tabela tabname="systables" owner="informix                        " partnum="1048800" tabid="1" rowsize="497" ncols="24" nindexes="2" nrows="245.0000000000000000" created="21/11/2011" version="65539" tabtype="T" locklevel="R" npused="9.0000000000000000" fextsize="32" nextsize="32" flags="0" type_xid="0" am_id="0" pagesize="4096" ustlowts="2011-11-21 11:23:13.00000" secpolicyid="0" protgranularity=" "/>
<tabela tabname="syscolumns" owner="informix                        " partnum="1048801" tabid="2" rowsize="157" ncols="10" nindexes="2" nrows="2778.0000000000000000" created="21/11/2011" version="65539" tabtype="T" locklevel="R" npused="30.0000000000000000" fextsize="32" nextsize="32" flags="0" type_xid="0" am_id="0" pagesize="4096" ustlowts="2011-11-21 11:23:13.00000" secpolicyid="0" protgranularity=" "/>
<tabela tabname="sysindices" owner="informix                        " partnum="1048802" tabid="3" rowsize="3323" ncols="15" nindexes="2" nrows="192.0000000000000000" created="21/11/2011" version="65539" tabtype="T" locklevel="R" npused="11.0000000000000000" fextsize="32" nextsize="32" flags="0" type_xid="0" am_id="0" pagesize="4096" ustlowts="2011-11-21 11:23:13.00000" secpolicyid="0" protgranularity=" "/>
<tabela tabname="systabauth" owner="informix                        " partnum="1048803" tabid="4" rowsize="77" ncols="4" nindexes="2" nrows="262.0000000000000000" created="21/11/2011" version="65539" tabtype="T" locklevel="R" npused="6.0000000000000000" fextsize="32" nextsize="32" flags="0" type_xid="0" am_id="0" pagesize="4096" ustlowts="2011-11-21 11:23:13.00000" secpolicyid="0" protgranularity=" "/>
<tabela tabname="syscolauth" owner="informix                        " partnum="1048804" tabid="5" rowsize="73" ncols="5" nindexes="2" nrows="109.0000000000000000" created="21/11/2011" version="65539" tabtype="T" locklevel="R" npused="3.0000000000000000" fextsize="32" nextsize="32" flags="0" type_xid="0" am_id="0" pagesize="4096" ustlowts="2011-11-21 11:23:13.00000" secpolicyid="0" protgranularity=" "/>
1 row(s) retrieved.

select genxml(systables, "tabela") from systables ;
 8368: Function (genxml) Buffer size exceeds maximum size.
Error in line 2
Near character position 49

Database closed.