|Oracle8i Application Developer's Guide - XML
Release 3 (8.1.7)
Part Number A86030-01
Using XML-SQL Utility (XSU), 18 of 26
The XML-SQL Utility PL/SQL API reflects the Java API in the generation and storage. The
DBMS_XMLSave are the two packages that reflect the functions in the java classes - OracleXMLQuery and
Both these packages have a context handle associated with them. Create a context by calling one of the constructor-like functions to get the handle and then use the handle in all subsequent calls.
Generating XML results in a CLOB that contains the XML document. The steps involved in using the generation engine follow:
Here are some examples that use this PL/SQL package.
In this example, we will try to select rows from the emp table and get a XML document as a CLOB. We first get the context handle by passing in a query and then call the
getXMLClob routine to get the CLOB value. The document will be in the same encoding as that of the database character set.
declare queryCtx DBMS_XMLquery.ctxType; result CLOB; begin -- set up the query context...! queryCtx := DBMS_XMLQuery.newContext('select * from emp'); -- get the result..! result := DBMS_XMLQuery.getXML(queryCtx); -- Now you can use the result to put it in tables/send as messages.. printClobOut(result); DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle.. end; /
printClobOut() is a simple procedure that prints the CLOB to the output buffer. If you run this PL/SQL code in SQL*Plus, you will see the result of the CLOB being printed out to screen. Set the
serveroutput to on in order to see the results.
printClobOut is shown below:-
/CREATE OR REPLACE PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is xmlstr varchar2(32767); line varchar2(2000); begin xmlstr := dbms_lob.SUBSTR(result,32767); loop exit when xmlstr is null; line := substr(xmlstr,1,instr(xmlstr,chr(10))-1); dbms_output.put_line('| '||line); xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1); end loop; end; /
The PL/SQL APIs also provide the ability to change the ROW and the ROWSET tag names. These are the default names that are put around each row of the result and around the whole document respectively. The procedures,
setRowSetTagName accomplish this as shown below:
--Setting the ROW tag names declare queryCtx DBMS_XMLQuery.ctxType; result CLOB; begin -- set the query context. queryCtx := DBMS_XMLQuery.newContext('select * from emp'); DBMS_XMLQuery.setRowTag(queryCtx,'EMP'); -- sets the row tag name DBMS_XMLQuery.setRowSetTag(queryCtx,'EMPSET'); -- sets rowset tag name result := DBMS_XMLQuery.getXML(queryCtx); -- get the result printClobOut(result); -- print the result..! DBMS_XMLQuery.closeContext(queryCtx); -- close the query handle; end; /
The resulting XML document has an EMPSET document element and each row separated using the EMP tag.
The results from the query generation can be paginated by using the
setSkipRows functions. The former sets the maximum number of rows to be converted to XML. This is relative to the current row position from which the last result was generated. The skipRows parameter specifies the number of rows to skip before converting the row values to XML. For example, to skip the first 3 rows of the emp table and then print out the rest of the rows 10 at a time, we can set the skipRows to 3 for the first batch of 10 rows and then set skipRows to 0 for the rest of the batches.
As in the case of the XML-SQL Utility Java API, call the keepObjectOpen() function to make sure that the state is maintained between fetches. The default behavior is to close the state after a fetch is done. In the case of multiple fetches, we need to figure out when there are no more rows to fetch. This can be done by setting the
setRaiseNoRowsException(). This causes an exception to be raised if no rows are written to the CLOB. This can be caught and used as the termination condition.
-- Pagination of results declare queryCtx DBMS_XMLquery.ctxType; result CLOB; begin -- set up the query context...! queryCtx := DBMS_XMLQuery.newContext('select * from emp'); DBMS_XMLQuery.setSkipRows(queryCtx,3); -- set the number of rows to skip DBMS_XMLQuery.setMaxRows(queryCtx,10); -- set the max number of rows per fetch result := DBMS_XMLQuery.getXML(queryCtx); -- get the first result..! printClobOut(result); -- print the result out.. This is you own routine..! DBMS_XMLQuery.setSkipRows(queryCtx,0); -- from now don't skip any more rows..! DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,true); -- raise no rows exception..! begin loop -- loop forever..! result := DBMS_XMLQuery.getXML(queryCtx); -- get the next batch printClobOut(result); -- print the next batch of 10 rows..! end loop; exception when others then -- dbms_output.put_line(sqlerrm); null; -- termination condition, nothing to do; end; DBMS_XMLQuery.closeContext(queryCtx); -- close the handle..! end; /