2 Replies Latest reply: Sep 16, 2013 8:29 AM by 922566 RSS

    Help importing an XML file



      I'm working with Oracle SQL Developer,

      I have Table1 with columns : Column1, Column2, Column3, Column4, Column5, Column6

      When I right click on Table 1, choose import data then choose my xml file, I get the error: "They are no reader registred for the XML type"


      Here's the xml file:

      <?xml version="1.0"?>


         <book id="bk101">

            <author>Gambardella, Matthew</author>

            <title>XML Developer's Guide</title>




            <description>An in-depth look at creating applications

            with XML.</description>


         <book id="bk102">

            <author>Ralls, Kim</author>

            <title>Midnight Rain</title>




            <description>A former architect battles corporate zombies,

            an evil sorceress, and her own childhood to become queen

            of the world.</description>




        • 1. Re: Help importing an XML file

          select x.author, x.title, x.genre, x.price, x.publish_date, x.descr

            from xmltable('/catalog/book' passing

                          xmltype(bfilename('MYDIR', 'import_xml.xml'),

                                  nls_charset_id('AL32UTF8')) columns

                          author varchar2(200) path 'author',

                          title varchar2(200) path 'title',

                          genre varchar2(200) path 'genre',

                          price varchar2(200) path 'price',

                          publish_date varchar2(200) path 'publish_date',

                          descr varchar2(200) path 'description') x

          as for insert

          insert into...

          select ...

          'MYDIR' is oracle directory with 'import_xml.xml' file for import

          • 2. Re: Help importing an XML file

            Hi AlexAnd,

            Is there a way to do it graphicaly without command ?


            Here's the error that I get :

            Error report:

            Error report:

            SQL Error: ORA-00972: identifier is too long

            ORA-06512: at "SYS.XMLTYPE", line 296

            ORA-06512: at line 1

            00972. 00000 -  "identifier is too long"

            *Cause:    An identifier with more than 30 characters was specified.

            *Action:   Specify at most 30 characters.