This discussion is archived
4 Replies Latest reply: Jul 1, 2010 6:44 AM by 781620 RSS

How to insert the data from XML to a table

781620 Newbie
Currently Being Moderated
Hi,

I'm using Oracle 10g Express Edition
I need help in How to insert the data from XML file into the table.
Below is the example i'm working on..

I have create ridb user with below mentioned privileges:


Account Status Locked Unlocked
Default Tablespace: USERS
Temporary Tablespace: TEMP

User Privileges :
Roles:
CONNECT
RESOURCE

Direct Grant System Privileges:
CREATE DATABASE LINK
CREATE MATERIALIZED VIEW
CREATE PROCEDURE
CREATE PUBLIC SYNONYM
CREATE ROLE
CREATE SEQUENCE
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW




& table is created TRIALZIPCODES below mentioned is the DDL:

CREATE TABLE TRIALZIPCODES
(
STATE_ABBR VARCHAR2(20) NOT NULL
, ZIP_CODE NUMBER(10, 0) NOT NULL
, ZIP_CODE_EXT VARCHAR2(20)
);



Below is the XML FILE: which is stored in C:\OracleProject Folder
File name: trial.xml

<?xml version="1.0" ?>
<metadata>
- <Zipcodes>
- <mappings Record="4">
<STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
<ZIPCODE>94301</ZIPCODE>
</mappings>
- <mappings Record="5">
<STATE_ABBREVIATION>CO</STATE_ABBREVIATION>
<ZIPCODE>80323</ZIPCODE>
<ZIP_CODE_EXTN>9277</ZIP_CODE_EXTN>
</mappings>
</Zipcodes>
</metadata>


PL/SQL Procedure:which i'm trying to execute from SQLDeveloper

create or replace
PROCEDURE TRIAL AS
BEGIN
DECLARE
-- declare attributes

charString varchar2(80);
finalStr varchar2(4000) := null;
rowsp integer;
v_FileHandle UTL_FILE.FILE_TYPE;
l_context_handle dbms_xmlgen.ctxHandle;
insCtx DBMS_XMLStore.ctxType;

begin

-- DBMS_XMLGEN.setRowTag ( ctx IN ctxHandle, rowTag IN VARCHAR2);
-- DBMS_XMLGEN.setRowSetTag ( ctx IN ctxHandle, rowSetTag IN VARCHAR2);
-- the name of the table as specified in our DTD
DBMS_XMLGEN.SETROWSETTAG(l_context_handle,'zipcodes');
-- the name of the data set as specified in our DTD
DBMS_xmlgen.setRowTag(l_context_handle,'mappings');
-- for getting the output on the screen
dbms_output.enable(1000000);
-- open the XML document in read only mode
v_FileHandle := utl_file.fopen('c:/OracleProject','trial.xml', 'r');

loop

BEGIN

utl_file.get_line(v_FileHandle, charString);
exception
when no_data_found then
utl_file.fclose(v_FileHandle);
exit;

END;
dbms_output.put_line(charString);
if finalStr is not null then
finalStr := finalStr || charString;
else
finalStr := charString;
end if;
end loop;
-- for inserting the XML data into the table
insCtx := DBMS_XMLSTORE.NEWCONTEXT('RIDB.TRIALZIPCODES');
insCtx := DBMS_XMLSTORE.INSERTXML(insCtx, finalStr);
dbms_output.put_line('INSERT DONE '||TO_CHAR(rowsp));
DBMS_XMLStore.closeContext(insCtx);


END;

END TRIAL;

For the first time when i complied i got the errors as :

Procedure RIDB.PROCEDURE1@RIDB
Error(16,14): PLS-00201: identifier 'UTL_FILE' must be declared
Error(16,14): PL/SQL: Item ignored
Error(29,1): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(29,1): PL/SQL: Statement ignored
Error(33,1): PL/SQL: Statement ignored
Error(33,19): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(36,1): PL/SQL: Statement ignored
Error(36,17): PLS-00320: the declaration of the type of this expression is incomplete or malformed

So i logged in as sys & grant the permission to execute on UTL_FILE to ridb (user):
SQL Statement:

grant execute on utl_file to ridb

So, it got compiled successfully but when i execute it gives me error as:
Source does not have a runnable target.

What does this mean?

So I browse through forum & i got to know that i need to initial the UTL_FILE_DIR ="C:/OracleProject" in init.ora
So can i edit the init.ora with notepad.When i tried to do that it says permission denied

In my system it shows the init.ora file in path C:\oraclexe\app\oracle\product\10.2.0\server\config\scripts
but there is also other file initXETemp in the same path do i need to do the changes in it.

I have tried even editing the SPFILE as mentioned below:

C:\oraclexe\app\oracle\product\10.2.0\server\dbs\SPFILEEXE - I had edit this file using notepad & set the value of UTL_FILE_DIR ="C:/OracleProject". So next time when i restarted i'm unable to log on to the database.

So i had reinstall the software again.

Could you please let me know how to proceed..
  • 1. Re: How to insert the data from XML to a table
    odie_63 Guru
    Currently Being Moderated
    Hi,

    UTL_FILE_DIR parameter is deprecated in 10g, Oracle recommends the use of DIRECTORY object instead :
    CREATE DIRECTORY xml_dir AS 'C:\OracleProject';
    The user who creates the directory is automatically granted READ, WRITE and EXECUTE rights, but the owner is always SYS.


    For your XML requirement, you may use the overloaded version of the XMLType constructor taking a BFILE pointer as parameter.
    It allows you to directly access the XML file on your OS file system.

    If you want to use DBMS_XMLSTORE, then the relational table must have the same column names as the elements of the XML :
    CREATE TABLE TRIALZIPCODES (
      STATE_ABBREVIATION VARCHAR2(20) NOT NULL
    , ZIPCODE NUMBER(10, 0) NOT NULL
    , ZIP_CODE_EXTN VARCHAR2(20)
    );
    DECLARE
    
      insCtx DBMS_XMLStore.ctxType;
      cnt NUMBER;
      xmldoc xmltype := 
       xmltype( bfilename('XML_DIR','trial.xml'), nls_charset_id('AL32UTF8') );
    
    BEGIN
      
      insCtx := DBMS_XMLStore.newContext('DEV.TRIALZIPCODES');
     
      DBMS_XMLStore.setUpdateColumn(insCtx, 'STATE_ABBREVIATION'); 
      DBMS_XMLStore.setUpdateColumn(insCtx, 'ZIPCODE'); 
      DBMS_XMLStore.setUpdatecolumn(insCtx, 'ZIP_CODE_EXTN'); 
      DBMS_XMLStore.setRowTag(insCtx, 'mappings');
      cnt := DBMS_XMLStore.insertXML(insCtx, xmldoc);
    
      DBMS_XMLStore.closeContext(insCtx);
      
    END;
    /
    If you want more flexibility, you may try this :
    CREATE TABLE TRIALZIPCODES (
      STATE_ABBR VARCHAR2(20) NOT NULL
    , ZIP_CODE NUMBER(10, 0) NOT NULL
    , ZIP_CODE_EXT VARCHAR2(20)
    );
    INSERT INTO trialzipcodes (state_abbr, zip_code, zip_code_ext)
    SELECT extractvalue(x.column_value, 'mappings/STATE_ABBREVIATION'),
           extractvalue(x.column_value, 'mappings/ZIPCODE'),
           extractvalue(x.column_value, 'mappings/ZIP_CODE_EXTN')
    FROM TABLE(
      XMLSequence(
        EXTRACT(
          xmltype( bfilename('XML_DIR','trial.xml'), nls_charset_id('AL32UTF8') ),
          'metadata/Zipcodes/mappings'
        )
      )
    ) x
    ;
  • 2. Re: How to insert the data from XML to a table
    781620 Newbie
    Currently Being Moderated
    hi,

    I have created the directory from sys database

    CREATE or replace DIRECTORY XML_DIR2 AS 'C:\OracleProject';

    & grant read,write access to the user

    grant read,write on directory XML_DIR2 to RIDB;

    & i had change the tag name in the xml file as shown below:

    <?xml version = '1.0'?>
    <metadata>
    <Zipcodes>
    <mappings Record="4">
    <STABBRE>CA</STABBRE>
    <ZIPCODE>94301</ZIPCODE>
    </mappings>
    <mappings Record="5">
    <STABBRE>CO</STABBRE>
    <ZIPCODE>80323</ZIPCODE>
    <ZIPCODEEXT>9277</ZIPCODEEXT>
    </mappings>
    </Zipcodes>
    </metadata>

    TRIALZIPCODE table as shown below:

    CREATE TABLE "RIDB"."TRIALZIPCODE"
    (     "STABBRE" VARCHAR2(20 BYTE),
         "ZIPCODE" NUMBER(*,6) NOT NULL ENABLE,
         "ZIPCODEEXT" NUMBER
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "USERS" ;

    I have tried two methods as shown below:

    Procedure 1:

    create or replace
    PROCEDURE TRIAL_V2 AS
    BEGIN
    DECLARE
    -- declare attributes
    charString varchar2(80);
    finalStr varchar2(4000) := null;
    rowsp integer;
    v_FileHandle UTL_FILE.FILE_TYPE;
    l_context_handle dbms_xmlgen.ctxHandle;
    insCtx DBMS_XMLStore.ctxType;
    cnt NUMBER;
    xmldoc xmltype := xmltype( bfilename('XML_DIR2','trialxml.xml'), nls_charset_id('AL32UTF8') );

    --XML_DIR VARCHAR2(40) := 'C:\\OracleProject';
    BEGIN

    insCtx := DBMS_XMLStore.newContext('DEV.TRIALZIPCODES');

    DBMS_XMLStore.setUpdateColumn(insCtx, 'STABBRE');
    DBMS_XMLStore.setUpdateColumn(insCtx, 'ZIPCODE');
    DBMS_XMLStore.setUpdatecolumn(insCtx, 'ZIPCODEEXT');
    DBMS_XMLStore.setRowTag(insCtx, 'mappings');
    cnt := DBMS_XMLStore.insertXML(insCtx, xmldoc);

    DBMS_XMLStore.closeContext(insCtx);

    END;

    Procedure 1 was compiled with out errors but when i execute i got the error as :

    Source does not have a runnable target.


    Procedure 2_

    CREATE OR REPLACE PROCEDURE TRIAL_V3 AS
    BEGIN
    DECLARE
    -- declare attributes
    charString varchar2(80);
    finalStr varchar2(4000) := null;
    rowsp integer;
    v_FileHandle UTL_FILE.FILE_TYPE;
    l_context_handle dbms_xmlgen.ctxHandle;
    insCtx DBMS_XMLStore.ctxType;
    cnt NUMBER;
    xmldoc xmltype := xmltype( bfilename('XML_DIR2','trialxml.xml'), nls_charset_id('AL32UTF8') );

    --XML_DIR VARCHAR2(40) := 'C:\\OracleProject';
    BEGIN
    INSERT INTO trialzipcode (STABBRE, ZIPCODE, ZIPCODEEXT)
    SELECT extractvalue(x.column_value, 'mappings/STABBRE'),
    extractvalue(x.column_value, 'mappings/ZIPCODE'),
    extractvalue(x.column_value, 'mappings/ZIPCODEEXT')
    FROM TABLE(
    XMLSequence(
    EXTRACT(
    xmltype( bfilename('XML_DIR2','trialxml.xml'), nls_charset_id('AL32UTF8') ),
    'metadata/Zipcodes/mappings'
    )
    )
    ) x
    ;
    END;

    END TRIAL_V3;

    Procedure 2 was complied without errors but when i execute i got the error as:

    Connecting to the database RIDB.
    ORA-22288: file or LOB operation FILEOPEN failed
    The system cannot find the file specified.
    ORA-06512: at "SYS.DBMS_LOB", line 523
    ORA-06512: at "SYS.XMLTYPE", line 287
    ORA-06512: at "RIDB.TRIAL_V3", line 12
    ORA-06512: at line 2
    Process exited.
    Disconnecting from the database RIDB.

    Could you please let me know how to proceed...
  • 3. Re: How to insert the data from XML to a table
    odie_63 Guru
    Currently Being Moderated
    Procedure 1 was compiled with out errors
    Are you sure about that?

    I'm suspicious about this part :
    --XML_DIR VARCHAR2(40) := 'C:
    OracleProject';
    Use SQL*Plus to create the procedure and check errors afterwards with command "SHOW ERRORS".

    Did you try to run just the PL/SQL block I gave?
  • 4. Re: How to insert the data from XML to a table
    781620 Newbie
    Currently Being Moderated
    I have commented the statement, anyway i had removed that & executed it.but still i'm getting error:

    Procedure:_

    CREATE OR REPLACE PROCEDURE TRIAL_V3 AS
    BEGIN
    DECLARE
    -- declare attributes
    charString varchar2(80);
    finalStr varchar2(4000) := null;
    rowsp integer;
    v_FileHandle UTL_FILE.FILE_TYPE;
    l_context_handle dbms_xmlgen.ctxHandle;
    insCtx DBMS_XMLStore.ctxType;
    cnt NUMBER;
    xmldoc xmltype := xmltype( bfilename('XML_DIR2','trialxml.xml'), nls_charset_id('AL32UTF8') );


    BEGIN
    INSERT INTO trialzipcode (STABBRE, ZIPCODE, ZIPCODEEXT)
    SELECT extractvalue(x.column_value, 'mappings/STABBRE'),
    extractvalue(x.column_value, 'mappings/ZIPCODE'),
    extractvalue(x.column_value, 'mappings/ZIPCODEEXT')
    FROM TABLE(
    XMLSequence(
    EXTRACT(
    xmltype( bfilename('XML_DIR2','trialxml.xml'), nls_charset_id('AL32UTF8') ),
    'metadata/Zipcodes/mappings'
    )
    )
    ) x
    ;
    END;

    END TRIAL_V3;

    Error i was getting is :
    Connecting to the database RIDB.
    ORA-22288: file or LOB operation FILEOPEN failed
    The system cannot find the file specified.
    ORA-06512: at "SYS.DBMS_LOB", line 523
    ORA-06512: at "SYS.XMLTYPE", line 287
    ORA-06512: at "RIDB.TRIAL_V3", line 12
    ORA-06512: at line 2
    Process exited.
    Disconnecting from the database RIDB.

    For the procedure2:

    create or replace
    PROCEDURE TRIAL_V2 AS
    BEGIN
    DECLARE
    -- declare attributes
    charString varchar2(80);
    finalStr varchar2(4000) := null;
    rowsp integer;
    v_FileHandle UTL_FILE.FILE_TYPE;
    l_context_handle dbms_xmlgen.ctxHandle;
    insCtx DBMS_XMLStore.ctxType;
    cnt NUMBER;
    xmldoc xmltype := xmltype( bfilename('XML_DIR2','trialxml.xml'), nls_charset_id('AL32UTF8') );


    BEGIN

    insCtx := DBMS_XMLStore.newContext('DEV.TRIALZIPCODES');

    DBMS_XMLStore.setUpdateColumn(insCtx, 'STABBRE');
    DBMS_XMLStore.setUpdateColumn(insCtx, 'ZIPCODE');
    DBMS_XMLStore.setUpdatecolumn(insCtx, 'ZIPCODEEXT');
    DBMS_XMLStore.setRowTag(insCtx, 'mappings');
    cnt := DBMS_XMLStore.insertXML(insCtx, xmldoc);

    DBMS_XMLStore.closeContext(insCtx);

    END;

    error was: Source does not have a runnable target.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points