Using the XML-SQL Utility for PL/SQL

The XML-SQL Utility PL/SQL API reflects the Java API in the generation and storage. The DBMS_XMLQuery and DBMS_XMLSave are the two packages that reflect the functions in the java classes - OracleXMLQuery and OracleXMLSave.

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 with DBMS_XMLQuery

Generating XML results in a CLOB that contains the XML document. The steps involved in using the generation engine follow:

1. Create a context handle by calling the DBMS_XMLQuery.getCtx function and supplying it the query (either as a CLOB or a VARCHAR2)

2. Bind possible values to the query using the DBMS_XMLQuery.bind function. The binds work by binding a name to the position. For example, the query can be something like, select * from emp where empno = :EMPNO_VAR. Here the user binds the value for the EMPNO_VAR using the setBindValue function.

3. Set optional arguments like the ROW tag name, the ROWSET tag name or the number of rows to fetch etc.

4. Fetch the XML as a CLOB using the getXML() functions. The getXML can be called to generate the XML with or without a DTD.

5. Close the context.

Here are some examples that use this PL/SQL package.

XSU Example 13: Generating XML From Simple Queries (PL/SQL)

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;
/

XSU Example 13a: Printing CLOB to Output Buffer

The 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.

The 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;
/

XSU Example 14: Changing ROW and ROWSET Tag Names (PL/SQL)

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, setRowTagName and 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.

XSU Example 15: Paginating Results Using setMaxRows() and setSkipRows()

The results from the query generation can be paginated by using the setMaxRows and 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;
/