Solved

How to load an XML document in Oracle 10g xmlTable

Posted on 2009-05-14
10
1,749 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
  • 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 47

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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

863 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

18 Experts available now in Live!

Get 1:1 Help Now