Examples for using the xmlgen package



 

Example 1: Getting XML out as a CLOB and displaying it:

Example PL/SQL code:

This code creates an XML document by selecting data from the scott.emp table

Rem Test out the PL/SQL side

declare
 xmlString CLOB := null;
 -- Here we are reading 250 bytes at a time. We should be really reading a
 -- whole chunk. dbms_output.put_line can only accomodate 256 characters per line
 -- so we have this limitation.
 amount integer:= 255;
 position integer := 1;
 charString varchar2(255);

begin

  xmlgen.setRowTag('EMP_ROW');                     -- we want the row element to be named EMP_ROW.
  xmlgen.setRowsetTag('EMP_RESULTS');        -- we want the result document root to be EMP_RESULTS.
  xmlgen.setMaxRows(3);                                       -- limit the output to 3 rows.
  xmlgen.setskipRows(2);                                       -- skip the first two rows in the query before outputing results.
  xmlgen.useLowerCaseTagNames();                   -- set the tag names to be all in lower case.
  xmlgen.setErrorTag('ERROR_RESULT');           -- set the ERROR tag to be ERROR_RESULTS.
  xmlgen.setRowIdAttrName('ENO');                    -- set the id attribute in the ROW element to be ENO.
  xmlgen.setRowIdColumn('EMPNO');                 -- use the EMPNO column's value for the id attribute.
  xmlgen.useNullAttributeIndicator(false);          -- do not use the null indicator to indicate nullness.
  xmlgen.setStyleSheet('http://www.oracle.com/xsl');  -- attach the stylesheet PI to the result document.

  xmlString := xmlgen.getXML('select * from scott.emp ',1); -- This gets the XML out

  dbms_lob.open(xmlString,DBMS_LOB.LOB_READONLY);  -- Now open the lob data..
  loop
    dbms_lob.read(xmlString,amount,position,charString);      -- read the lob data
    dbms_output.put_line(charString);
    position := position + amount;
  end loop;

  exception
  when no_data_found then
    dbms_lob.close(xmlString);                                                   -- end of fetch, free the lob
    dbms_lob.freetemporary(xmlString);
    xmlgen.resetOptions;
  when others then
    xmlgen.resetOptions;
end;
/
 

The output from this looks like,

<?xml version="1.0"?>
<?xml-stylesheet href="http://www.oracle.com/style.xsl" type="text/xsl"?>
<!DOCTYPE emp_results [
<!ELEMENT emp_results (EMP_ROW)*>
<!ELEMENT EMP_ROW (empno, ename?, job?, mgr?, hiredate?, sal?, comm?, deptno?)>
<!ATTLIST EMP_ROW num CDATA #REQUIRED>
<!ELEMENT empno (#PCDATA)>
<!ELEMENT ename (#PCDATA)>
<!ELEMENT job (#PCDATA)>
<!ELEMENT mgr (#PCDATA)>
<!ELEMENT hiredate (#PCDATA)>
<!ELEMENT sal (#PCDATA)>
<!ELEMENT comm (#PCDATA)>
<!ELEMENT deptno (#PCDATA)>
]>
<emp_results>
  <EMP_ROW num="3">
    <empno>7521</empno>
    <ename>WARD</ename>
    <job>SALESMAN</job>
    <mgr>7698</mgr>
    <hiredate>1981-02-22 00:00:00.0</hiredate>
    <sal>1250</sal>
    <comm>500</comm>
    <deptno>30</deptno>
 </EMP_ROW>
</emp_results>
 


Example 2: Getting the results from a SQL query

         Example SQL code:
select xmlgen.getXML('select * from scott.emp') from dual;

select xmlgen.getXML('select * from all_Tables where owner=''SCOTT''') from dual;
 


Example 3: Inserting an XML document in a CLOB into the database

Example PL/SQL code:

Here we get the document using getXML into the clob and insert the clob back into the temp table. The temp table has the same structure as scott.emp.
The temp table is created as "create table scott.temp as select * from scott.emp"

Rem Test out the PL/SQL side
declare
 xmlString CLOB := null;
-- Here we are reading 250 bytes at a time. We should be really reading a
-- whole chunk. dbms_output.put_line can only accomodate 256 characters per line
-- so we have this limitation.
 amount integer:= 255;
 position integer := 1;
 charString varchar2(255);
 finalStr varchar2(4000) := null;
 rowsp integer;
begin

 -- the row element is called EMP_ROW
  xmlgen.setRowTag('EMP_ROW');

  -- use a lower case tag names.
  xmlgen.useLowerCaseTagNames();

  xmlgen.useNullAttributeIndicator(false);
  xmlgen.setStyleSheet('http://www.oracle.com/xsl');

  xmlString := xmlgen.getXML('select * from scott.emp ',0);

 -- open the lob
  dbms_lob.open(xmlString,DBMS_LOB.LOB_READONLY);

 -- before inserting , make sure that the case is ignored (since we generated everything with lower case)
 xmlgen.resetOptions;
 xmlgen.setRowTag('EMP_ROW');
 xmlgen.setIgnoreTagCase(xmlgen.IGNORE_CASE);

-- insert the XML document
 rowsp := xmlgen.insertXML('scott.temp',xmlString);
 dbms_output.put_line(' Rows processed = '|| TO_CHAR(rowsp));

 -- free the lob
 dbms_lob.close(xmlString);
 dbms_lob.freetemporary(xmlString);

exception
  when others then
      xmlgen.resetOptions;
     dbms_lob.close(xmlString);
     dbms_lob.freetemporary(xmlString);
end;
/
 


Example 4: Inserting an XML document in a string into the database

Example PL/SQL code:

Here we get the document using getXML into the clob and create a varchar2 buffer which holds the XML document. This is just for illustration. The temp table has the same structure as scott.emp.
The temp table is created as "create table scott.temp as select * from scott.emp"

Rem Test out the PL/SQL side
declare
 xmlString CLOB := null;
-- Here we are reading 250 bytes at a time. We should be really reading a
-- whole chunk. dbms_output.put_line can only accomodate 256 characters per line
-- so we have this limitation.
 amount integer:= 255;
 position integer := 1;
 charString varchar2(255);
 finalStr varchar2(4000) := null;
 rowsp integer;
begin

  xmlgen.setRowTag('EMP_ROW');
  xmlgen.setRowsetTag('EMP_RESULTS');
  xmlgen.setMaxRows(2);
  xmlgen.setskipRows(2);

 -- get the XML CLOB out..
  xmlString := xmlgen.getXML('select * from scott.emp ',0);

 begin
     -- we are now creating a varchar2 string from the clob.
     dbms_lob.open(xmlString,DBMS_LOB.LOB_READONLY);
     loop
         dbms_lob.read(xmlString,amount,position,charString);

         if finalStr is not null then
           finalStr := finalStr || charString;
        else
           finalStr := charString;
        end if;
        position := position + amount;

    end loop;

   exception
     when no_data_found then
       dbms_lob.close(xmlString);
       dbms_lob.freetemporary(xmlString);
   end;

   -- insert the varchar string.!
  rowsp := xmlgen.insertXML('scott.temp',finalStr);

 dbms_output.put_line('INSERT DONE '||TO_CHAR(rowsp));
 xmlgen.resetOptions;
end;
/