Solved

How to load an XML document in Oracle 10g xmlTable

Posted on 2009-05-14
10
1,746 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks schwertner, mrjoltcola,

                                  Will try my hands on this :)
0
 
LVL 10

Author Comment

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

My apologies folks.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
Hi aman0711,

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

Author Comment

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

Author Comment

by:aman0711
Comment Utility
I want to assign points for this question.
0
 
LVL 10

Author Closing Comment

by:aman0711
Comment Utility
Thank you folks :-)
Sorry for abandoning this question :(
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 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