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  |
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,'&','&'),'<','<'),'>','>')+ '</price_file_approver_name>' as price_file_approver_name,
'<price_file_approver_id>' + replace(replace(replace(cast(price_file_approver_id as nvarchar(4000)),'&','&'),'<','<'),'>','>')+ '</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,'&','&'),'<','<'),'>','>')+ '</record_modified_comment>' as record_modified_comment,
'<record_modified_by>' + replace(replace(replace(record_modified_by,'&','&'),'<','<'),'>','>')+ '</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,'&','&'),'<','<'),'>','>')+ '</record_deactivated_comment>' as record_deactivated_comment,
'<record_deactivated_by>' + replace(replace(replace(record_deactivated_by,'&','&'),'<','<'),'>','>')+ '</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 #653508 is a reply to message #653498] |
Fri, 08 July 2016 15:06  |
 |
Barbara Boehmer
Messages: 9104
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.
|
|
|
|
Goto Forum:
Current Time: Mon Feb 03 17:45:19 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
