A Complete Example
From InterBase
Go Up to Exporting XML
This section provides a complete example of how to generate XML from an InterBase table.
XML Output Structure
<?xml version="1.0">
<Database>
<Tablename>
<Row>
<ColumnAlias1>Data</ColumnAlias1>
<ColumnAlias2>Data</ColumnAlias2>
</Row>
<Row>
<ColumnAlias1>Data</ColumnAlias1>
<ColumnAlias2>Data</ColumnAlias2>
</Row>
…
</Tablename>
</Database>
The C Program
The following C code shows how to use the InterBase XML API calls with the example database employee.ib to generate the emp.xml XML file.
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <ibase.h>
#include <ibxml_proto.h>
#define ERREXIT(status, rc) {isc_print_status(status); return rc;}
#define LASTLEN 20
#define FIRSTLEN 15
#define EXTLEN 4
/* This macro is used to declare structures representing SQL VARCHAR types */
#define SQL_VARCHAR(len) struct {short vary_length; char vary_string[(len)+1];}
int main (ARG(int, argc), ARG(char **, argv))
ARGLIST(int argc)
ARGLIST(char **argv)
{
char last_name[LASTLEN+2];
char first_name[FIRSTLEN+2];
char file_name [1024];
char phone_ext[EXTLEN + 2];
short flag0 = 0, flag1 = 0;
short flag2 = 0;
isc_stmt_handle stmt = NULL; /* statement handle */
isc_db_handle DB = NULL; /* database handle */
isc_tr_handle trans = NULL; /* transaction handle */
long status[20]; /* status vector */
XSQLDA ISC_FAR * sqlda;
long fetch_stat;
char empdb[128];
char *sel_str =
"SELECT last_name, first_name, phone_ext FROM phone_list \
WHERE location = 'Monterey' ORDER BY last_name, first_name;";
IB_XMLDA xmlda;
char version[] = "<?xml version = \"1.0\"?>\n
<!-- Example XML from IB (smistry) -->\n";
char employeedb[] = "Employee_DB";
char tbname[] = "PhoneList";
char rowname[] = "Employee";
FILE *xmlfptr;
xmlda.xmlda_status = 0;
xmlda.xmlda_version = 1;
xmlda.xmlda_header_tag = version;
xmlda.xmlda_database_tag = employeedb;
xmlda.xmlda_table_tag = tbname;
xmlda.xmlda_row_tag = rowname;
if (argc > 1)
strcpy(empdb, argv[1]);
else
strcpy(empdb, "D:\\smistry\\work\\IB6.5\\XML\\XML\\employee.ib");
if (isc_attach_database(status, 0, empdb, &DB, 0, NULL))
isc_print_status(status);
if (isc_start_transaction(status, &trans, 1, &DB, 0, NULL))
{
ERREXIT(status, 1)
}
/* Allocate an output SQLDA. */
sqlda = (XSQLDA ISC_FAR *) malloc(XSQLDA_LENGTH(3));
sqlda->sqln = 3;
sqlda->sqld = 3;
sqlda->version = 1;
/* Allocate a statement. */
if (isc_dsql_allocate_statement(status, &DB, &stmt))
{
ERREXIT(status, 1)
}
/* Prepare the statement. */
if (isc_dsql_prepare(status, &trans, &stmt, 0, sel_str, 1, sqlda))
{
ERREXIT(status, 1)
}
/*
* Although all three selected columns are of type varchar, the
* third field's type is changed and printed as type TEXT.
*/
sqlda->sqlvar[0].sqldata = (char *)&last_name;
sqlda->sqlvar[0].sqltype = SQL_TEXT + 1;
sqlda->sqlvar[0].sqlind = &flag0;
sqlda->sqlvar[1].sqldata = (char *)&first_name;
sqlda->sqlvar[1].sqltype = SQL_TEXT + 1;
sqlda->sqlvar[1].sqlind = &flag1;
sqlda->sqlvar[2].sqldata = (char ISC_FAR *) phone_ext;
sqlda->sqlvar[2].sqltype = SQL_TEXT + 1;
sqlda->sqlvar[2].sqlind = &flag2;
printf("\n%-20s %-15s %-10s\n\n", "LAST NAME", "FIRST NAME", "EXTENSION");
/* Execute the statement. */
if (isc_dsql_execute(status, &trans, &stmt, 1, NULL))
{
ERREXIT(status, 1)
}
/* Fetch and print the records.
* Status is 100 after the last row is fetched. */
/* Open a file for XML*/
/* replace from here if you want to use buffer call */
strcpy (file_name, "D:\\smistry\\work\\IB6.5\\XML\\XML\\emp.xml");
xmlda.xmlda_file_name = file_name;
while ((fetch_stat = isc_dsql_xml_fetch(status, &stmt, 1, sqlda, &xmlda)) == 0)/* the caller still has access to the sqlda variables */
/* the caller still has access to the sqlda variables */
{
printf("%-s", last_name);
printf("%-s", first_name);
printf("%s\n", phone_ext);
}
/* end replace here for buffer call */
if (fetch_stat != 100L)
{
ERREXIT(status, 1)
}
/* Free statement handle. */
if (isc_dsql_free_statement(status, &stmt, DSQL_close))
{
ERREXIT(status, 1)
}
if (isc_commit_transaction(status, &trans))
{
ERREXIT(status, 1)
}
if (isc_detach_database(status, &DB))
{
ERREXIT(status, 1)
}
free( sqlda);
return 0;
}
The XML Output
The preceding code generates the following XML file:
<?xml version="1.0"?>
<!-- Example XML from IB (smistry) -->
<Employee_DB>
<PhoneList>
<Employee>
<LAST_NAME>Bender</LAST_NAME>
<FIRST_NAME>Oliver H.</FIRST_NAME>
<PHONE_EXT>255</PHONE_EXT>
</Employee>
<Employee>
<LAST_NAME>Bishop</LAST_NAME>
<FIRST_NAME>Dana</FIRST_NAME>
<PHONE_EXT>290</PHONE_EXT>
</Employee>
<Employee>
<LAST_NAME>Brown</LAST_NAME>
<FIRST_NAME>Kelly</FIRST_NAME>
<PHONE_EXT>202</PHONE_EXT>
</Employee>
<Employee>
<LAST_NAME>Burbank</LAST_NAME>
<FIRST_NAME>Jennifer M.</FIRST_NAME>
<PHONE_EXT>289</PHONE_EXT>
</Employee>
<Employee>
<LAST_NAME>De Souza</LAST_NAME>
<FIRST_NAME>Roger</FIRST_NAME>
<PHONE_EXT>288</PHONE_EXT>
</Employee>
…
…
<Employee>
<LAST_NAME>Young</LAST_NAME>
<FIRST_NAME>Katherine</FIRST_NAME>
<PHONE_EXT>231</PHONE_EXT>
</Employee>
</PhoneList>
</Employee_DB>