• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1798
  • Last Modified:

How to load an XML document in Oracle 10g xmlTable

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
aman0711
Asked:
aman0711
  • 5
  • 2
2 Solutions
 
mrjoltcolaCommented:
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
 
schwertnerCommented:
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
 
aman0711Author Commented:
Thanks schwertner, mrjoltcola,

                                  Will try my hands on this :)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
aman0711Author Commented:
I am sorry, some how I got real busy. I wanted to assign points to schwertner and mrjoltcola.

My apologies folks.
0
 
mrjoltcolaCommented:
Hi aman0711,

Feel free to go ahead and close / score the question yourself as you see fit.
0
 
aman0711Author Commented:
I cant see the option to close this question now... :(
0
 
aman0711Author Commented:
I want to assign points for this question.
0
 
aman0711Author Commented:
Thank you folks :-)
Sorry for abandoning this question :(
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now