How to transform an output from an already xml query into Informix?

0

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

This using IBM Informix v11.50 database and native mode in the database.

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

    
asked by anonymous 03.04.2014 / 01:46

1 answer

0

From seat 11.10 of the seat, it is possible to do so. 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, however there are limitations that to circumvent them can be a bit laborious and even depend on certain configurations in the bank (SMARTBLOB spaces).

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

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

For reference to an older version of the database, see this link of version 11.50
For reference in the most current version of the bank see this link of version 12.10

Example of a 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.
    
03.04.2014 / 01:46