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.
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>
- 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
Created: May 10, 2003
Revised: May 10, 2003