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;