/*******************************************************************************
*                                                                       
*N  {xml_sample.c}  -- demonstrates how to create xml column and xml index,
*                            and how to perform xml editing and xml query
*   
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*P  Purpose:
*
*    This sample C program demonstrates how to: 
*		(1) create XML column;
*		(2) create XML tags;
*		(3) create XML index;
*		(4) edit XMLs;
*		(5) query with XPATH;
*    
*E                                                                 
*::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
* How to compile:: 
*	 Please refer section "Build C Samples" in the SDE sample
*
*E
*::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
* Usage:
*     xml_sample.c {server} {instance} {database} {user} {password} {keywords}  
*
*::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*X  Legalese:
*
* Copyright © 2007 ESRI
*
* All rights reserved under the copyright laws of the United States and 
* applicable international laws, treaties, and conventions.
*
* You may freely redistribute and use this sample code, with or without 
* modification, provided you include the original copyright notice and use 
* restrictions.  
*
* Disclaimer:  THE SAMPLE CODE IS PROVIDED "AS IS" AND ANY EXPRESS OR IMPLIED 
* WARRANTIES, INCLUDING THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS 
* FOR A PARTICULAR PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL ESRI OR 
* CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, 
* EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, 
* PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; 
* OR BUSINESS INTERRUPTION) SUSTAINED BY YOU OR A THIRD PARTY, HOWEVER CAUSED 
* AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR 
* TORT ARISING IN ANY WAY OUT OF THE USE OF THIS SAMPLE CODE, EVEN IF ADVISED 
* OF THE POSSIBILITY OF SUCH DAMAGE.
* 
* For additional information, contact:
* Environmental Systems Research Institute, Inc.
* Attn: Contracts and Legal Services Department
* 380 New York Street
* Redlands, California, 92373
* USA
* 
* email: contracts@esri.com
*
*E
****************************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <malloc.h>
#include "sdetype.h"
#include "sdeerno.h"


/* Local defines */
#define KEYCOL   "KEYCOL"
#define STRCOL   "STRCOL"
#define XMLCOL   "XMLCOL"
#define ROWIDCOL "ROWIDCOL"

#define DOC1 "<?xml version = \"1.0\" ?>\
<xmldocmetadata>\
  <Esri>\
    <gn>\
      <coverage>1000</coverage>\
      <suptheme>4</suptheme>\
      <suptheme>5</suptheme>\
      <suptheme>18</suptheme>\
      <featured>2000</featured>\
    </gn>\
  </Esri>\
</xmldocmetadata>"

#define DOC2 "<?xml version = \"1.0\" ?>\
<xmldocmetadata>\
  <Esri MetaID=\"2\">\
    <gn>\
      <coverage>5000</coverage>\
      <suptheme>400</suptheme>\
      <featured>6000</featured>\
    </gn>\
  </Esri>\
</xmldocmetadata>"

#define DOC3 "<?xml version = \"1.0\" ?>\
<xmldocmetadata>\
  <Esri MetaID=\"3\">\
    <gn>\
      <coverage >9000</coverage>\
      <suptheme>500</suptheme>\
      <suptheme>600</suptheme>\
      <featured>900</featured>\
    </gn>\
  </Esri>\
</xmldocmetadata>"

#define UPDATEDOC1 "<?xml version = \"1.0\" ?>\
<xmldocmetadata>\
  <Esri MetaID=\"1000\">\
    <gn>\
      <coverage>5500</coverage>\
      <suptheme>1500</suptheme>\
	  <featured>1500</featured>\
    </gn>\
  </Esri>\
</xmldocmetadata>"

#define UPDATEDOC2 "<?xml version = \"1.0\" ?>\
<xmldocmetadata>\
  <Esri MetaID=\"2000\">\
    <gn>\
      <coverage>1</coverage>\
      <suptheme>2</suptheme>\
      <featured>150</featured>\
    </gn>\
  </Esri>\
</xmldocmetadata>"

/* Function macros */
#define check_rc_return_on_failure(c,s,rc,f) \
	{if(rc!= SE_SUCCESS) {check_rc_(c,s,rc,f,__LINE__,__FILE__);return SE_FAILURE; }}

/* Local Function Prototypes */
void check_rc_(SE_CONNECTION Connection, SE_STREAM Stream, LONG rc, 
				 char *comment, LONG line_no, char* file_name);
static LONG S_create_table(SE_CONNECTION connection,CHAR *table,CHAR *keyword);
static LONG S_insert_rows(SE_CONNECTION  handle,const CHAR *table);
static LONG S_update_rows(SE_CONNECTION  handle,const CHAR *table,int num_of_row, LONG* keyvals);
static LONG S_query_table (SE_CONNECTION   connection, 
                          const CHAR      *table,
						  const CHAR	  *where,
                          const CHAR      *xpath);

LONG main(int argc, char *argv[]){
	CHAR   				*server, 
       	       			*user, 
						*passwd, 
						*database,
						*instance,
						*keyword;
	SE_CONNECTION 		conn;
	SE_ERROR 			error;
	LONG				result;
	CHAR				tablename[SE_MAX_TABLE_LEN+1];
	CHAR				**xml_tag_name;
    SE_XMLTAGINFO		xml_tag;
    CHAR				xml_index_name[SE_MAX_XML_INDEX_NAME_LEN+1];
    SE_XMLINDEXINFO		xml_index;
    SE_XMLCOLUMNINFO	xmlcol;
	LONG				i,tag_types[4];

	LONG			    updaterows,update_keyvals[10];
	CHAR				where[1024];
	CHAR				xpath[128];

	if(argc<7){
		printf("Usage %s <server> <instance> <database> <user> <password> <keyword>",argv[0]);
		exit(1);
	}

	server=argv[1];
	instance=argv[2];
	database=argv[3];
	user=argv[4];
	passwd=argv[5];
	keyword=argv[6];

	strcpy(tablename,"xml_samlple");

	//connect to sde server 
	result = SE_connection_create( server, instance, database, user, passwd, &error, &conn );
	check_rc_return_on_failure (conn,NULL,result,"SE_connection_create");
	printf("Connected to %s:%s as %s\n",server,instance,user);

	//Create the sample table
	printf("\nCreating sample table......\n");
	result=S_create_table(conn,tablename,keyword);
	if(result!=SE_SUCCESS)
		return result;

	//Init a xmlindex object
    result = SE_xmlindexinfo_create (&xml_index);
    check_rc_return_on_failure (conn,NULL,result,"SE_xmlindexinfo_create");
    result = SE_xmlindexinfo_set_type (xml_index, SE_XML_INDEX_DEFINITION);
	check_rc_return_on_failure (conn,NULL,result,"SE_xmlindexinfo_set_type");
	//prepare xtags
	xml_tag_name = (CHAR **) malloc (4 * sizeof(CHAR *));
	for(i=0;i<4;i++){
		xml_tag_name[i]=(CHAR *) malloc (SE_MAX_XML_TAG_NAME_LEN * sizeof(CHAR));
		tag_types[i]=SE_XML_INDEX_DOUBLE_TYPE;
	}
    strcpy(xml_tag_name[0],"/xmldocmetadata/Esri/gn/suptheme");
    strcpy(xml_tag_name[1],"/xmldocmetadata/Esri/gn/coverage");
    strcpy(xml_tag_name[2],"/xmldocmetadata/Esri/gn/featured");
    strcpy(xml_tag_name[3],"/xmldocmetadata/Esri@MetaID");
    result = SE_xmltaginfo_create (&xml_tag);
	//Add xmltags to the xmlindex object
    check_rc_return_on_failure (conn,NULL,result,"SE_xmltaginfo_create");
    for (i = 0; i < 4; i++)
    {
      result = SE_xmltaginfo_set_name (xml_tag, xml_tag_name[i]);
      check_rc_return_on_failure (conn,NULL,result,"SE_xmltaginfo_set_name");
      result = SE_xmltaginfo_set_data_type (xml_tag,tag_types[i]);
      check_rc_return_on_failure (conn,NULL,result,"SE_xmltaginfo_set_data_type");
      result = SE_xmlindexinfo_add_tag (xml_index, xml_tag);
      check_rc_return_on_failure (conn,NULL,result,"SE_xmlindexinfo_add_tag");
    }
	free(xml_tag_name);

	//Create a xml column
	printf("\nCreating XML column XMLCOL......\n");
    result = SE_xmlcolumninfo_create(&xmlcol);
	check_rc_return_on_failure (conn,NULL,result,"SE_xmlcolumninfo_create");
    result = SE_xmlcolumninfo_set_xml_column(xmlcol, tablename, XMLCOL);
    check_rc_return_on_failure (conn,NULL,result,"SE_xmlcolumninfo_set_xml_column");
    result = SE_xmlcolumninfo_set_creation_keyword (xmlcol,keyword);
    check_rc_return_on_failure (conn,NULL,result,"SE_xmlcolumninfo_set_creation_keyword");
    result = SE_xmlcolumn_create (conn, xmlcol);
    check_rc_return_on_failure (conn,NULL,result,"SE_xmlcolumn_create");

	//Create xml index on the xml column 
	printf("\nCreate index on XMLCOL......\n");
    strcpy (xml_index_name, "xmlindex");
    result = SE_xmlindexinfo_set_name (xml_index, xml_index_name);
    check_rc_return_on_failure (conn,NULL,result,"SE_xmlindexinfo_set_name");
    result = SE_xmlcolumninfo_set_index (xmlcol, xml_index);
	check_rc_return_on_failure (conn,NULL,result,"SE_xmlcolumninfo_set_index");
    result = SE_xmlcolumn_alter (conn, xmlcol);
	check_rc_return_on_failure (conn,NULL,result,"SE_xmlcolumn_alter");

	//Insert 30 rows into the table
	printf("\nInsert 30 rows into the sample table......\n");
	result=S_insert_rows(conn,tablename);
	if(result!=SE_SUCCESS)
		return result;

	sprintf(where,"KEYCOL > 10 AND KEYCOL< 20");
	strcpy(xpath,"/xmldocmetadata/Esri/gn[featured>1000]");

	//Perform query on the table without xpath
	printf("\nQuery the sample table without xpath.....\n");
	result=S_query_table(conn,tablename,where,NULL);

	//Perform query on the table with xpath
	printf("\nQuery the sample table with xpaths.....\n");
	result=S_query_table(conn,tablename,where,xpath);
	if(result!=SE_SUCCESS)
		return result;

	//Update the first 9 rows: row 10-19
	printf("\nUpdateing rows.....\n");
	updaterows=9;
	for(i=0;i<updaterows;i++)
		update_keyvals[i]=i+10;
	result=S_update_rows(conn,tablename,updaterows,update_keyvals);
	if(result!=SE_SUCCESS)
		return result;

	//Perform query on the table without xpath again 
	printf("\nQuery the sample table without xpath.....\n");
	result=S_query_table(conn,tablename,where,NULL);
	if(result!=SE_SUCCESS)
		return result;

	//Perform query on the table with xpath again
	printf("\nQuery the sample table with xpaths.....\n");
	result=S_query_table(conn,tablename,where,xpath);
	if(result!=SE_SUCCESS)
		return result;

	//Clean up
	SE_table_delete(conn,tablename);
    SE_xmlcolumninfo_free(xmlcol);
    SE_xmlindexinfo_free(xml_index);
	SE_xmltaginfo_free(xml_tag);
	SE_connection_free(conn);

	return SE_SUCCESS;
}


/***********************************************************************
*
*N  {S_create_table}  -  create a test table with provided specification
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Create a sample table with provided specification
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <connection>  ==  (SE_CONNECTION) The connection handle.
*     <table >      ==  (CHAR*) the table name
*     <keyword >    ==  (CHAR*) the keyword for table creation
*E
***********************************************************************/
static LONG S_create_table(SE_CONNECTION connection,CHAR *table,CHAR *keyword)
{
	SHORT               number_of_columns;
	SE_COLUMN_DEF       column_definitions[3];
	LONG                result;
	SE_REGINFO          registration;

	/* Prepare the column definitions */
	number_of_columns = 2;
	strcpy (column_definitions[0].column_name,KEYCOL);
	column_definitions[0].sde_type = SE_INT32_TYPE;
	column_definitions[0].size = 0;
	column_definitions[0].decimal_digits = 0;
	column_definitions[0].nulls_allowed = TRUE;

	strcpy(column_definitions[1].column_name,STRCOL);
	column_definitions[1].sde_type = SE_STRING_TYPE;
	column_definitions[1].size = 128;
	column_definitions[1].decimal_digits = -1;
	column_definitions[1].nulls_allowed = FALSE;

	//delete the table if already existed
	result = SE_table_delete(connection,table);
	if(result!=SE_TABLE_NOEXIST)
		check_rc_return_on_failure (connection,NULL,result,"SE_table_delete");
	//create the sample table
	result = SE_table_create (connection,table,number_of_columns,
							column_definitions,keyword);
	check_rc_return_on_failure (connection,NULL,result,"SE_table_create");

	/* Add an SDE maintained rowid to the table*/
	result = SE_reginfo_create (&registration);
	check_rc_return_on_failure (connection,NULL,result,"SE_reginfo_create");
	result = SE_registration_get_info (connection,table,registration);
	check_rc_return_on_failure (connection,NULL,result,"SE_registration_get_info");
	result = SE_reginfo_set_creation_keyword (registration,keyword);
	check_rc_return_on_failure (connection,NULL,result,"SE_reginfo_set_creation_keyword");
	check_rc_return_on_failure (connection,NULL,result,"SE_reginfo_set_description");
	result = SE_reginfo_set_rowid_column (registration,ROWIDCOL,
							   SE_REGISTRATION_ROW_ID_COLUMN_TYPE_SDE);
	check_rc_return_on_failure (connection,NULL,result,"SE_reginfo_set_rowid_column");
	result = SE_registration_alter (connection,registration);
	check_rc_return_on_failure (connection,NULL,result,"SE_registration_alter");
	SE_reginfo_free(registration);

	return(SE_SUCCESS);
}           


/***********************************************************************
*
*N  {S_insert_rows}  -  insert 30 rows into the table
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*   Insert rows into the sample table. 
*   The value of the XML column will be determice by the value of KEYCOL:
*      KEYCOL%3==0: XML=DOC1
*      KEYCOL%3==1: XML=DOC2
*      KEYCOL%3==2: XML=DOC3
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <handle>        ==  (SE_CONNECTION) The connection handle.
*     <table >        ==  (CHAR*) the layer name
*E
***********************************************************************/
static LONG S_insert_rows(SE_CONNECTION  handle,const CHAR *table)
{
	SE_STREAM       stream;
	LONG            result,row,i;
	SHORT           number_of_columns;
	CHAR            **base_columns;
	SE_INT32        key_val;
	CHAR			str_val[128];
	SE_XMLDOC		xmldoc;

	/*Init the column names for insertion*/
	number_of_columns = 3;
	base_columns = (CHAR **) malloc (number_of_columns * sizeof(CHAR *));
	for(i=0;i<number_of_columns;i++)
		base_columns[i]=(CHAR *) malloc (SE_MAX_COLUMN_LEN * sizeof(CHAR));
	strcpy(	base_columns[0],KEYCOL);
	strcpy(	base_columns[1],STRCOL);
	strcpy(	base_columns[2],XMLCOL);

	//Create a XMLDOC object
	result = SE_xmldoc_create (&xmldoc);
	check_rc_return_on_failure (handle,NULL,result,"SE_xmldoc_create");

	/*Init an insert stream*/
	result = SE_stream_create (handle,&stream);
	check_rc_return_on_failure (handle,NULL,result,"SE_stream_create");

	result = SE_stream_insert_table (stream,table,number_of_columns,
								   (const CHAR **)base_columns);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_insert_table");

	//Insert 30 rows into the sample table
	for (row = 0;row < 30 ;row++)
	{
		//prepare values for each column
		key_val=row; 
		sprintf(str_val,"Row %d",row+1);
		switch (key_val % 3)
		{
		  case 0:
			  result = SE_xmldoc_set_text (xmldoc, (LONG)(strlen (DOC1)+1)*sizeof(CHAR), DOC1);
			  check_rc_return_on_failure (handle, NULL, result, "SE_xmldoc_set_text");
			  break;
		  case 1:
			  result = SE_xmldoc_set_text (xmldoc, (LONG)(strlen (DOC2)+1)*sizeof(CHAR), DOC2);
			  check_rc_return_on_failure (handle, NULL, result, "SE_xmldoc_set_text");
			  break;
		  case 2:
			  result = SE_xmldoc_set_text (xmldoc, (LONG)(strlen (DOC3)+1)*sizeof(CHAR), DOC3);
			  check_rc_return_on_failure (handle, NULL, result, "SE_xmldoc_set_text");
			  break;
		}
		
		//set value for each column
		result = SE_stream_set_integer(stream,1,&key_val);
		check_rc_return_on_failure (NULL,stream,result,"SE_stream_set_integer");
		result = SE_stream_set_string(stream,2,str_val);
		check_rc_return_on_failure (NULL,stream,result,"SE_stream_set_string");
		result = SE_stream_set_xml(stream,3,xmldoc);
		check_rc_return_on_failure (NULL,stream,result,"SE_stream_set_xml");

		//Execute the insertion
		result = SE_stream_execute (stream);
		if(result!=SE_SUCCESS){
			SE_stream_free(stream);
			SE_xmldoc_free(xmldoc);
			free(base_columns);
			check_rc_return_on_failure (NULL,stream,result,"SE_stream_execute");
		}
	}
	
	//free resources
	result = SE_stream_free(stream);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_free");
	SE_xmldoc_free(xmldoc);
	for(i=0;i<number_of_columns;i++)
		free(base_columns[i]);
	free(base_columns);
	return(SE_SUCCESS);
}

/***********************************************************************
*
*N  {S_update_rows}  -  update rows into a table
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*   Update rows into the table. 
*
*   The value of the XML column will be determice by the value of KEYCOL:
*      KEYCOL%2==0: XML=UPDATEDOC1
*      KEYCOL%2==1: XML=UPDATEDOC2
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <handle>        ==  (SE_CONNECTION) The connection handle.
*     <table >        ==  (CHAR*) the layer name
*     <num_of_row>    ==  (LONG) number of rows to be inserted
*     <keyvals>       ==  (LONG*) specify rows to be updated
*E
***********************************************************************/
static LONG S_update_rows(SE_CONNECTION  handle,const CHAR *table,int num_of_row, LONG* keyvals)
{
	SE_STREAM       stream;
	LONG            result,row,i;
	SHORT           number_of_columns;
	CHAR            **base_columns;
	CHAR		    str_val[128];
	SE_XMLDOC		xmldoc;
	CHAR			where_clause[256];

	//Prepare column names for the update
	number_of_columns = 2;
	base_columns = (CHAR **) malloc (number_of_columns * sizeof(CHAR *));
	for(i=0;i<number_of_columns;i++)
		base_columns[i]=(CHAR *) malloc (SE_MAX_COLUMN_LEN * sizeof(CHAR));
	strcpy(	base_columns[0],STRCOL);
	strcpy(	base_columns[1],XMLCOL);
	
	//Create a XMLDOC object
	result = SE_xmldoc_create (&xmldoc);
	check_rc_return_on_failure (handle,NULL,result,"SE_xmldoc_create");

	//Prepara an update stream
	result = SE_stream_create (handle,&stream);
	check_rc_return_on_failure (handle,NULL,result,"SE_stream_create");

	for (row = 0;row < num_of_row ;row++)
	{
		//Prepare the where clauses for the update
		sprintf(where_clause,"%s = %d",KEYCOL,keyvals[row]);

		//Prepare values for each column
		sprintf(str_val,"Updated Row %d",keyvals[row]);
		if(keyvals[row]%2==0){
			  result = SE_xmldoc_set_text (xmldoc, (LONG)(strlen (UPDATEDOC1)+1)*sizeof(CHAR), UPDATEDOC1);
			  check_rc_return_on_failure (handle, NULL, result, "SE_xmldoc_set_text");
		}else{
			  result = SE_xmldoc_set_text (xmldoc, (LONG)(strlen (UPDATEDOC2)+1)*sizeof(CHAR), UPDATEDOC2);
			  check_rc_return_on_failure (handle, NULL, result, "SE_xmldoc_set_text");
		}

		result = SE_stream_update_table(stream, (const CHAR *)table, number_of_columns,
								   (const CHAR **)base_columns,(const CHAR*)where_clause);
		check_rc_return_on_failure (NULL,stream,result,"SE_stream_update_table");

		//set value for each column
		result = SE_stream_set_string(stream,1,str_val);
		check_rc_return_on_failure (NULL,stream,result,"SE_stream_set_string");
		result = SE_stream_set_xml(stream,2,xmldoc);
		check_rc_return_on_failure (NULL,stream,result,"SE_stream_set_xml");

		//Execute the update
		result = SE_stream_execute (stream);
		if( (result!=SE_SUCCESS) && (result!=SE_NO_ROWS_UPDATED)){
			SE_stream_free(stream);
			SE_xmldoc_free(xmldoc);
			free(base_columns);
			check_rc_return_on_failure (NULL,stream,result,"SE_stream_execute");
		}

		//Close the stream.
		result = SE_stream_close (stream,FALSE /* Don't reset. */);
		check_rc_return_on_failure (NULL,stream,result,"SE_stream_close");

	}//END FOR

	//free resources
	result = SE_stream_free(stream);
	check_rc_return_on_failure (NULL,stream,result,"SE_stream_free");
	SE_xmldoc_free(xmldoc);
	for(i=0;i<number_of_columns;i++)
		free(base_columns[i]);
	free(base_columns);

	return(SE_SUCCESS);
}

/***********************************************************************
*
*N  {S_query_table}  -  query the table
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*   Query the table with specified where clause and xpath
*
*E
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*A  Parameters:
*     <handle>        ==  (SE_CONNECTION) The connection handle.
*     <table >        ==  (CHAR*) the layer name
*     <where>         ==  (const CHAR*) the where clause
*     <xpath>         ==  (const CHAR*) the xpath
*E
***********************************************************************/
static LONG S_query_table(SE_CONNECTION   connection, 
                          const CHAR      *table,
						  const CHAR	  *where,
                          const CHAR      *xpath)
{
	SE_XMLDOC			xmldoc;
	LONG				result,keyval,i,fetched_rows=0,rowid;
	CHAR				**columns;
	SE_QUERYINFO		qinfo;
	SE_STREAM			query_stream;
	CHAR				orderby[128];
	SHORT				num_of_column;
	CHAR			    str_val[128];

	printf("\t%-11s: %s\n","WHERE",where);
	printf("\t%-11s: %s\n","XPATH",xpath==NULL?"NULL":xpath);

	//prepare columns to be queried
	num_of_column = 4;
	columns = (CHAR **) malloc (num_of_column * sizeof(CHAR *));
	for(i=0;i<num_of_column;i++)
		columns[i]=(CHAR *) malloc ((SE_MAX_COLUMN_LEN+1) * sizeof(CHAR));
	strcpy(	columns[0],ROWIDCOL);
	strcpy(	columns[1],KEYCOL);
	strcpy(	columns[2],STRCOL);
	strcpy( columns[3],XMLCOL);
	
	//Prepare a XMLDOC object
	result = SE_xmldoc_create (&xmldoc);
	check_rc_return_on_failure (connection,NULL,result,"SE_xmldoc_create");

	//Init a query stream
	result=SE_stream_create(connection, &query_stream);
	check_rc_return_on_failure (connection,NULL,result,"SE_stream_create");
	result = SE_queryinfo_create(&qinfo);
	check_rc_return_on_failure (connection,NULL,result,"SE_queryinfo_create");
	result = SE_queryinfo_set_tables (qinfo, 1, (const CHAR**)&table, NULL);
	check_rc_return_on_failure (connection,NULL,result,"SE_queryinfo_set_tables");
	result = SE_queryinfo_set_columns (qinfo, num_of_column, (const CHAR **)columns);
	check_rc_return_on_failure (connection,NULL,result,"SE_queryinfo_set_columns");
	result = SE_queryinfo_set_where_clause(qinfo,where);
	check_rc_return_on_failure (connection,NULL,result,"SE_queryinfo_set_where_clause");
	sprintf(orderby,"order by %s",KEYCOL);
	result = SE_queryinfo_set_by_clause(qinfo,orderby);
	check_rc_return_on_failure (connection,NULL,result,"SE_queryinfo_set_by_clause");

	//Apply the xpath as needed
	if (xpath){
		result = SE_queryinfo_set_xpath_constraint (qinfo, table,XMLCOL,xpath);
		check_rc_return_on_failure (NULL,query_stream,result,"SE_queryinfo_set_xpath_constraint");
	}

	//Execute the query
	result = SE_stream_query_with_info (query_stream,qinfo);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_query_with_info");
    result = SE_stream_execute (query_stream);
	check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_execute");

	//fetech rows
	while(result==SE_SUCCESS){
	   result = SE_stream_fetch (query_stream);
	   if(result!=SE_FINISHED){
		    check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_fetch");
		    fetched_rows++;
			//get values for each column in current row
			result = SE_stream_get_integer(query_stream,1,&rowid);
			check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_get_integer");
			result = SE_stream_get_integer(query_stream,2,&keyval);
			check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_get_integer");
			result = SE_stream_get_string(query_stream,3,str_val);
			check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_get_string");
			result = SE_stream_get_xml(query_stream,4,xmldoc);
			check_rc_return_on_failure (NULL,query_stream,result,"SE_stream_get_xml");
	   }
	}	
	printf("\t%-11s: %d rows\n","NUM_OF_ROWS",fetched_rows);

	//free resource
	SE_xmldoc_free (xmldoc);
	SE_queryinfo_free(qinfo);
	SE_stream_free(query_stream);
	for(i=0;i<num_of_column;i++)
		free(columns[i]);
	free(columns);

	return(SE_SUCCESS);
}



/***********************************************************************
*
*N  {check_rc_}  -  check the returned code
*
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
*
*P  Purpose:
*     Check returned sde error code and print the error messages.
***********************************************************************/
void check_rc_(SE_CONNECTION Connection, SE_STREAM Stream, LONG rc, 
				 char *comment, LONG line_no, char* file_name) 
{

	SE_ERROR	error;
	CHAR		error_string [SE_MAX_MESSAGE_LENGTH];
	LONG		temp_rc=SE_FAILURE;


	if ((rc != SE_SUCCESS) && (rc != SE_FINISHED)) 
	{
		error_string[0] = '\0';
		SE_error_get_string (rc, error_string);

	    printf ("%s encountered a %d error:\"%s\" \nat line %ld in file %s\n",comment, rc,error_string, line_no, file_name);

		/*Print extended error info, if any */
		if ((SE_DB_IO_ERROR == rc) | (SE_INVALID_WHERE == rc) | (SE_SSA_FUNCTION_ERROR == rc)) 
		{
			if (NULL != Stream) 
			{
				/* Assume this is a stream error */
				temp_rc = SE_stream_get_ext_error (Stream, &error);
			}else if (NULL != Connection) {
				/*Assume this is a connection error */
				temp_rc = SE_connection_get_ext_error (Connection, &error);
			}
			if (SE_SUCCESS == temp_rc)
			{
					printf("Extended error code: %d, extended error string:\n%s\n",
					error.ext_error, error.err_msg1);
			}
		} /*End SE_DB_IO_ERROR */

	} 
}