Solved

oracle data to xml format

Posted on 2004-04-28
4
2,006 Views
Last Modified: 2009-08-24
hi, I am new to oracle. I want to know if there is any program can convert my oracle data to xml format, so I can use html to display it. or any other suggestion that I should use to display my oracle data? like php...etc. cuz' I dont know much about php. Other than using program to convert data, anyone can tell me how to convert data by ourselves, or post a tutorial link is very helpful. Thanks alot
0
Comment
Question by:shy513
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
riazpk earned 50 total points
ID: 10940315
From http://asktom.oracle.com/pls/ask/f?p=4950:8:2340990633216432615::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4061080732051,


The Oracle XML Developer's Kits (XDK) is fully supported in Oracle8i 8.1.7, so
that's where you should start.  A version of the XDK is normally installed in
the 8.1.7 database, but you can get the latest copy of the FREE APIs on Oracle
TechNet (requires a free registration), here:  
http://otn.oracle.com/tech/xml/content.html
 On the left hand side of
the page, there is an index titled "XML".  Click on the first link that reads
"XML Developer's Kits" and you will find links to all the downloads for the
various APIs on the various supported platforms.

You've asked a large number of questions that, in order to answer, could easily
consume a large chapter of a book or even a book on its own if the person
answering was somewhat verbose.  I'll try to give some brief examples along with
links to more information where appropriate.


----------------------------
GENERATING XML FROM ORACLE &
DISPLAYING XML FM SQL*PLUS

Generating XML from the database can be performed in a large number of ways.  
We'll look at the things you asked from a SQL/PLSQL approach, with the
understanding that almost everything we discuss can be accomplished using C, C++
and Java as well.  After you've installed the XDK, use this example to generate
some XML from SCOTT.EMP:

SQL> set autoprint on
SQL> set long 100000
SQL> set linesize 100000
SQL> set longchunksize 100000
SQL> var g_clob clob
SQL> declare
  2    l_ctx  dbms_xmlquery.ctxHandle;
  3    l_clob clob;
  4  begin
  5    l_ctx := dbms_xmlquery.newContext('select * from scott.emp');
  6    dbms_lob.createtemporary(:g_clob,true,dbms_lob.session);
  7    :g_clob := dbms_xmlquery.getXml(l_ctx);
  8  end;
  9  /

PL/SQL procedure successfully completed.


G_CLOB
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>17-DEC-80</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7499</EMPNO>
  <ENAME>ALLEN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>20-FEB-81</HIREDATE>
  <SAL>1600</SAL>
  <COMM>300</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
 . . .
</ROWSET>

SQL> set linesize 100
SQL> set longchunksize 80
SQL> set autoprint off

Two quick things here.  First, you'll notice I set my linesize, longchunksize
and long settings to 100000.  This was just to get formatting in SQL*Plus.  You
don't need this unless you want to see data in SQL*Plus.


---------------------------------
GENERATING DTDs FROM THE DATABASE

Using the above example, a quick change from DBMS_XMLQUERY.GETXML to
DBMS_XMLQUERY.GETDTD will get your DTD for the query:

CHANGE...
  7    :g_clob := dbms_xmlquery.getXml(l_ctx);
TO...
  7    :g_clob := dbms_xmlquery.getdtd(l_ctx);
RESULTS IN...

G_CLOB
--------------------------------------------------------------------------------
<!DOCTYPE ROWSET [
<!ELEMENT ROWSET (ROW)*>
<!ELEMENT ROW (EMPNO, ENAME?, JOB?, MGR?, HIREDATE?, SAL
?, COMM?, DEPTNO?)>
<!ATTLIST ROW num CDATA #REQUIRED>
<!ELEMENT EMPNO (#PCDATA)>
<!ELEMENT ENAME (#
PCDATA)>
<!ELEMENT JOB (#PCDATA)>
<!ELEMENT MGR (#PCDATA)>
<!ELEMENT HIREDATE (#PCDATA)>
<!ELEMENT S
AL (#PCDATA)>
<!ELEMENT COMM (#PCDATA)>
<!ELEMENT DEPTNO (#PCDATA)>
]>

-------------------------------------------
SAVING XML INTO ORACLE (TABLE OR OTHERWISE)

There are two ways to do this in Oracle8i 8.1.7.  You can either store native
XML in a CLOB/NCLOB/BLOB column, or you can have Oracle serialize XML into a
relational table.  Saving data into a CLOB column is simple using the above
example.  You'd simply insert the resulting CLOB value into a table.  Even
better, you could insert an empty_clob() into a table, returning the value into
a local variable.  Then just write to the local variable i.e. -

SQL> create table xmldocs(
  2    docname varchar2(50),
  3    xmldoc  clob)
  4  /

Table created.

SQL> declare
  2    l_ctx  dbms_xmlquery.ctxHandle;
  3    l_clob clob;
  4  begin
  5    l_ctx := dbms_xmlquery.newContext('select * from scott.emp');
  6    insert into xmldocs values ('EMP TABLE', empty_clob())
  7    returning xmldoc into l_clob;
  8    dbms_xmlquery.getXml(l_ctx, l_clob);
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> col docname for a20
SQL> col xmldoc  for a60
SQL> select *
  2    from xmldocs
  3  /

DOCNAME              XMLDOC
-------------------- -----------------------------------------------------------
EMP TABLE            <?xml version="1.0"?>
                     <ROWSET>
                      <ROW>
                       <EMPNO>7369</EMPNO>
                       <ENAME>SMITH</ENAME>
                       <JOB>CLERK</JOB>
                       <MGR>7902</MGR>
                       <HIREDATE>17-DEC-80</HIREDATE>
                       <SAL>800</SAL>
                       <DEPTNO>20</DEPTNO>
                      </ROW>
                      <ROW>
                       <EMPNO>7499</EMPNO>
                       <ENAME>ALLEN</ENAME>
                       <JOB>SALESMAN</JOB>
                       <MGR>7698</MGR>
                       <HIREDATE>20-FEB-81</HIREDATE>
                       <SAL>1600</SAL>
                       <COMM>300</COMM>
                       <DEPTNO>30</DEPTNO>
                      </ROW>
                      . . .
                     </ROWSET>

SQL> set echo off

For serializing this XML into a table (for example, you're getting this XML
document off the Internet and you want to store it in a relational EMP table),
you would again use the XML SQL Utility to accomplish this.  In the following
example, we use the XSU to save an XML document into a table:

SQL> create table myemp
  2  as select *
  3       from scott.emp
  4      where 1 = 0
  5  /
Table created.

SQL> select *
  2    from myemp
  3  /
no rows selected

SQL> declare
  2    l_clob  clob :=
  3  '<?xml version = "1.0"?>
  4  <ROWSET>
  5     <ROW num="1">
  6        <EMPNO>7369</EMPNO>
  7        <ENAME>SEANDILLON</ENAME>
  8        <JOB>DBDUDE</JOB>
  9        <MGR>7902</MGR>
 10        <HIREDATE>12/17/1980 0:0:0</HIREDATE>
 11        <SAL>800</SAL>
 12        <DEPTNO>20</DEPTNO>
 13     </ROW>
 14     <ROW num="2">
 15        <EMPNO>7499</EMPNO>
 16        <ENAME>TOMKYTE</ENAME>
 17        <JOB>DBDUDE</JOB>
 18        <MGR>7698</MGR>
 19        <HIREDATE>2/20/1981 0:0:0</HIREDATE>
 20        <SAL>1600</SAL>
 21        <COMM>300</COMM>
 22        <DEPTNO>30</DEPTNO>
 23     </ROW>
 24     <ROW num="3">
 25        <EMPNO>7521</EMPNO>
 26        <ENAME>MICKEYMOUS</ENAME>
 27        <JOB>RAT</JOB>
 28        <MGR>7698</MGR>
 29        <HIREDATE>2/22/1981 0:0:0</HIREDATE>
 30        <SAL>1250</SAL>
 31        <COMM>500</COMM>
 32        <DEPTNO>30</DEPTNO>
 33     </ROW>
 34  </ROWSET>';
 35  
 36    l_ctx   dbms_xmlsave.ctxType;
 37    l_rows  number;
 38  begin
 39    l_ctx := dbms_xmlsave.newContext('MYEMP');
 40    l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
 41    dbms_xmlsave.closeContext(l_ctx);
 42    dbms_output.put_line(l_rows || ' rows inserted...');
 43  end insert_xml_emps;
 44  /
3 rows inserted...
PL/SQL procedure successfully completed.

SQL> select *
  2    from myemp
  3  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
      7369 SEANDILLON DBDUDE          7902 17-DEC-80        800                  
  20
      7499 TOMKYTE    DBDUDE          7698 20-FEB-81       1600        300      
  30
      7521 MICKEYMOUS RAT             7698 22-FEB-81       1250        500      
  30

SQL> drop table myemp
  2  /

Table dropped.


-----------------------------
HOW TO USE IT ONCE IT IS DONE

You name it.  XML has a variety of usages, but don't use XML for XMLs sake.  If
you have a valid requirement for the storage and use of XML, great.  (Such as
you are exchanging XML data with 3d parties, you need an audit trail of the data
transfered, the data you are storing is totally unstructured and does not lend
itself to relational storage, etc.).  

<SOAPBOX>
There are many people in the industry that feel using XML is cool, so you should
just do it.  Personally, I think XML is a little overhyped and although useful
as a common denominator in data formats, you should have a firm grip on the
reason you MUST store/use XML before you head down that road.
</SOAPBOX>


----------------------------------------
READING XML DOCUMENTS FROM THE HARD DISK

This can be accomplished in a variety of ways.  In 8.1.7, I used Java Stored
Procedures from inside the database.  You just need to learn some of the Java
APIs for the XDK for creating XML documents, and the Java APIs for reading files
from your disk.


-------------
WHAT IS XPATH

Before I go into the Sean Dillon version of what XPath is, let me refer you to
the abundance of resources on the Internet that can answer that question in a
much more elegant, explanative COMPLETE manner :-).  Oracle has a few links that
I think you would benefit greatly from:

AN XML PRIMER:
http://download-east.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a88895/appaxml.htm#621642
Oracle XML Developer's Kits Primer (don't be deterred from the "9i-esque" of the
XDK.  It's backwards compatible w/ 8.1.7):
http://download-east.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a88894/toc.htm

There are a few books out there I highly recommend for IT folks looking to use
XML with Oracle.  The best yet is Steve Muench's "Building Oracle XML
Applications" which you can find here:
http://www.oreilly.com/catalog/orxmlapp/


------------------------------------------
FINDING DATA YOU DELETED THEN COMMITTED...

Well, in Oracle9i I would tell you to use Flashback Query.  This new feature of
9i allows you to reconstruct the data in your database as it existed some time
in the past.  Since you are still running on 8.1.7 :-), your only options are
recovery fm backup or using Log Miner (recommended route).  DBMS_LOGMINER is a
supplied package in the database that you can use to do things like find out
when a table was 'accidentally' dropped, perform some type of auditing on a
table after the actions have been committed, or 'undo' a transaction (your
case).  I'm not going to get into all the details of using Log Miner here, but
instead will refer you to the documentation.  
http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/appdev.901/a89852/dbms_log.htm#73729
Keep in mind, this is 9i documentation so there are going to be differences
between 9i and 8.1.7.  You should refer to the documentation that came as a part
of your 8.1.7 database.  Another GREAT place you will find information about Log
Miner is in Tom's (of Ask Tom ;-) book, Expert One-on-One Oracle.  
http://www.amazon.com/exec/obidos/ASIN/1861004826
He goes into great depth in practical examples of using Log Miner.

Hope that helps!  

0
 

Expert Comment

by:ankhan100599
ID: 10957300
In loading xml file to database, is there any way to point the clob declaration to a file rather than to the bit of xml data ?

I mean, if the file was larger than a few records, say a few hundred thousand records, what would be the best way to load it ?
0
 
LVL 13

Expert Comment

by:riazpk
ID: 10959645
@@ankhan

Please don't ask questions here...only the author can post here comment as Question.
Sumit a new question.
0
 

Author Comment

by:shy513
ID: 11014992
thanks riazpk, it really helps. I really wanna know the answer of the question that ankhan asked, but I think I am gonna post a new question to ask. Again, thx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SSN Format in Oracle 2 59
Oracle Database Upgrade 13 41
Oracle Subquery bad Join 11 42
Best RAID for a BDD Oracle 4 16
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now