Solved

How to load an XML document in Oracle 10g xmlTable

Posted on 2009-05-14
10
1,754 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 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

789 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