The XML Extender provides two stored procedures, dxxGenXML() and dxxRetrieveXML(), to compose XML documents.
The stored procedure dxxGenXML() constructs XML documents using data that is stored in XML collection tables, which are specified by the <Xcollection> element in the DAD file. This stored procedure inserts each XML document as a row into a result table. You can also open a cursor on the result table and fetch the result set. The result table should be created by the application and always has one column of VARCHAR, CLOB, XMLVARCHAR, or XMLCLOB type. Additionally, if you specify the validation element in the DAD file as YES, the XML Extender adds the column DXX_VALID of INTEGER type, and inserts a value of 1 for a valid XML document and 0 for an invalid document.
The stored procedure dxxGenXML() also allows you to specify the maximum number of rows that are to be generated in the result table. This shortens processing time. The stored procedure returns the actual number of rows in the table, along with any return codes and messages.
The corresponding stored procedure for decomposition is dxxShredXML(); it also takes the DAD as the input parameter and does not require that the XML collection be enabled.
To compose an XML collection: dxxGenXML()
Embed a stored procedure call in your application using the following stored procedure declaration:
dxxGenXML(CLOB(100K) DAD, /* input */ char(UDB_SIZE) resultTabName, /* input */ integer overrideType, /* input */ varchar(1024) override, /* input */ integer maxRows, /* input */ integer numRows, /* output */ long returnCode, /* output */ varchar(1024) returnMsg) /* output */
See dxxGenXML() for the full syntax and examples.
Example: The following example composes an XML document:
#include "dxx.h" #include "dxxrc.h" EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; SQL TYPE is CLOB(100K) dad; /* DAD */ SQL TYPE is CLOB_FILE dadFile; /* dad file */ char result_tab[32]; /* name of the result table */ char override[2]; /* override, will set to NULL*/ short overrideType; /* defined in dxx.h */ short max_row; /* maximum number of rows */ short num_row; /* actual number of rows */ long returnCode; /* return error code */ char returnMsg[1024]; /* error message text */ short dad_ind; short rtab_ind; short ovtype_ind; short ov_inde; short maxrow_ind; short numrow_ind; short returnCode_ind; short returnMsg_ind; EXEC SQL END DECLARE SECTION; /* create table */ EXEC CREATE TABLE xml_order_tab (xmlorder XMLVarchar); /* read data from a file to a CLOB */ strcpy(dadfile.name,"c:\dxx\samples\dad\getstart_xcollection.dad"); dadfile.name_length = strlen("c:\dxx\samples\dad\getstart_xcollection.dad"); dadfile.file_options = SQL_FILE_READ; EXEC SQL VALUES (:dadfile) INTO :dad; strcpy(result_tab,"xml_order_tab"); override[0] = '\0'; overrideType = NO_OVERRIDE; max_row = 500; num_row = 0; returnCode = 0; msg_txt[0] = '\0'; collection_ind = 0; dad_ind = 0; rtab_ind = 0; ov_ind = -1; ovtype_ind = 0; maxrow_ind = 0; numrow_ind = -1; returnCode_ind = -1; returnMsg_ind = -1; /* Call the store procedure */ EXEC SQL CALL dxxGenXML(:dad:dad_ind; :result_tab:rtab_ind, :overrideType:ovtype_ind,:override:ov_ind, :max_row:maxrow_ind,:num_row:numrow_ind, :returnCode:returnCode_ind,:returnMsg:returnMsg_ind);
The result table after the stored procedure is called contains 250 rows because the SQL query specified in the DAD file generated 250 XML documents.
The stored procedure dxxRetrieveXML() works the same as the stored procedure dxxGenXML(), except that it takes the name of an enabled XML collection instead of a DAD file. When an XML collection is enabled, a DAD file is stored in the XML_USAGE table. Therefore, the XML Extender retrieves the DAD file and, from this point forward, dxxRetrieveXML() is the same as dxxGenXML().
dxxRetrieveXML() allows the same DAD file to be used for both composition and decomposition. This stored procedure also can be used for retrieving decomposed XML documents.
The corresponding stored procedure for decomposition is dxxInsertXML(); it also takes the name of an enabled XML collection.
To compose an XML collection: dxxRetrieveXML()
Embed a stored procedure call in your application using the following stored procedure declaration:
dxxRetrieveXML(char(UDB_SIZE) collectionName, /* input */ char(UDB_SIZE) resultTabName, /* input */ integer overrideType, /* input */ varchar(1024) override, /* input */ integer maxRows, /* input */ integer numRows, /* output */ long returnCode, /* output */ varchar(1024) returnMsg) /* output */
See dxxRetrieveXML() for full syntax and examples.
Example: The following example is of a call to dxxRetrieveXML(). It assumes that a result table is created with the name of XML_ORDER_TAB and it has one column of XMLVARCHAR type.
#include "dxx.h" #include "dxxrc.h" EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; char collection[32]; /* dad buffer */ char result_tab[32]; /* name of the result table */ char override[2]; /* override, will set to NULL*/ short overrideType; /* defined in dxx.h */ short max_row; /* maximum number of rows */ short num_row; /* actual number of rows */ long returnCode; /* return error code */ char returnMsg[1024]; /* error message text */ short dadbuf_ind; short rtab_ind; short ovtype_ind; short ov_inde; short maxrow_ind; short numrow_ind; short returnCode_ind; short returnMsg_ind; EXEC SQL END DECLARE SECTION; /* create table */ EXEC SQL CREATE TABLE xml_order_tab (xmlorder XMLVarchar); /* initialize host variable and indicators */ strcpy(collection,"sales_ord"); strcpy(result_tab,"xml_order_tab"); override[0] = '\0'; overrideType = NO_OVERRIDE; max_row = 500; num_row = 0; returnCode = 0; msg_txt[0] = '\0'; collection_ind = 0; rtab_ind = 0; ov_ind = -1; ovtype_ind = 0; maxrow_ind = 0; numrow_ind = -1; returnCode_ind = -1; returnMsg_ind = -1; /* Call the store procedure */ EXEC SQL CALL dxxRetrieve(:collection:collection_ind; :result_tab:rtab_ind, :overrideType:ovtype_ind,:override:ov_ind, :max_row:maxrow_ind,:num_row:numrow_ind, :returnCode:returnCode_ind,:returnMsg:returnMsg_ind);