This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Mar 1, 2012 3:06 AM by V prasad RSS

How to insert rows from an  xml file to a table

901238 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    pls help me to insert based on above example
  • 4. Re: How to insert rows from an  xml file to a table
    BluShadow Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    <?xml version = '1.0'?>
    change to
    <?xml version = "1.0"?>
  • 9. Re: How to insert rows from an  xml file to a table
    901238 Newbie
    Currently Being Moderated
    I tried but still getting same error
  • 10. Re: How to insert rows from an  xml file to a table
    AlexAnd Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank You ODIE

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

Legend

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