Advantage Developer Zone

 
 
 

Exporting Advantage Data as XML

Friday, May 09, 2008

Many people have a need to export or manipulate Advantage data as XML. This is very important when working with web services since most data transfer between web services is accomplished via XML datasets.

Defining the AEP

One of the easiest mechanisms for adding XML export functionality to your application is through an Advantage Extended Procedure (AEP). In this design the AEP will take three parameters; tablename, filepath and xmlfilename. The AEP will have to open the specified table and convert it to an XML file with the specified name on the specified path.

The AEP will have no output parameters. The input parameters are defined below:

TableName Char 255
FilePath Char 260
XMLFileName Char 255

The Example AEP is named sp_TableToXML and is either a DotNet Assembly which must be registered in the data dictionary, or an AEP file written with Delphi 7. An example of the Stored Procedure Dialog is shown below.



Visual Studio

We will use the code template for an Advantage AEP from within Visual Studio 2005 for our example. The template is installed with the Advantage .Net Data Provider. We are using version 9.0.0.1 for this example.



We will use the WriteXML method of the DataSet object to generate the XML file. The WriteXML method only requires a fully qualified path to create a simple XML file. In this example we will include the table schema in the file by specifying the XmlWriteMode.

The method TableToXML in our AEP Container simply reads in the supplied parameter values and opens the specified table. After successfully filling the dataset with the specified table, the WriteXML method is called. 

   1: try
   2: {
   3:     // Open the specified table
   4:     oCommand.CommandText = sTableName;
   5:     oAdapter = new Advantage.Data.Provider.AdsDataAdapter((AdsCommand)oCommand);
   6:     oAdapter.Fill(oDataSet);
   7:  
   8:     // Export the table to XML
   9:     oDataSet.WriteXml(sDestination.Trim() + "\\" + sFileName.Trim() + ".xml", 
  10:                       XmlWriteMode.WriteSchema);
  11:
  12: }
  13: catch (Exception e)
  14: {
  15:     IDbCommand oErrCommand = oStateInfo.DataConn.CreateCommand();
  16:     oErrCommand.CommandText = "INSERT INTO __error VALUES( 1,
  17:                               + e.Message + "'";
  18:     oErrCommand.ExecuteNonQuery();
  19: }
Delphi

Delphi also has a library which will generate an XML file for us. The TClientDataset, which is an in memory table, has a SaveToFile method which will write an XML file with the table schema included.

To export the data from an Advantage table we need to use a DataSetProvider in conjunction with the TClientDataSet. The DataSetProvider uses a TAdsTable as the data source. With the TClientDataSet linked to the DataSetProvider the Open method reads the data from our TAdsTable and loads it into memory much like the .Net DataSet object.

Once the data is loaded into the TClientDataSet it can be saved as an XML file. The code snippet is below. Note that tblSource, cdsTransform and dspSource are defined in the AEP data module.

   1: // Open the specified table
   2:  tblSource.TableName := strTableName;
   3:  tblSource.Open;
   4:  
   5:  // Load the table into the clientdataset
   6:  cdsTransform.Open;
   7:  
   8:  // Build the output file name
   9:  strFileName := strFilePath + '\' + strXMLFileName + '.xml';
  10:  
  11:  // Create the xml file
  12:  cdsTransform.SaveToFile(strFileName, dfXML);
  13:  
  14:  // Close the ClientDataSet
  15:  cdsTransform.Close;
  16:  
  17:  // Close the source table
  18:  tblSource.Close;

Summary

Exporting a table as an XML file is a fairly simple process. Through the use of AEPs this functionality can easily be added to your database. The full code for the example stored procedures is available in Code Central on the DevZone. There is a C# project written using Visual Studio 2005 and a Delphi 7 project.

Code Central
Example using Visual Studio 2005 C# & Delphi 7


 
 
 

© Copyright 2020, Sybase Inc. Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics Follow Advantage