Example PL/SQL code:This code creates an XML document by selecting data from the scott.emp table
Rem Test out the PL/SQL sidedeclare
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>
select xmlgen.getXML('select * from scott.emp') from dual;select xmlgen.getXML('select * from all_Tables where owner=''SCOTT''') from dual;
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 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;
beginxmlgen.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;
/