Skip to Main Content
  • Questions
  • Uploading Data from xml file into table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajuri.

Asked: May 20, 2024 - 11:50 am UTC

Last updated: May 30, 2024 - 6:00 am UTC

Version: 19.0

Viewed 1000+ times

You Asked

I have an issue, where i am trying to upload data from my xml file into the table using oracle apex data parser api . I have 6 columns in xml file and in which 1 column is not having any data in any of the records . When i try to insert the data , instead of showing 6 columns it is showing only the 5 columns which has the data and its skipping the column with empty values. I want the empty column also to be shown in the table because the xml file comes dynamically and i am not sure whether the column will have values or not.

Below is my xml file data
<ROW>
<PROJECT_NAME>abc</PROJECT_NAME>
<CLASS_CATEGORY>xyz</CLASS_CATEGORY>
<CLASS_CODE>Capital</CLASS_CODE>
<CODE_PERCENTAGE></CODE_PERCENTAGE>
<PROJECT_ID>300000074</XX_PROJECT_ID>
<CLASSIFICATION_ID>300000</XX_CLASSIFICATION_ID>
</ROW>
........so on


Below is the apex_data_parser code

SELECT * FROM ABCD ofdt
, TABLE ( apex_data_parser.parse ( p_content => ofdt.BLOB_CONTENT
, p_file_name => 'sample.xml' )
) blob_data
WHERE ofdt.id = 10939

and Connor said...

By default we'll skip empty XML tags if we don't find any null values in the rows we initally process, eg

SQL> create table abcd ( BLOB_CONTENT blob );

Table created.

SQL>
SQL> begin
  2  insert into abcd values (
  3  utl_raw.cast_to_raw(
  4  '<?xml version="1.0"?>
  5  <ROWSET>
  6  <ROW>
  7  <PROJECT_NAME>abc</PROJECT_NAME>
  8  <CLASS_CATEGORY>xyz</CLASS_CATEGORY>
  9  <CLASS_CODE>Capital</CLASS_CODE>
 10  <CODE_PERCENTAGE></CODE_PERCENTAGE>
 11  <PROJECT_ID>300000074</PROJECT_ID>
 12  <CLASSIFICATION_ID>300000</CLASSIFICATION_ID>
 13  </ROW>
 14  </ROWSET>
 15  '));
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT col001,col002,col003,col004,col005,col006
  2    FROM ABCD ofdt
  3    , TABLE ( apex_data_parser.parse ( p_content => ofdt.BLOB_CONTENT
  4    , p_file_name => 'sample.xml'
  5    )
  6    ) blob_data;

COL001       COL002       COL003       COL004       COL005       COL006
------------ ------------ ------------ ------------ ------------ ------------
abc          xyz          Capital      300000074    300000


but if there was a second row with a value...

SQL> delete abcd;

1 row deleted.

SQL>
SQL> begin
  2  insert into abcd values (
  3  utl_raw.cast_to_raw(
  4  '<?xml version="1.0"?>
  5  <ROWSET>
  6  <ROW>
  7  <PROJECT_NAME>abc</PROJECT_NAME>
  8  <CLASS_CATEGORY>xyz</CLASS_CATEGORY>
  9  <CLASS_CODE>Capital</CLASS_CODE>
 10  <CODE_PERCENTAGE></CODE_PERCENTAGE>
 11  <PROJECT_ID>300000074</PROJECT_ID>
 12  <CLASSIFICATION_ID>300000</CLASSIFICATION_ID>
 13  </ROW>
 14  <ROW>
 15  <PROJECT_NAME>abc</PROJECT_NAME>
 16  <CLASS_CATEGORY>xyz</CLASS_CATEGORY>
 17  <CLASS_CODE>Capital</CLASS_CODE>
 18  <CODE_PERCENTAGE>HERE</CODE_PERCENTAGE>
 19  <PROJECT_ID>300000074</PROJECT_ID>
 20  <CLASSIFICATION_ID>300000</CLASSIFICATION_ID>
 21  </ROW>
 22  </ROWSET>
 23  '));
 24  end;
 25  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT col001,col002,col003,col004,col005,col006
  2    FROM ABCD ofdt
  3    , TABLE ( apex_data_parser.parse ( p_content => ofdt.BLOB_CONTENT
  4    , p_file_name => 'sample.xml'
  5    )
  6    ) blob_data;

COL001       COL002       COL003       COL004       COL005       COL006
------------ ------------ ------------ ------------ ------------ ------------
abc          xyz          Capital                   300000074    300000
abc          xyz          Capital      HERE         300000074    300000



Also, you can use the profile parameters to explicitly control things if you like

SQL> delete abcd;

1 row deleted.

SQL>
SQL> begin
  2  insert into abcd values (
  3  utl_raw.cast_to_raw(
  4  '<?xml version="1.0"?>
  5  <ROWSET>
  6  <ROW>
  7  <PROJECT_NAME>abc</PROJECT_NAME>
  8  <CLASS_CATEGORY>xyz</CLASS_CATEGORY>
  9  <CLASS_CODE>Capital</CLASS_CODE>
 10  <CODE_PERCENTAGE></CODE_PERCENTAGE>
 11  <PROJECT_ID>300000074</PROJECT_ID>
 12  <CLASSIFICATION_ID>300000</CLASSIFICATION_ID>
 13  </ROW>
 14  </ROWSET>
 15  '));
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> SELECT col001,col002,col003,col004,col005,col006
  2    FROM ABCD ofdt
  3    , TABLE ( apex_data_parser.parse ( p_content => ofdt.BLOB_CONTENT
  4    , p_file_name => 'sample.xml'
  5    ,p_file_profile => '{
  6                                          "row-selector":"//ROW",
  7                                          "columns": [
  8                                                {"name":"PROJECT_NAME","selector":"PROJECT_NAME"},
  9                                                {"name":"CLASS_CATEGORY","selector":"CLASS_CATEGORY"},
 10                                                {"name":"CLASS_CODE","selector":"CLASS_CODE"},
 11                                                {"name":"CODE_PERCENTAGE","selector":"CODE_PERCENTAGE"},
 12                                                {"name":"PROJECT_ID","selector":"PROJECT_ID"},
 13                                              {"name":"CLASSIFICATION_ID","selector":"CLASSIFICATION_ID"}]}'
 14    )
 15    ) blob_data;

COL001       COL002       COL003       COL004       COL005       COL006
------------ ------------ ------------ ------------ ------------ ------------
abc          xyz          Capital                   300000074    300000



Thanks to Carsten from the APEX team for the info on file profiles

Rating

  (2 ratings)

Comments

A reader, May 27, 2024 - 1:17 pm UTC

Thanks for the help Connor,

SELECT col001,col002,col003,col004,col005,col006
FROM ABCD ofdt
, TABLE ( apex_data_parser.parse ( p_content => ofdt.BLOB_CONTENT
, p_file_name => 'sample.xml'
,p_file_profile => '{
"row-selector":"//ROW",
"columns": [
{"name":"PROJECT_NAME","selector":"PROJECT_NAME"},
{"name":"CLASS_CATEGORY","selector":"CLASS_CATEGORY"},
{"name":"CLASS_CODE","selector":"CLASS_CODE"},
{"name":"CODE_PERCENTAGE","selector":"CODE_PERCENTAGE"},
{"name":"PROJECT_ID","selector":"PROJECT_ID"},
{"name":"CLASSIFICATION_ID","selector":"CLASSIFICATION_ID"}]}'
)
) blob_data;




The above code works fine if i know the column names from the file before parsing but according to my requirement , the file which i get is dynamic and i dont know the column names before parsing the file. So is there any solution like above which works fine for the dynamic columns.
Connor McDonald
May 30, 2024 - 6:00 am UTC

See the next comment. APEX_DATA_PARSER is going to try look ahead to work this out.

Detecting the profile automatically

mathguy, May 27, 2024 - 11:28 pm UTC

APEX_DATA_PARSER.DISCOVER can probably be used to retrieve the profile dynamically (and the result can be passed as the profile argument to PARSE). It will likely suffer from the same issue though. Not sure if it can be made to include all "columns", including those without data.

Connor McDonald
May 30, 2024 - 6:00 am UTC

+1

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here