Home » Developer & Programmer » JDeveloper, Java & XML » Load XML file using xmltype (Oracle 12c, Windows)
Load XML file using xmltype [message #653498] Fri, 08 July 2016 02:11 Go to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
I have created the following XML file and have used the anonymous pl/sql block to read the XML and load my table.
I got the below error. Kindly let me know how to resolve it.
thanks for your support.
select '<?xml version="1.0"?>' as rowheader, 
'' as user_id,															
'' as price_list_file_id,
'' as price_list_group_id,							
'' as price_file_user_publish_flag,	
'' as price_file_user_active_flag,		
'' as price_file_approver_name,			
'' as price_file_approver_id,					
'' as user_appr_request_date,			
'' as price_file_approval_dt,					
'' as last_price_file_access_dt,			
'' as record_created_dt,							
'' as record_modified_dt,							
'' as record_modified_comment,			
'' as record_modified_by,							
'' as record_deactivated_dt,					
'' as record_deactivated_comment,
'' as record_deactivated_by,				
'' as mailsent,
'' as rowfooter
union						
select '<user_pricelist_access>' as rowheader,								
'' as user_id,															
'' as price_list_file_id,
'' as price_list_group_id,							
'' as price_file_user_publish_flag,	
'' as price_file_user_active_flag,		
'' as price_file_approver_name,			
'' as price_file_approver_id,					
'' as user_appr_request_date,			
'' as price_file_approval_dt,					
'' as last_price_file_access_dt,			
'' as record_created_dt,							
'' as record_modified_dt,							
'' as record_modified_comment,			
'' as record_modified_by,							
'' as record_deactivated_dt,					
'' as record_deactivated_comment,
'' as record_deactivated_by,				
'' as mailsent,
'' as rowfooter
union
select '<row>' as rowheader,
'<>'+cast(user_id as varchar(40))+'</>' as user_id,
'<price_list_file_id>'+cast(price_list_file_id as varchar(40))+'</price_list_file_id>' as price_list_file_id,
'<price_list_group_id>'+cast(price_list_group_id as varchar(40))+'</price_list_group_id>' as price_list_group_id,
'<price_file_user_publish_flag>'+cast(price_file_user_publish_flag as varchar(10))+'</price_file_user_publish_flag>' as price_file_user_publish_flag,
'<price_file_user_active_flag>'+cast(price_file_user_active_flag as varchar(10))+'</price_file_user_active_flag>' as price_file_user_active_flag,
'<price_file_approver_name>' + replace(replace(replace(price_file_approver_name,'&','&amp;'),'<','&lt;'),'>','&gt;')+ '</price_file_approver_name>' as price_file_approver_name,
'<price_file_approver_id>' + replace(replace(replace(cast(price_file_approver_id as nvarchar(4000)),'&','&amp;'),'<','&lt;'),'>','&gt;')+ '</price_file_approver_id>' as price_file_approver_id,
'<user_appr_request_date>'+convert(nvarchar(23),user_appr_request_date,121)+'</user_appr_request_date>' as user_appr_request_date,
'<price_file_approval_dt>'+convert(nvarchar(23),price_file_approval_dt,121)+'</price_file_approval_dt>' as price_file_approval_dt,
'<last_price_file_access_dt>'+convert(nvarchar(23),last_price_file_access_dt,121)+'</last_price_file_access_dt>' as last_price_file_access_dt,
'<record_created_dt>'+convert(nvarchar(23),record_created_dt,121)+'</record_created_dt>' as record_created_dt,
'<record_modified_dt>'+convert(nvarchar(23),record_modified_dt,121)+'</record_modified_dt>' as record_modified_dt,
'<record_modified_comment>' + replace(replace(replace(record_modified_comment,'&','&amp;'),'<','&lt;'),'>','&gt;')+ '</record_modified_comment>' as record_modified_comment,
'<record_modified_by>' + replace(replace(replace(record_modified_by,'&','&amp;'),'<','&lt;'),'>','&gt;')+ '</record_modified_by>' as record_modified_by,
'<record_deactivated_dt>'+convert(nvarchar(23),record_deactivated_dt,121)+'</record_deactivated_dt>' as record_deactivated_dt,
'<record_deactivated_comment>' + replace(replace(replace(record_deactivated_comment,'&','&amp;'),'<','&lt;'),'>','&gt;')+ '</record_deactivated_comment>' as record_deactivated_comment,
'<record_deactivated_by>' + replace(replace(replace(record_deactivated_by,'&','&amp;'),'<','&lt;'),'>','&gt;')+ '</record_deactivated_by>' as record_deactivated_by,
'<mailsent>'+cast(mailsent as varchar(10))+'</mailsent>' as mailsent,
'</rowfooter>' as rowfooter
from user_pricelist_access
union						
select '<user_pricelist_access>' as rowheader,								
'' as user_id,															
'' as price_list_file_id,
'' as price_list_group_id,							
'' as price_file_user_publish_flag,	
'' as price_file_user_active_flag,		
'' as price_file_approver_name,			
'' as price_file_approver_id,					
'' as user_appr_request_date,			
'' as price_file_approval_dt,					
'' as last_price_file_access_dt,			
'' as record_created_dt,							
'' as record_modified_dt,							
'' as record_modified_comment,			
'' as record_modified_by,							
'' as record_deactivated_dt,					
'' as record_deactivated_comment,
'' as record_deactivated_by,				
'' as mailsent,
'</user_pricelist_access>' as rowfooter
DECLARE

   acct_doc xmltype := xmltype( bfilename('MIGRATION_DIR','user_pricelist_access.xml')
                              , nls_charset_id('UTF16') );
    
    BEGIN
    
     insert into user_pricelist_access_stg (
      USER_ID,
      PRICE_LIST_FILE_ID,
      PRICE_LIST_GROUP_ID,
      PRICE_FILE_USER_PUBLISH_FLAG,
      PRICE_FILE_USER_ACTIVE_FLAG,
      PRICE_FILE_APPROVER_NAME,
      PRICE_FILE_APPROVER_ID,
      USER_APPR_REQUEST_DATE,
      PRICE_FILE_APPROVAL_DT,
      LAST_PRICE_FILE_ACCESS_DT,
      RECORD_CREATED_DT,
      RECORD_MODIFIED_DT,
      RECORD_MODIFIED_COMMENT,
      RECORD_MODIFIED_BY,
      RECORD_DEACTIVATED_DT,
      RECORD_DEACTIVATED_COMMENT,
      RECORD_DEACTIVATED_BY,
      MAILSENT
      )
     select *
    from xmltable(
    '/user_pricelist_access/row'
     passing acct_doc
     columns 
      USER_ID	NUMBER(10,0) path 'user_id',
      PRICE_LIST_FILE_ID	NUMBER(18,0) path 'price_list_file_id',
      PRICE_LIST_GROUP_ID	NUMBER(18,0) path 'price_list_group_id',
      PRICE_FILE_USER_PUBLISH_FLAG	NUMBER(1,0) path 'price_file_user_publish_flag',
      PRICE_FILE_USER_ACTIVE_FLAG	NUMBER(1,0) path 'price_file_user_active_flag',
      PRICE_FILE_APPROVER_NAME	VARCHAR2(50 CHAR) path 'price_file_approver_name',
      PRICE_FILE_APPROVER_ID	CLOB path 'price_file_approver_id',
      USER_APPR_REQUEST_DATE	TIMESTAMP(6) path 'user_appr_request_date',
      PRICE_FILE_APPROVAL_DT	TIMESTAMP(6) path 'price_file_approval_dt',
      LAST_PRICE_FILE_ACCESS_DT	TIMESTAMP(6) path 'last_price_file_access_dt',
      RECORD_CREATED_DT	TIMESTAMP(6) path 'record_created_dt',
      RECORD_MODIFIED_DT	TIMESTAMP(6) path 'record_modified_dt',
      RECORD_MODIFIED_COMMENT	VARCHAR2(50 CHAR) path 'record_modified_comment',
      RECORD_MODIFIED_BY	VARCHAR2(50 CHAR) path 'record_modified_by',
      RECORD_DEACTIVATED_DT	TIMESTAMP(6) path 'record_deactivated_dt',
      RECORD_DEACTIVATED_COMMENT	VARCHAR2(50 CHAR) path 'record_deactivated_comment',
      RECORD_DEACTIVATED_BY	VARCHAR2(50 CHAR) path 'record_deactivated_by',
      MAILSENT	NUMBER(1,0) path 'mailsent'
    );
   END;
Re: Load XML file using xmltype [message #653500 is a reply to message #653498] Fri, 08 July 2016 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There are plenty of examples in our XML forum (where I moved your topic), just search for "bfilename" and/or "xmltable" in it.

Re: Load XML file using xmltype [message #653508 is a reply to message #653498] Fri, 08 July 2016 15:06 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Quote:

...I have created the following XML file ...


No, you haven't. The code that you posted is invalid in so many ways, missing from clauses, plus signs instead of concatenation, no resemblance to valid xml.

Quote:

... and have used the anonymous pl/sql block to read the XML and load my table....


How can you load something that doesn't exist?

Quote:

...I got the below error....


What error? Where? I don't see any error.

You need to start over and confirm that one piece at a time works. Make sure you actually have xml data before you try to load it.

There is no need for PL/SQL. Just use a SQL insert statement, but make sure the select that it uses works first, before you try to put that select in an insert.

If you expect help, then you need to post create table and insert statements for sample data used to create your xml data or at least post the created xml data. You should also explain what your ultimate goal is.

Previous Topic: FOR ORDINALITY Read order
Next Topic: Dynamic splitting of column
Goto Forum:
  


Current Time: Thu Mar 28 15:27:36 CDT 2024