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
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