?
Solved

How to load an XML document in Oracle 10g xmlTable

Posted on 2009-05-14
10
Medium Priority
?
1,775 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 800 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 1200 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Suggested Courses

777 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