Oracle Application Server provides a powerful utility to help you generate XML straight from data in your Oracle database — the XSQL processor. XML style sheets allows you to manipulate XML into various usable forms.
What is xml ?
xml stands for eXtensible Markup language.
“eXtensible” - Information in a neutral way,stored in a neutral form, independent of systems, devices and applications
“Markup” - Adding information to a document relating to its structure and/or format
“Language” - A standard methodology with formal syntax.
Reasons behind xml Popularity
Platform independent
Ease of data exchange
License-Free and well supported
Big players (Oracle and Microsoft) are backing the W3C XML standard
An XML Example depicting Purchase Order
Oracle xml components
The XML Developer’s Kits (XDK’s) include components that can be used to read, modify, and transform XML documents (via XSL-T).
Java
XML Parser and integrated XSL-T (XSL Transformation) Processor
XML documents to be transformed (converted) to formats such as HTML, WML, Voice, etc.
XML Class Generator
Generates Java classes based on XML document formats
Oracle Xml Parser
The Oracle XML parser is used to parse and translate XML documents.Using this parser, an XML document and its internal structure and data can be quickly translated to any other XML structure. In addition, this parser in conjunction with XSL-T can quickly transform an XML document to any applicable presentation format (I.e. WML, HTML, voice, etc.). The current version of the Oracle XML parser can be used both as a validating or non-validating parser. Validation is based on the use of XML document DTD’s.
The Oracle XML parser supports two common XML processing API’s:
DOM (document object model) interface:
The DOM interface is a W3C standard, and provides an industry-standard interface to read and parse an XML documents contents.
SAX (simple API for XML) interface:
This provides an API for working with XML documents.
Two packages are provided with the database to support the use of XML and xml stylesheets with PL/SQL .
DBMS_XMLQUERY : This package provides a group of procedures and functions that allow data to be extracted or read from database tables in XML format.
DBMS_XMLSAVE : This package provides a group of procedures and functions that allow the user to read the contents of an XML document and insert, update, or delete data in database tables based on content within that XML document.
An Pl/sql example of generating xml with a query by using the DMBS_XMLQUERY:
create or replace procedure test_xmlquery
is
ctxsql dbms_xmlquery.ctxtype;
clobvar1 clob;
begin
-- define the cursor
ctxsql := dbms_xmlquery.newcontext('SELECT * FROM SCOTT.EMP');
-- bring back the data
clobvar1 := dbms_xmlquery.getxml(ctxsql);
-- print the result
clobfieldprinting(clobvar1);
-- close the cursor
dbms_xmlquery.closecontext(ctxsql);
END;
/
The “clobfieldprinting” procedure (this example can be found in the Oracle documentation) is listed below:
create procedure clobfieldprinting (output IN OUT NOCOPY CLOB)
is
xmlvar varchar2(32767);
line varchar2(2000);
begin
– dbms_lob package is being used
xmlvar := dbms_lob.substr(output,32767);
loop
exit when xmlvar is null;
line := substr(xmlvar,1,instr(xmlvar,chr(10)));
–printing of the line
htp.prn(line);
xmlstr := substr(xmlvar,instr(xmlvar,chr(10))+1);
end loop;
end;
/
The output of the above code:
