Solved

How to load an XML document in Oracle 10g xmlTable

Posted on 2009-05-14
10
1,767 Views
Last Modified: 2012-05-07
Hi experts,
                   My task is to process data present in an XML file. For this I need to load this XML document in xmlTable type Oracle table which is placed on a unix server.

                   Can someone please guide me with the steps
0
Comment
Question by:aman0711
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
10 Comments
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 200 total points
ID: 24385018
1) Easiest method that I use is Toad, load into a clob with the file upload support. If you browse the table in schema browser, clob (and probably xmlTable with new versions) will have a button to upload into the cell.

2) Use sqlldr to load from flat file

See Oracle docs: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_loading.htm#sthref1368
0
 
LVL 48

Accepted Solution

by:
schwertner earned 300 total points
ID: 24385029
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 13 14:56:19 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect xml/xml@test

SQL> CREATE TABLE empleados (empid   NUMBER PRIMARY KEY,
  2                          empname VARCHAR2(30),
  3                          empjob  VARCHAR2(30),
  4                          empsal  NUMBER);

Table created.

SQL> create or replace procedure InsertXML(xmlDoc IN VARCHAR2, tableName IN VARCHAR2) is
  2     insCtx DBMS_XMLSave.ctxType;
  3     rows number;
  4   begin
  5      insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle
  6      rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document
  7      dbms_output.put_line(to_char(rows) || ' rows inserted');
  8      DBMS_XMLSave.closeContext(insCtx);            -- this closes the handle
  9  end;
 10  /

Procedure created.
SQL> exec InsertXML('<?xml version="1.0"?><ROWSET><ROW num="1"><EMPID>10</EMPID><EMPNAME>Perry Smith
</EMPNAME><EMPJOB>Manager</EMPJOB><EMPSAL>800</EMPSAL></ROW><ROW num="1"><EMPID>20</EMPID><EMPNAME>J
ohn Calvach</EMPNAME><EMPJOB>Principal Support Consultant</EMPJOB><EMPSAL>900</EMPSAL></ROW><ROW num
="1"><EMPID>30</EMPID><EMPNAME>Louis Bald</EMPNAME><EMPJOB>Technical Specialist</EMPJOB><EMPSAL>400<
/EMPSAL></ROW><ROW num="1"><EMPID>40</EMPID><EMPNAME>Anthony Flowers</EMPNAME><EMPJOB>Technical Team
 Leader</EMPJOB><EMPSAL>500</EMPSAL></ROW><ROW num="1"><EMPID>50</EMPID><EMPNAME>George Monk</EMPNAM
E><EMPJOB>Support Consultant</EMPJOB><EMPSAL>200</EMPSAL></ROW></ROWSET>','empleados');

PL/SQL procedure successfully completed.

SQL> select * from empleados;

     EMPID EMPNAME                        EMPJOB
---------- ------------------------------ ------------------------------
    EMPSAL
----------
        10 Perry Smith                    Manager
       800

        20 John Calvach                   Principal Support Consultant
       900

        30 Louis Bald                     Technical Specialist
       400


     EMPID EMPNAME                        EMPJOB
---------- ------------------------------ ------------------------------
    EMPSAL
----------
        40 Anthony Flowers                Technical Team Leader
       500

        50 George Monk                    Support Consultant
       200


SQL>



SQL> select SYS_XMLGEN(empid) FROM empleados;

SYS_XMLGEN(EMPID)
---------------------------------------------------------
<?xml version="1.0"?>
<EMPID>10</EMPID>

<?xml version="1.0"?>
<EMPID>20</EMPID>

<?xml version="1.0"?>
<EMPID>30</EMPID>

<?xml version="1.0"?>
<EMPID>40</EMPID>

SYS_XMLGEN(EMPID)

<?xml version="1.0"?>
<EMPID>50</EMPID>


SQL> select SYS_XMLAGG(SYS_XMLGEN(empid)) FROM empleados;
<?xml version="1.0"?>
<ROWSET>
<EMPID>10</EMPID>
<EMPID>20</EMPID>
<EMPID>30</EM


-- Create directory object mapped to physical directory which contains the XML file. 
-- Procedure owner should have read permission for the directory and the XML file. 
 
CREATE DIRECTORY XML_DIR AS '/tmp'; 
 
-- Create a table containing a BFILE and insert a row for the XML file. 
 
CREATE TABLE XML_TEMP (key NUMBER, f_lob BFILE); 
 
INSERT INTO XML_TEMP VALUES (1,BFILENAME('XML_DIR','example.xml')); 
 
-- Create table into which the XML document has to be loaded. 
-- Note that the column names of the table should match the XML tags. 
 
CREATE TABLE XML_DOC ( 
DOCID                             VARCHAR2(10), 
SUBJECT                        VARCHAR2(100), 
TYPE                                VARCHAR2(20), 
CONTENT_TYPE             VARCHAR2(20), 
STATUS                           VARCHAR2(20), 
CREATION_DATE           VARCHAR2(15), 
LAST_REVISION_DATE VARCHAR2(15), 
LANGUAGE                     VARCHAR2(10) 
); 
 
 
 
Program 
------- 
 
 
 
CREATE OR REPLACE PROCEDURE loadxml AS 
  fil    BFILE; 
  buffer RAW(32767); 
  len    INTEGER; 
  insrow INTEGER; 
BEGIN 
 
  SELECT f_lob INTO fil FROM xml_temp WHERE key = 1; 
 
  DBMS_LOB.FILEOPEN(fil,DBMS_LOB.FILE_READONLY); 
  len := DBMS_LOB.GETLENGTH(fil); 
  DBMS_LOB.READ(fil,len,1,buffer); 
  xmlgen.resetOptions; 
  insrow := xmlgen.insertXML('xml_doc',UTL_RAW.CAST_TO_VARCHAR2(buffer)); 
  DBMS_OUTPUT.PUT_LINE(insrow); 
 
  IF DBMS_LOB.FILEISOPEN(fil) = 1 THEN 
    DBMS_LOB.FILECLOSE(fil); 
  END IF; 
 
EXCEPTION 
  WHEN OTHERS THEN 
  DBMS_OUTPUT.PUT_LINE('In Exception'); 
  DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE)); 
  IF DBMS_LOB.FILEISOPEN(fil) = 1 THEN 
    DBMS_LOB.FILECLOSE(fil); 
  END IF; 
end; 
/ 
 
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - - 
 
Sample Output 
-------------- 
 
Issue the following statements in SQL*Plus to see the results of the program: 
 
SQL> set serveroutput on 
SQL> exec loadxml; 
 
SQL> column subject format a20 truncated 
SQL> select * from xml_doc; 
 
 
DOCID            SUBJECT                             TYPE                        
              CONTENT_TYPE                  STATUS                                CREATION_DATE    LAST_REVISION_D  LANGUAGE 
---------- -------------------- -------------------- -------------------- -------------------- 
--------------- --------------- ---------- 
 91739.1          MTS: ORA-29855, DRG       PROBLEM                             TEXT/PLAIN 
                        PUBLISHED                          14-DEC-1999           15-JUN-2000            USAENG 
 

Open in new window

0
 
LVL 10

Author Comment

by:aman0711
ID: 24385254
Thanks schwertner, mrjoltcola,

                                  Will try my hands on this :)
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 10

Author Comment

by:aman0711
ID: 24569080
I am sorry, some how I got real busy. I wanted to assign points to schwertner and mrjoltcola.

My apologies folks.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24569112
Hi aman0711,

Feel free to go ahead and close / score the question yourself as you see fit.
0
 
LVL 10

Author Comment

by:aman0711
ID: 24569123
I cant see the option to close this question now... :(
0
 
LVL 10

Author Comment

by:aman0711
ID: 24572061
I want to assign points for this question.
0
 
LVL 10

Author Closing Comment

by:aman0711
ID: 31581442
Thank you folks :-)
Sorry for abandoning this question :(
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

705 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