4 Replies Latest reply: Jul 1, 2010 8:44 AM by 781620 RSS

    How to insert the data from XML to a table

    781620
      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
          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
            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
              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
                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.