PERSIST

Syntax:

>>-EXEC SQL--.------------.--->
                     +-AT db_name-+

 >--PERSIST cursor_name TO xml_destination--->

 >-----END-EXEC-----<>

Parameters:

AT `db_name`	The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement.
`cursor_name`	A previously declared and opened cursor.
`xml_destination`	An identifier, a host variable, or a literal enclosed in single or double quotes

Comments:

To use PERSIST, you must:

Have an XML ODBC driver installed
Embed the SET SCROLLOPTION statement and set it to STATIC

If you are using Data Direct Connect ODBC drivers, you must use version 3.70 or later.

Example:

 01  hv  pic x(50).
 procedure-division.

    *> set whenever clause to handle sql errors
    exec sql whenever sqlerror goto sql-error end-exec
    exec sql whenever sqlwarning perform sql-warning end-exec

    *> connect to data source
    exec sql connect to "data source"  end-exec

    *> declare static cursor with column info you want to save to xml file
    exec sql
      declare c static cursor for 
        select * from emp
    end-exec

    *> open cursor
    exec sql open c end-exec 

    *> save data to xml file using double quoted literal
    exec sql 
      persist c to "c:\XML Files\xmltest1.xml" 
    end-exec 

    *> save data to xml file using single quoted literal
    exec sql 
      persist c to 'c:\XML Files\xmltest2.xml'
    end-exec

    *> save data to xml file using a host variable
    move "c:\XML Files\xmltest3.xml" to hv 
    exec sql
      persist c to :hv 
    end-exec 

    *> close the cursor
    exec sql close c end-exec

    *> disconnect from datasource
    exec sql disconnect current end-exec

    goback.