XML Features of Oracle 8i and 9i (2/7) - exploring XML

XML Features of Oracle 8i and 9i

Examples and Code Snippets

Let's create some code after looking at the big picture.

XSU (=XML-SQL) Command line usage

XSU can be used via the command line, or via APIs for java and for PL/SQL.

Example:

java OracleXML getXML -user "scott/tiger" "select * from emp"

Given a table emp with the following structure:

EMPNO NUMBER,
ENAME VARCHAR2(20),
JOB VARCHAR2(20),
MGR NUMBER,
HIREDATE DATE,
SAL NUMBER,
DEPTNO NUMBER

XSU will output XML in this format

<?xml version=Â’1.0Â’?>
<ROWSET>
  <ROW num="1">
    <EMPNO>7369</EMPNO>
    <ENAME>Smith</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>12/17/1980 0:0:0</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </ROW>
<!-- additional rows ... -->
</ROWSET>

Features:

• Simple transformations such as changing the tag name for the ROW or ROWSET element.
• Optional output of corresponding DTD.
• Oracle allows for fields to be defined as object types e.g a field DEPTADDR could be defined as of type STREET, CITY, STATE, ZIP. XSU preserves this structure.
• XSU command line usage only allows SELECT and INSERT. Using Java or PL/SQL API gives full functionality.

Conversely, to insert XML:

java OracleXML putXML -user "scott/tiger" -fileName "/tmp/temp.xml" "emp"

where in this case temp.xml would be a valid XML file such as the example above.

XSU can also be called from Java...

Produced by Michael Claßen

URL: http://www.webreference.com/xml/column81/2.html
Created: May 10, 2003
Revised: May 10, 2003