9 Replies Latest reply on Dec 10, 2009 9:32 PM by joelkallman-Oracle

    What XML encoding do I use to get APEX to import an XML file?

    736600
      Environment:

      APEX 3.1.1.00.09 on AIX 5.3
      SQL Developer 1.5.5 on Windows XP Pro SP3
      Oracle 10.2.0.2 EE on AIX 5.3

      Trying to import an XML file generated from SQL Developer into a table using APEX Import utility.

      I'm getting the error: ORA-19200: Invalid column specification

      All I've done is exported the EMP table from SQL Developer (v. 1.5.5) into an XML file and then tried to load into an empty EMP table in the database (10.2.0.2) using APEX and the Data Load Utility.

      I've tried creating the XML file using different encodings out of SQL Developer but so far an still getting the above error.

      Any help is greatly appreciated.

      -gary
        • 1. Re: What XML encoding do I use to get APEX to import an XML file?
          736600
          OK. I've given everyone 2 weeks to come up with the answer to this one but so far no one has jumped in to help.

          Just give it a try.

          Export a small table from SQL Developer as an XML file and try to load it into a table using the data load capability of APEX.

          If it works, please let me know what encoding you used. It is either in the Preferences in SQL Developer and/or in the heading of the XML file.

          I'm trying to see if it's the encoding or something to do with the XML loading feature of APEX.

          Many thanks for any assistance.

          -gary
          • 2. Re: What XML encoding do I use to get APEX to import an XML file?
            Sc0tt
            I think just unicode - all I did was go into Apex, Unloaded my emp table into an XML file (output below). Went back to Load data and it loaded right up. Compare this to your file and maybe you'll see the error.
              <?xml version="1.0" ?>
            <ROWSET>
             <ROW>
              <DEPTNO>30</DEPTNO>
              <SAL>100500</SAL>
              <JOB>SACH</JOB>
              <EMPNO>3</EMPNO>
              <ENAME>SACH</ENAME>
             </ROW>
             <ROW>
              <DEPTNO>30</DEPTNO>
              <SAL>101000</SAL>
              <JOB>SMITH</JOB>
              <EMPNO>1</EMPNO>
              <ENAME>SMITH</ENAME>
             </ROW>
             <ROW>
              <DEPTNO>30</DEPTNO>
              <SAL>101500</SAL>
              <JOB>JONES</JOB>
              <EMPNO>2</EMPNO>
              <ENAME>JONES</ENAME>
             </ROW>
            </ROWSET>
            1 person found this helpful
            • 3. Re: What XML encoding do I use to get APEX to import an XML file?
              736600
              Hi Scott

              Thanks very much for your help.

              I copied your XML data into a file on my PC, went back to APEX and went through the 'Loading XML' process into an empty EMP table and received the following error:

              ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00209: PI names starting with XML are reserved

              The Oracle Error Message Guide was very helpful in giving me this explanation:

              ORA-31011: 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.


              Could there be a setting either in APEX or on my PC that is causing the problem???

              Any suggestions are welcome!!

              -gary
              • 4. Re: What XML encoding do I use to get APEX to import an XML file?
                Sc0tt
                What happens if you export a simple file and then try to reimport it back in Apex? Just curious if you get an error or not.
                • 5. Re: What XML encoding do I use to get APEX to import an XML file?
                  736600
                  Hi Scott

                  First of all I was successful in exporting and re-importing the EMP table to and from APEX.

                  When I exported the EMP table in XML format using SQL Developer, the resulting XML looked very different from the same data exported from APEX. My APEX exported XML looked just like yours. Here are the first 3 rows of the XML file exported from SQL Developer. I'm NOT an XML person so I can't judge whether or not this is valid XML but I can tell you that it doesn't import into an empty EMP table through APEX. I can control the encoding type in SQL Developer and have tried several different flavors.

                  <?xml version='1.0' encoding='UTF-8' ?>
                  <RESULTS>
                       <ROW>
                            <COLUMN NAME="EMPNO"><![CDATA[7876]]></COLUMN>
                            <COLUMN NAME="ENAME"><![CDATA[ADAMS]]></COLUMN>
                            <COLUMN NAME="JOB"><![CDATA[CLERK]]></COLUMN>
                            <COLUMN NAME="MGR"><![CDATA[7788]]></COLUMN>
                            <COLUMN NAME="HIREDATE"><![CDATA[12-JAN-83]]></COLUMN>
                            <COLUMN NAME="SAL"><![CDATA[1100]]></COLUMN>
                            <COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
                            <COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
                       </ROW>
                       <ROW>
                            <COLUMN NAME="EMPNO"><![CDATA[7499]]></COLUMN>
                            <COLUMN NAME="ENAME"><![CDATA[ALLEN]]></COLUMN>
                            <COLUMN NAME="JOB"><![CDATA[SALESMAN]]></COLUMN>
                            <COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
                            <COLUMN NAME="HIREDATE"><![CDATA[20-FEB-81]]></COLUMN>
                            <COLUMN NAME="SAL"><![CDATA[1600]]></COLUMN>
                            <COLUMN NAME="COMM"><![CDATA[300]]></COLUMN>
                            <COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
                       </ROW>
                       <ROW>
                            <COLUMN NAME="EMPNO"><![CDATA[7521]]></COLUMN>
                            <COLUMN NAME="ENAME"><![CDATA[WARD]]></COLUMN>
                            <COLUMN NAME="JOB"><![CDATA[SALESMAN]]></COLUMN>
                            <COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
                            <COLUMN NAME="HIREDATE"><![CDATA[22-FEB-81]]></COLUMN>
                            <COLUMN NAME="SAL"><![CDATA[1250]]></COLUMN>
                            <COLUMN NAME="COMM"><![CDATA[500]]></COLUMN>
                            <COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
                       </ROW>


                  Maybe I should move this thread to the SQL Developer or XML forums.

                  If you have any other suggestions I would be most appreciative.

                  Thanks very much for your help.

                  -gary
                  • 6. Re: What XML encoding do I use to get APEX to import an XML file?
                    j.sieben
                    Hi user 71...

                    from what I can see, the XML file you're trying to import contains a processing instruction (<?xml... ?>) which is fine for an XML file. But if you import it, I guess that a second PI is surrounded to the xml file, giving you the error above. Maybe you try and exclude the processing instruction from the xml file and retry.

                    Best regards,

                    Jürgen
                    • 7. Re: What XML encoding do I use to get APEX to import an XML file?
                      joelkallman-Oracle
                      Hi Gary,

                      It isn't that Application Express doesn't like the CDATA elements. It's just that they are two completely different XML formats.

                      I thought this was a good candidate for a blog post, so I created one here: http://joelkallman.blogspot.com/2009/12/moving-table-data-from-sql-developer.html

                      Thanks for the inspiration, Gary.

                      Joel
                      • 8. Re: What XML encoding do I use to get APEX to import an XML file?
                        TexasApexDeveloper
                        Joel,
                        You would think there would be some communication between the Sql Developer group and the APEX group on how to interchange data files, since Sql Developer does interact with APEX...

                        Just a kinda weird thought here, ya know since they both are Oracle development groups...

                        Thank you,

                        Tony Miller
                        Webster, TX
                        • 9. Re: What XML encoding do I use to get APEX to import an XML file?
                          joelkallman-Oracle
                          Hi Tony,

                          Actually, I'm a little surprised too, really. I never knew that Oracle SQL Developer did this differently until Gary raised this question and I took a look at it today. I imagine there's a good reason why they chose this format, but I don't know. I'll find out and let you (and this thread) know, though.

                          Joel