1 2 Previous Next 15 Replies Latest reply: Mar 1, 2012 5:06 AM by V prasad RSS

    How to insert rows from an  xml file to a table

    901238
      im Using Oracle 11g It is showing below error when i execute..



      declare
      charString varchar2(80);
      finalStr varchar2(400) := null;
      rowsp integer;
      v_FileHandle UTL_FILE.FILE_TYPE;
      begin
      -- the name of the table as specified in our DTD
      xmlgen.setRowsetTag('Zipcodes');
      -- the name of the data set as specified in our DTD
      xmlgen.setRowTag('mappings');
      -- for getting the output on the screen
      dbms_output.enable(100000);
      -- open the XML document in read only mode
      v_FileHandle := utl_file.fopen(TMP_DIR1,'XML_NEW_CITIES.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
      rowsp := xmlgen.insertXML('SYS.ZIPCODES',finalStr);
      dbms_output.put_line('INSERT DONE '||TO_CHAR(rowsp));
      xmlgen.resetOptions;
      end;
      /




      XML_NEW_CITIES.XML is

      <?xml version = '1.0'?>
      <Zipcodes>
      <mappings Record="4">
      <STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
      <ZIPCODE>94301</ZIPCODE>
      <CITY>Palo Alto</CITY>
      </mappings>
      <mappings Record="5">
      <STATE_ABBREVIATION>CO</STATE_ABBREVIATION>
      <ZIPCODE>80323</ZIPCODE>
      <ZIP_CODE_EXTN>9277</ZIP_CODE_EXTN>
      <CITY>Boulder</CITY>
      </mappings>
      </Zipcodes>




      Error report:
      ORA-06550: line 8, column 3:
      PLS-00201: identifier 'XMLGEN.SETROWSETTAG' must be declared
      ORA-06550: line 8, column 3:
      PL/SQL: Statement ignored
      ORA-06550: line 10, column 3:
      PLS-00201: identifier 'XMLGEN.SETROWTAG' must be declared
      ORA-06550: line 10, column 3:
      PL/SQL: Statement ignored
      ORA-06550: line 14, column 34:
      PLS-00201: identifier 'TMP_DIR1' must be declared
      ORA-06550: line 14, column 3:
      PL/SQL: Statement ignored
      ORA-06550: line 31, column 12:
      PLS-00201: identifier 'XMLGEN.INSERTXML' must be declared
      ORA-06550: line 31, column 3:
      PL/SQL: Statement ignored
      ORA-06550: line 33, column 3:
      PLS-00201: identifier 'XMLGEN.RESETOPTIONS' must be declared
      ORA-06550: line 33, column 3:
      PL/SQL: Statement ignored
      06550. 00000 - "line %s, column %s:\n%s"
      *Cause:    Usually a PL/SQL compilation error

      Edited by: 898235 on Nov 20, 2011 11:02 PM
        • 2. Re: How to insert rows from an  xml file to a table
          BluShadow
          Lots of examples in the XML DB forum FAQ:

          XML DB FAQ
          • 3. Re: How to insert rows from an  xml file to a table
            901238
            pls help me to insert based on above example
            • 4. Re: How to insert rows from an  xml file to a table
              BluShadow
              Something like...
              declare
                v_xml   XMLTYPE;
                function file2clob( p_dir in varchar2, p_file_name in varchar2 ) return clob is
                  file_lob bfile;
                  file_clob clob;
                begin
                  file_lob := bfilename( p_dir, p_file_name );
                  dbms_lob.open( file_lob, dbms_lob.file_readonly );
                  dbms_lob.createtemporary( file_clob, true );
                  dbms_lob.loadfromfile( file_clob, file_lob, dbms_lob.lobmaxsize );
                  dbms_lob.close( file_lob ); 
                  return file_clob;
                exception
                  when others then -- would not normally recommend using when others exceptions
                    if dbms_lob.isopen( file_lob ) = 1
                    then
                      dbms_lob.close( file_lob ); 
                    end if;
                    if dbms_lob.istemporary( file_clob ) = 1
                    then
                      dbms_lob.freetemporary( file_clob );
                    end if;
                    raise;
                end;
              begin
                v_xml := XMLTYPE(file2clob('TMP_DIR1', 'XML_NEW_CITIES.XML'));
                insert into zipcodes (myxmlcolumn) values (v_xml);
              end;
              Not sure what SYS.ZIPCODES is in your code. You shouldn't create your own tables or database objects in the SYS schema as that is the data dictionary and it's bad practice to use that yourself. Create your own user/schema to use.
              • 5. Re: How to insert rows from an  xml file to a table
                odie_63
                Or the shorter version :
                insert into zipcodes (myxmlcolumn) 
                values ( xmltype(bfilename('TMP_DIR1', 'XML_NEW_CITIES.xml'), nls_charset_id('AL32UTF8')) )
                ;
                But something tells me OP is probably looking for this :
                SQL> SELECT *
                  2  FROM XMLTable('/Zipcodes/mappings'
                  3         passing xmltype(bfilename('TMP_DIR1', 'xml_new_cities.xml'), nls_charset_id('AL32UTF8'))
                  4         columns state_abbreviation varchar2(2)  path 'STATE_ABBREVIATION'
                  5               , zipcode            varchar2(5)  path 'ZIPCODE'
                  6               , city               varchar2(80) path 'CITY'
                  7       )
                  8  ;
                 
                STATE_ABBREVIATION ZIPCODE CITY
                ------------------ ------- --------------------------------------------------------------------------------
                CA                 94301   Palo Alto
                CO                 80323   Boulder
                 
                Edited by: odie_63 on 22 nov. 2011 14:21
                • 6. Re: How to insert rows from an  xml file to a table
                  BluShadow
                  odie_63 wrote:
                  But something tells me OP is probably looking for this :
                  Well, I considered that, but then looking at how he was just concatenating all the contents of the file together and then trying to insert all that in one go, it wasn't clear.

                  even had a similar thing on my test database earlier...
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  with t as (select xmltype('<?xml version = ''1.0''?>
                    2  <Zipcodes>
                    3  <mappings Record="4">
                    4  <STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
                    5  <ZIPCODE>94301</ZIPCODE>
                    6  <CITY>Palo Alto</CITY>
                    7  </mappings>
                    8  <mappings Record="5">
                    9  <STATE_ABBREVIATION>CO</STATE_ABBREVIATION>
                   10  <ZIPCODE>80323</ZIPCODE>
                   11  <ZIP_CODE_EXTN>9277</ZIP_CODE_EXTN>
                   12  <CITY>Boulder</CITY>
                   13  </mappings>
                   14  </Zipcodes>') as xml from dual)
                   15  --
                   16  -- End of test data, please use SQL below on your own data
                   17  --
                   18  select x.*
                   19  from t
                   20      ,xmltable('/Zipcodes/mappings'
                   21                passing t.xml
                   22                columns record_no  number       path './@Record'
                   23                       ,state_abbr varchar2(2)  path './STATE_ABBREVIATION'
                   24                       ,zipcode    varchar2(5)  path './ZIPCODE'
                   25                       ,city       varchar2(20) path './CITY'
                   26*              ) x
                  SQL> /
                  
                   RECORD_NO ST ZIPCO CITY
                  ---------- -- ----- --------------------
                           4 CA 94301 Palo Alto
                           5 CO 80323 Boulder
                  • 7. Re: How to insert rows from an  xml file to a table
                    901238
                    HI I tried with below code

                    create or replace directory TMP_DIR1 as 'D:\test1';

                    declare
                    v_xml XMLTYPE;
                    function file2clob( p_dir in varchar2, p_file_name in varchar2 ) return clob is
                    file_lob bfile;
                    file_clob clob;
                    begin
                    file_lob := bfilename( p_dir, p_file_name );
                    dbms_lob.open( file_lob, dbms_lob.file_readonly );
                    dbms_lob.createtemporary( file_clob, true );
                    dbms_lob.loadfromfile( file_clob, file_lob, dbms_lob.lobmaxsize );
                    dbms_lob.close( file_lob );
                    return file_clob;
                    exception
                    when others then -- would not normally recommend using when others exceptions
                    if dbms_lob.isopen( file_lob ) = 1
                    then
                    dbms_lob.close( file_lob );
                    end if;
                    if dbms_lob.istemporary( file_clob ) = 1
                    then
                    dbms_lob.freetemporary( file_clob );
                    end if;
                    raise;
                    end;
                    begin
                    v_xml := XMLTYPE(file2clob('TMP_DIR1', 'XML_NEW_CITIES.XML'));
                    insert into zipcodes (STATE_ABBREVIATION) values (v_xml);
                    end;


                    <?xml version = '1.0'?>
                    <Zipcodes>
                    <mappings Record="4">
                    <STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
                    <ZIPCODE>94301</ZIPCODE>
                    <CITY>Palo Alto</CITY>
                    </mappings>
                    <mappings Record="5">
                    <STATE_ABBREVIATION>CO</STATE_ABBREVIATION>
                    <ZIPCODE>80323</ZIPCODE>
                    <ZIP_CODE_EXTN>9277</ZIP_CODE_EXTN>
                    <CITY>Boulder</CITY>
                    </mappings>
                    </Zipcodes>

                    Im getting below error

                    Error report:
                    ORA-31011: XML parsing failed
                    ORA-19202: Error occurred in XML processing
                    LPX-00210: expected '<' instead of '?'
                    Error at line 1
                    ORA-06512: at "SYS.XMLTYPE", line 272
                    ORA-06512: at line 26
                    31011. 00000 - "XML parsing failed"
                    *Cause:    XML parser returned an error while trying to parse the document.
                    *Action:   Check if the document to be parsed is valid.

                    Edited by: 898235 on Nov 22, 2011 9:32 PM
                    • 8. Re: How to insert rows from an  xml file to a table
                      AlexAnd
                      <?xml version = '1.0'?>
                      change to
                      <?xml version = "1.0"?>
                      • 9. Re: How to insert rows from an  xml file to a table
                        901238
                        I tried but still getting same error
                        • 10. Re: How to insert rows from an  xml file to a table
                          AlexAnd
                          >
                          I tried but still getting same error
                          >
                          show us

                          SQL> with t as 
                            2  (
                            3  select xmltype('<?xml version = '1.0'?>
                            4  <Zipcodes>
                            5  <mappings Record="4">
                            6  <STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
                            7  <ZIPCODE>94301</ZIPCODE>
                            8  <CITY>Palo Alto</CITY>
                            9  </mappings>
                           10  <mappings Record="5">
                           11  <STATE_ABBREVIATION>CO</STATE_ABBREVIATION>
                           12  <ZIPCODE>80323</ZIPCODE>
                           13  <ZIP_CODE_EXTN>9277</ZIP_CODE_EXTN>
                           14  <CITY>Boulder</CITY>
                           15  </mappings>
                           16  </Zipcodes>') xml from dual
                           17  )
                           18  select * from t
                           19  /
                          select xmltype('<?xml version = '1.0'?>
                                                           *
                          ERROR at line 3:
                          ORA-00907: missing right parenthesis
                          
                          
                          SQL> ed
                          Wrote file afiedt.buf
                          
                            1  with t as
                            2  (
                            3  select xmltype('<?xml version = "1.0"?>
                            4  <Zipcodes>
                            5  <mappings Record="4">
                            6  <STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
                            7  <ZIPCODE>94301</ZIPCODE>
                            8  <CITY>Palo Alto</CITY>
                            9  </mappings>
                           10  <mappings Record="5">
                           11  <STATE_ABBREVIATION>CO</STATE_ABBREVIATION>
                           12  <ZIPCODE>80323</ZIPCODE>
                           13  <ZIP_CODE_EXTN>9277</ZIP_CODE_EXTN>
                           14  <CITY>Boulder</CITY>
                           15  </mappings>
                           16  </Zipcodes>') xml from dual
                           17  )
                           18* select * from t
                          SQL> /
                          
                          XML
                          -----------------------------------------------------------
                          <?xml version = "1.0"?>
                          <Zipcodes>
                          <mappings Record="4">
                          <STATE_ABBREVIATION>CA<
                          
                          
                          SQL> 
                          • 11. Re: How to insert rows from an  xml file to a table
                            odie_63
                            Hi,

                            How about explaining what you want to do exactly?

                            Do you want to insert an entire XML document in a table, or do you want to shred the XML into relational rows and columns?
                            The way you altered BluShadow's script makes me think you want the latter.

                            If so, use the query with XMLTable as we suggested above.
                            • 12. Re: How to insert rows from an  xml file to a table
                              901238
                              i have created a table ZIPCODES with datatypes varchar2
                              Name
                              -----------------------------
                              STATE_ABBREVIATION
                              ZIPCODE
                              ZIP_CODE_EXTN
                              CITY

                              and i have an xml file XML_NEW_CITIES.XML with data given below

                              <?xml version = '1.0'?>
                              <Zipcodes>
                              <mappings Record="4">
                              <STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
                              <ZIPCODE>94301</ZIPCODE>
                              <CITY>Palo Alto</CITY>
                              </mappings>
                              <mappings Record="5">
                              <STATE_ABBREVIATION>CO</STATE_ABBREVIATION>
                              <ZIPCODE>80323</ZIPCODE>
                              <ZIP_CODE_EXTN>9277</ZIP_CODE_EXTN>
                              <CITY>Boulder</CITY>
                              </mappings>
                              </Zipcodes>

                              I want to insert data containing xml to zipcodes table columns using plsql reading from a file called XML_NEW_CITIES.XML
                              • 13. Re: How to insert rows from an  xml file to a table
                                odie_63
                                OK, then did you try the XMLTable example, or are you waiting for someone to write it all down for you? :)

                                You first have to create an Oracle directory object pointing to the physical location of your file (must be somewhere on the db server or at least a location the db server can access on the network).
                                Then create the procedure, in this case a simple INSERT SELECT :
                                SQL> create directory tmp_dir as 'c:\temp';
                                 
                                Directory created
                                 
                                SQL> create table zipcodes (
                                  2   state_abbreviation  varchar2(2),
                                  3   zipcode             varchar2(5),
                                  4   zip_code_extn       varchar2(10),
                                  5   city                varchar2(80)
                                  6  );
                                 
                                Table created
                                 
                                SQL> CREATE OR REPLACE PROCEDURE insertZipcodes (
                                  2    p_directory IN VARCHAR2
                                  3  , p_filename  IN VARCHAR2
                                  4  )
                                  5  IS
                                  6  BEGIN
                                  7  
                                  8    INSERT INTO zipcodes (state_abbreviation, zipcode, zip_code_extn, city)
                                  9    SELECT state_abbreviation
                                 10         , zipcode
                                 11         , zip_code_extn
                                 12         , city
                                 13    FROM XMLTable('/Zipcodes/mappings'
                                 14           passing xmltype(bfilename(p_directory, p_filename), nls_charset_id('AL32UTF8'))
                                 15           columns state_abbreviation varchar2(2)  path 'STATE_ABBREVIATION'
                                 16                 , zipcode            varchar2(5)  path 'ZIPCODE'
                                 17                 , zip_code_extn      varchar2(10) path 'ZIP_CODE_EXTN'
                                 18                 , city               varchar2(80) path 'CITY'
                                 19         )
                                 20    ;
                                 21  
                                 22  END;
                                 23  /
                                 
                                Procedure created
                                 
                                SQL> exec insertZipcodes('TMP_DIR', 'xml_new_cities.xml');
                                 
                                PL/SQL procedure successfully completed
                                 
                                SQL> commit;
                                 
                                Commit complete
                                 
                                SQL> select * from zipcodes;
                                 
                                STATE_ABBREVIATION ZIPCODE ZIP_CODE_EXTN CITY
                                ------------------ ------- ------------- ---------------
                                CA                 94301                 Palo Alto
                                CO                 80323   9277          Boulder
                                 
                                • 14. Re: How to insert rows from an  xml file to a table
                                  901238
                                  Thank You ODIE

                                  Edited by: Nagaraju525 on Nov 24, 2011 10:59 AM
                                  1 2 Previous Next