Following is the script to insert XML
data from a file to oracle database tables
INSERT INTO XX_ONRPTS_STG_TAB (DOCUMENT_NUM,
HEADER_ID,
RELEASE_NO,
RELEASE_ID,
DOCUMENT_LINE_NUM,
ITEM_NUM,
REC_QUANTITY,
LINE_ID,
LINE_LOCATION_ID,
TRANSACTION_ID,
FILE_NAME)
SELECT X1.DOCUMENT_NUM,
X1.HEADER_ID,
X1.RELEASE_NO,
X1.RELEASE_ID,
X2.DOCUMENT_LINE_NUM,
X2.ITEM_NUM,
X2.REC_QUANTITY,
X2.LINE_ID,
X2.LINE_LOCATION_ID,
XX_ONRPTS_STG_TAB_S.NEXTVAL,
piv_file_name
FROM XMLTABLE (
'/ORDER/PO/PO'
PASSING ACCT_DOC
COLUMNS HEADER_NO FOR ORDINALITY,
DOCUMENT_NUM VARCHAR2
(240) PATH 'PO_NUMBER',
HEADER_ID NUMBER
PATH 'HEADER_ID',
RELEASE_NO NUMBER
PATH 'RELEASE_NO',
RELEASE_ID NUMBER
PATH 'RELEASE_ID') X1,
XMLTABLE (
'$d/ORDER/PO_LINE[$hn]/PO_LINE'
PASSING ACCT_DOC AS "d",
X1.HEADER_NO AS "hn"
COLUMNS
DOCUMENT_LINE_NUM VARCHAR2 (240) PATH 'LINE_NUM',
ITEM_NUM VARCHAR2
(240) PATH 'PART_NO',
REC_QUANTITY NUMBER
PATH 'QUANTITY',
LINE_ID NUMBER PATH
'LINE_ID',
LINE_LOCATION_ID NUMBER PATH 'LINE_LOCATION_ID') X2;
Following
the sample XML data file:
