Forum Stats

  • 3,852,755 Users
  • 2,264,134 Discussions
  • 7,905,132 Comments

Discussions

Categories

SQL Workshop > Utilities > Data Load > XML received LPX-00222 error

sect55
sect55 Member Posts: 1,106 Bronze Badge
edited Sep 26, 2018 12:20PM in APEX Discussions

Hi all,

APEX 5.1

Oracle 11g Rel 2

I tried to load data from an XML file to my Oracle table and I received the following error:

ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00222: error received from SAX callback function

Error loading XML.

The Oracle table references all the columns in the XML file but each row of the XML file does not necessarily contain all the columns.

For example, the table is defined as follows:

pastedImage_7.png

pastedImage_8.png

pastedImage_9.png

Not all the rows contain all the columns. See attachment (sub_c_row_example.txt) for an example of one row.

I can not define all the columns in each row. The XML file is exported from a Microsoft Access table. Also, there are thousands of rows.

How can successfully import the rows into the Oracle table?

Thank you,

Robert

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Franck N
    Franck N Member Posts: 1,019 Silver Trophy
    edited Sep 25, 2018 3:36PM

    Hi,

    you can´t import a file with more than 30 KB using SQL Workshop utilities. and more than 1000 Rows is definitely more than 30 KB.

    So it won´t work.

    But if you have SQL plus or SQL developer just connect to your Schema that is assign to your APEX  Application and execute the import from there.

    regards,

    Franck

    · Share on TwitterShare on Facebook
  • sect55
    sect55 Member Posts: 1,106 Bronze Badge
    edited Sep 26, 2018 8:42AM

    Franck,

    The file is less than 30K (28,438 KB to be exact).

    I tried SQL Developer and you cannot import using XML. It only allows xls, xlsx,,csv, tsv, txt, or dsv files.

    Please help!

    Robert

    · Share on TwitterShare on Facebook
  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown
    edited Sep 26, 2018 12:20PM

    You need to use the "DIY Data Extract, Transform, and Load Wizard".

    DIY === Do It Yourself.

    In other words, you need to create the application that does the processing.

    Steps involved:

    1. Create an application
    2. Create a "thank you, have a nice day page" (I'm building the pages in Reverse)
    3. Create a Page to accept and process the file.
    4. On that page (step 3), Create a "File Browse.." Item
      1. Configure it so that it goes to APEX_APPLICATION_TEMP_FILES.
        1. create a real table if you have problems with the XMLTable() syntax.
      2. Configure it so that it is a Required Field
    5. Add a Submit Button to the page (step 3)
    6. Add a On Submit Process to the page (step 3)
      1. This should be a PL/SQL process.
    7. Add a Branch to that page (step 3) that branches on button click (step 5) to the "Thank You" page (step 2)

    The PL/SQL Process should contain your INSERT ... SELECT statement.

    It should look similar to:

    INSERT INTO Table_A ( .... )SELECT ...FROM APEX_APPLICATION_TEMP_FILES t, XMLTable( '/something/          using XMLType( t.file_blob )          columns              .... ) bwhere t.name = :P15_file_browse_item_name;

    My $0.02

    MK

    · Share on TwitterShare on Facebook
This discussion has been closed.