Home Services Partners Company

Persisting Result Sets as XML Data Files

Download as PDF


DataDirect Connect® Series ODBC drivers (DataDirect Connect for ODBC and DataDirect Connect64 for ODBC) include a feature that enables you to retrieve result sets from a relational database (such as Oracle, Informix, DB2, or Sybase) and store the result sets as XML data files (see following figure). This can be accomplished by simply adding one or two lines of code to existing ODBC applications.

DataDirect Connect for ODBC Drivers are currently the only ODBC drivers on the market that can persist result sets as XML data files.

Benefits of Using DataDirect Connect Series ODBC Drivers to Persist Data as XML

The ability to persist result sets as XML data files helps you take advantage of XML faster as you adopt advanced B2B and B2C business processes on the Web. DataDirect Technologies provides a simple way for you to use existing data and publish it on the Web. You do not have to replicate the data or manually reformat it—you just add one or two lines of code to your existing ODBC application and an XML data file is created.

XML allows you to use the power of the Web to provide up-to-date information to your colleagues within the same organization, your outside partners, and your customers. For example, instead of e-mailing a text-formatted report to sales representatives in the field, you can publish daily sales information in an XML report on a secure Web page, which is accessible to a PC, a laptop, or a handheld device.

Creating XML from data stored in a relational database offers a way for you to:

  • Quickly publish information (such as up-to-date price lists or current inventory totals) to the Web
  • Allow customers to interact with the data you publish on the Web

Sample Scenario

In this scenario, a wholesaler decides to provide an online catalog over the Web. The wholesaler chooses XML for the format of the catalog because XML provides semantics to the online catalog information that will allow the customer to do business with the wholesaler online.

The wholesaler's catalog information is stored natively in an Oracle relational database inside the wholesaler's Internet firewall. Through an ODBC application using a DataDirect Connect Series ODBC Oracle driver, the wholesaler retrieves the data from the relational database and publishes that information to the Web Server as an XML data file in the form of an online catalog. To do this, the ODBC application issues a SQL Select statement, for example:

SQLExecDirect (hstmt, "Select ITEM_NO, PRICE, DESCRIPTION,

Once the wholesaler has published the catalog on the Web as an XML data file, the customer can view the catalog using any XML-enabled browser. More important, customers can deal with the wholesaler more efficiently by using applications to interact with the catalog just as if they had direct access to the wholesaler's relational database.

The wholesaler develops and distributes to his customers an ODBC application that:

  • Connects to a URL that specifies the wholesaler's XML catalog
  • Checks the price and availability of any item in the wholesaler's catalog
  • Places the order if the item is in stock

In addition, the customer could develop custom applications to interact with the wholesaler's catalog, applications that fit the exact needs of the customer.

In this scenario, both the wholesaler and consumer benefit by the wholesaler's catalog being published to the Web using XML. And, DataDirect Connect Series ODBC drivers provide a way to persist data easily from a relational database to XML.

Implementing XML Persistence

This section provides step-by-step instructions for implementing XML persistence in an ODBC application using a DataDirect Connect for ODBC Driver.

The ODBC application must:

  1. Turn on STATIC cursors. For example:

    SQLSetStmtAttr (hstmt, SQL_ATTR_CURSOR_TYPE,

    NOTE: A result set can be persisted as an XML data file only if the result set is generated using STATIC cursors.


  3. Execute a SQL statement. For example:

    SQLExecDirect (hstmt, "Select * from GTABLE", SQL_NTS)

  4. Persist the result set as an XML data file. For example:

    SQLSetStmtAttr (hstmt, SQL_PERSIST_AS_XML,
    "c:\temp\GTABLE.XML", SQL_NTS)

    NOTE: A statement attribute is available to support XML persistence, SQL_PERSIST_AS_XML. A client application must call SQLSetStmtAttr with this attribute as an argument. See the following table for the definition of valid arguments for SQLSetStmtAttr.




The handle of the statement that contains the result set to persist as XML.


SQL_PERSIST_AS_XML. This statement attribute can be found in the file qesqlext.h, which is installed with the driver.


Pointer to a URL that specifies the full path name of the XML data file to be generated. The directory specified in the path name must exist, and if the specified file name exists, the file will be overwritten.


The length of the string pointed to by ValuePtr or SQL_NTS if ValuePtr points to a null terminated string.

An ODBC application can persist the data at any time that the statement is in an executed or cursor-positioned state.


DataDirect Connect Series ODBC drivers include the ability to persist result sets as XML data files for the leading databases: Oracle, Informix, DB2, and Sybase. The benefit to you is that you can quickly publish information to the Web and allow customers to interact with the data you publish. Currently, DataDirect Technologies provides the only ODBC drivers on the market that provide this functionality.


Persisting Result Sets as XML Data Files


ODBC connectors

Real-Time Access to XML Data


ODBC Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers