XML File data to Database Table
1> Get the xml file,
>> If it is from any concurrent request, get it from server and change the extension from .out to .xml
select outfile_name
from fnd_concurrent_requests
where request_id = <request id> ;
2> create table xx_xml_tst_tbl (
xml_data xmltype
,request_id number
);
3> Create a directory, link to any appropriate directory of Database tier.
>> Here I am using '/usr/tmp' directory.
Run this sql script.
CREATE directory XXHW_DOC_DWNLD AS '/usr/tmp';
4> Place the converted .xml file to /usr/tmp -- DB Tier
5> Get the characterset used and insert the xml data into the table we created above
select value
from nls_database_parameters
where parameter='NLS_CHARACTERSET';
INSERT INTO xx_xml_tst_tbl
VALUES (XMLTYPE(bfilename('XXHW_XML_DWNLD', 'o2438606V2.xml'), nls_charset_id('AL32UTF8')),2438606)
6> Now get the xml data into columns and rows.
Example 1: Test data(Basic)
<?xml version="1.0" ?>
- <PONOREC>
- <NORECEIPT_DETAILS>
<Created>24-JUN-12</Created>
<Requisition_x0020_Num>111001000058</Requisition_x0020_Num>
<PO_x0020_Number>111001000052</PO_x0020_Number>
<RECEIVING_STATUS>Not Received</RECEIVING_STATUS>
<Business_x0020_Unit>XXX - School of Life</Business_x0020_Unit>
<Type>Standard Purchase Order</Type>
<Supplier_Name>Hospitality Services - Internal</Supplier_Name>
<Supplier_Site>CATERING</Supplier_Site>
</NORECEIPT_DETAILS>
- <NORECEIPT_DETAILS>
<Created>20-DEC-12</Created>
<Requisition_x0020_Num>666601002002</Requisition_x0020_Num>
<PO_x0020_Number>666601001860</PO_x0020_Number>
<RECEIVING_STATUS>Partially Received</RECEIVING_STATUS>
<Business_x0020_Unit>XXX - Professional Services</Business_x0020_Unit>
<Type>Standard Purchase Order</Type>
<Supplier_Name>Testing LTD</Supplier_Name>
<Supplier_Site>HERTS</Supplier_Site>
</NORECEIPT_DETAILS>
- <NORECEIPT_DETAILS>
<Created>11-JAN-13</Created>
<Requisition_x0020_Num>666601002091</Requisition_x0020_Num>
<PO_x0020_Number>666601001936</PO_x0020_Number>
<RECEIVING_STATUS>Partially Received</RECEIVING_STATUS>
<Business_x0020_Unit>XXX - Professional Services</Business_x0020_Unit>
<Type>Standard Purchase Order</Type>
<Supplier_Name>Testing2 LTD</Supplier_Name>
<Supplier_Site>EDINBURGH</Supplier_Site>
</NORECEIPT_DETAILS>
- <NORECEIPT_DETAILS>
<Created>11-JAN-13</Created>
<Requisition_x0020_Num>666601002094</Requisition_x0020_Num>
<PO_x0020_Number>666601001937</PO_x0020_Number>
<RECEIVING_STATUS>Partially Received</RECEIVING_STATUS>
<Business_x0020_Unit>XXX - Professional Services</Business_x0020_Unit>
<Type>Standard Purchase Order</Type>
<Supplier_Name>CHARITABLE SERVICES</Supplier_Name>
<Supplier_Site>MACCLESFIELD</Supplier_Site>
</NORECEIPT_DETAILS>
</PONOREC>
> Get the data
SELECT evt.*
from xx_xml_tst_tbl xxt
,xmltable('/PONOREC/NORECEIPT_DETAILS'
passing (xxt.xml_data)
columns Created varchar2(40) path 'Created' -- <XML Tag name>
,Requi_num varchar2(40) path 'Requisition_x0020_Num'
,PO_Num varchar2(40) path 'PO_x0020_Number'
,Status varchar2(100) path 'RECEIVING_STATUS'
,BU varchar2(240) path 'Business_x0020_Unit'
,Type varchar2(40) path 'Type'
,Supplier_Name varchar2(256) path 'Supplier_Name'
,Supplier_Site varchar2(256) path 'Supplier_Site'
) evt
where request_id = 2438606
order by evt.Requi_num ;
> Sample Output
Example 2 : Test data (with two layers)
INSERT INTO xx_xml_tst_tbl
VALUES (XMLTYPE(bfilename('XXHW_XML_DWNLD', 'o2421932V2.xml'), nls_charset_id('AL32UTF8')),2421932)
<?xml version="1.0" encoding="UTF-8"?>
<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<XXHWAPSRA>
<LIST_G_SEL_CHECKS>
<G_SEL_CHECKS>
<VENDOR_TYPE_LOOKUP_CODE>VENDOR</VENDOR_TYPE_LOOKUP_CODE>
<C_VENDOR_NUMBER>10000155</C_VENDOR_NUMBER>
<VENDOR_NAME>VENDOR11</VENDOR_NAME>
<PAYMENT_NUMBER_>98010</PAYMENT_NUMBER_>
<LIST_G_SEL_INV_DATA>
<G_SEL_INV_DATA>
<C_IP_CHECK_ID>98010</C_IP_CHECK_ID>
<C_INVOICE_DATE>28-APR-13</C_INVOICE_DATE>
<C_INVOICE_NUM>123456</C_INVOICE_NUM>
</G_SEL_INV_DATA>
</LIST_G_SEL_INV_DATA>
</G_SEL_CHECKS>
<G_SEL_CHECKS>
<VENDOR_TYPE_LOOKUP_CODE>VENDOR</VENDOR_TYPE_LOOKUP_CODE>
<C_VENDOR_NUMBER>10002911</C_VENDOR_NUMBER>
<VENDOR_NAME>VENDOR22</VENDOR_NAME>
<PAYMENT_NUMBER_>98011</PAYMENT_NUMBER_>
<LIST_G_SEL_INV_DATA>
<G_SEL_INV_DATA>
<C_IP_CHECK_ID>98011</C_IP_CHECK_ID>
<C_INVOICE_DATE>28-APR-13</C_INVOICE_DATE>
<C_INVOICE_NUM>1234567</C_INVOICE_NUM>
</G_SEL_INV_DATA>
</LIST_G_SEL_INV_DATA>
</G_SEL_CHECKS>
<G_SEL_CHECKS>
<VENDOR_TYPE_LOOKUP_CODE>VENDOR</VENDOR_TYPE_LOOKUP_CODE>
<C_VENDOR_NUMBER>863</C_VENDOR_NUMBER>
<VENDOR_NAME>VENDOR33</VENDOR_NAME>
<PAYMENT_NUMBER_>98012</PAYMENT_NUMBER_>
<LIST_G_SEL_INV_DATA>
<G_SEL_INV_DATA>
<C_IP_CHECK_ID>98012</C_IP_CHECK_ID>
<C_INVOICE_DATE>29-APR-13</C_INVOICE_DATE>
<C_INVOICE_NUM>12345678</C_INVOICE_NUM>
</G_SEL_INV_DATA>
</LIST_G_SEL_INV_DATA>
<LIST_G_SEL_INV_DATA>
<G_SEL_INV_DATA>
<C_IP_CHECK_ID>98012_1</C_IP_CHECK_ID>
<C_INVOICE_DATE>29-APR-13</C_INVOICE_DATE>
<C_INVOICE_NUM>12345678_1</C_INVOICE_NUM>
</G_SEL_INV_DATA>
</LIST_G_SEL_INV_DATA>
</G_SEL_CHECKS>
</LIST_G_SEL_CHECKS>
</XXHWAPSRA>
> Get the data
SELECT hed.Vendor_Type
,hed.vendor_num
,hed.payment_num
,hed.vendor_name
,evt.*
from xx_xml_tst_tbl xxt
,xmltable('/XXHWAPSRA/LIST_G_SEL_CHECKS/G_SEL_CHECKS'
passing (xxt.xml_data)
columns Vendor_Type varchar2(40) path 'VENDOR_TYPE_LOOKUP_CODE'
,Vendor_Num varchar2(40) path 'C_VENDOR_NUMBER'
,Vendor_Name varchar2(246) path 'VENDOR_NAME'
,Payment_Num varchar2(40) path 'PAYMENT_NUMBER_'
,evt xmltype path 'LIST_G_SEL_INV_DATA' -- Inner Layer
) Hed
,xmltable('/LIST_G_SEL_INV_DATA/G_SEL_INV_DATA'
passing (Hed.evt)
columns Check_id varchar2(40) path 'C_IP_CHECK_ID'
,Inv_Date varchar2(40) path 'C_INVOICE_DATE'
,Inv_Num varchar2(40) path 'C_INVOICE_NUM'
) evt
where request_id = 2421932
order by hed.vendor_num ;
> Sample Output
>> If you want to store these data into table.
> Create a table
create table xx_requi_tbl (created varchar2(20), requi_num varchar2(40), po_num varchar2(40), status varchar2(100), bu varchar2(240), type varchar2(40), supplier_name varchar2(256), supplier_site varchar2(256)) ;
>> Insert into table
declare
v_xml_data xmltype := xmltype(bfilename('XXHW_XML_DWNLD', 'o2438606V2.xml'), nls_charset_id('AL32UTF8')) ;
begin
insert into xx_requi_tbl (created, requi_num, po_num, status, bu, type, supplier_name, supplier_site)
select evt.*
from xmltable('/PONOREC/NORECEIPT_DETAILS'
passing (v_xml_data)
columns created varchar2(20) path 'Created'
,requi_num varchar2(40) path 'Requisition_x0020_Num'
,po_num varchar2(40) path 'PO_x0020_Number'
,status varchar2(100) path 'RECEIVING_STATUS'
,bu varchar2(240) path 'Business_x0020_Unit'
,type varchar2(40) path 'Type'
,supplier_name varchar2(256) path 'Supplier_Name'
,supplier_site varchar2(256) path 'Supplier_Site'
) evt ;
commit;
exception
when others then
dbms_output.put_line('Error: '||sqlerrm);
end;
> Select the data
select *
from xx_requi_tbl ;
> Sample Output
1> Get the xml file,
>> If it is from any concurrent request, get it from server and change the extension from .out to .xml
select outfile_name
from fnd_concurrent_requests
where request_id = <request id> ;
2> create table xx_xml_tst_tbl (
xml_data xmltype
,request_id number
);
3> Create a directory, link to any appropriate directory of Database tier.
>> Here I am using '/usr/tmp' directory.
Run this sql script.
CREATE directory XXHW_DOC_DWNLD AS '/usr/tmp';
4> Place the converted .xml file to /usr/tmp -- DB Tier
5> Get the characterset used and insert the xml data into the table we created above
select value
from nls_database_parameters
where parameter='NLS_CHARACTERSET';
INSERT INTO xx_xml_tst_tbl
VALUES (XMLTYPE(bfilename('XXHW_XML_DWNLD', 'o2438606V2.xml'), nls_charset_id('AL32UTF8')),2438606)
6> Now get the xml data into columns and rows.
Example 1: Test data(Basic)
<?xml version="1.0" ?>
- <PONOREC>
- <NORECEIPT_DETAILS>
<Created>24-JUN-12</Created>
<Requisition_x0020_Num>111001000058</Requisition_x0020_Num>
<PO_x0020_Number>111001000052</PO_x0020_Number>
<RECEIVING_STATUS>Not Received</RECEIVING_STATUS>
<Business_x0020_Unit>XXX - School of Life</Business_x0020_Unit>
<Type>Standard Purchase Order</Type>
<Supplier_Name>Hospitality Services - Internal</Supplier_Name>
<Supplier_Site>CATERING</Supplier_Site>
</NORECEIPT_DETAILS>
- <NORECEIPT_DETAILS>
<Created>20-DEC-12</Created>
<Requisition_x0020_Num>666601002002</Requisition_x0020_Num>
<PO_x0020_Number>666601001860</PO_x0020_Number>
<RECEIVING_STATUS>Partially Received</RECEIVING_STATUS>
<Business_x0020_Unit>XXX - Professional Services</Business_x0020_Unit>
<Type>Standard Purchase Order</Type>
<Supplier_Name>Testing LTD</Supplier_Name>
<Supplier_Site>HERTS</Supplier_Site>
</NORECEIPT_DETAILS>
- <NORECEIPT_DETAILS>
<Created>11-JAN-13</Created>
<Requisition_x0020_Num>666601002091</Requisition_x0020_Num>
<PO_x0020_Number>666601001936</PO_x0020_Number>
<RECEIVING_STATUS>Partially Received</RECEIVING_STATUS>
<Business_x0020_Unit>XXX - Professional Services</Business_x0020_Unit>
<Type>Standard Purchase Order</Type>
<Supplier_Name>Testing2 LTD</Supplier_Name>
<Supplier_Site>EDINBURGH</Supplier_Site>
</NORECEIPT_DETAILS>
- <NORECEIPT_DETAILS>
<Created>11-JAN-13</Created>
<Requisition_x0020_Num>666601002094</Requisition_x0020_Num>
<PO_x0020_Number>666601001937</PO_x0020_Number>
<RECEIVING_STATUS>Partially Received</RECEIVING_STATUS>
<Business_x0020_Unit>XXX - Professional Services</Business_x0020_Unit>
<Type>Standard Purchase Order</Type>
<Supplier_Name>CHARITABLE SERVICES</Supplier_Name>
<Supplier_Site>MACCLESFIELD</Supplier_Site>
</NORECEIPT_DETAILS>
</PONOREC>
> Get the data
SELECT evt.*
from xx_xml_tst_tbl xxt
,xmltable('/PONOREC/NORECEIPT_DETAILS'
passing (xxt.xml_data)
columns Created varchar2(40) path 'Created' -- <XML Tag name>
,Requi_num varchar2(40) path 'Requisition_x0020_Num'
,PO_Num varchar2(40) path 'PO_x0020_Number'
,Status varchar2(100) path 'RECEIVING_STATUS'
,BU varchar2(240) path 'Business_x0020_Unit'
,Type varchar2(40) path 'Type'
,Supplier_Name varchar2(256) path 'Supplier_Name'
,Supplier_Site varchar2(256) path 'Supplier_Site'
) evt
where request_id = 2438606
order by evt.Requi_num ;
> Sample Output
Example 2 : Test data (with two layers)
INSERT INTO xx_xml_tst_tbl
VALUES (XMLTYPE(bfilename('XXHW_XML_DWNLD', 'o2421932V2.xml'), nls_charset_id('AL32UTF8')),2421932)
<?xml version="1.0" encoding="UTF-8"?>
<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<XXHWAPSRA>
<LIST_G_SEL_CHECKS>
<G_SEL_CHECKS>
<VENDOR_TYPE_LOOKUP_CODE>VENDOR</VENDOR_TYPE_LOOKUP_CODE>
<C_VENDOR_NUMBER>10000155</C_VENDOR_NUMBER>
<VENDOR_NAME>VENDOR11</VENDOR_NAME>
<PAYMENT_NUMBER_>98010</PAYMENT_NUMBER_>
<LIST_G_SEL_INV_DATA>
<G_SEL_INV_DATA>
<C_IP_CHECK_ID>98010</C_IP_CHECK_ID>
<C_INVOICE_DATE>28-APR-13</C_INVOICE_DATE>
<C_INVOICE_NUM>123456</C_INVOICE_NUM>
</G_SEL_INV_DATA>
</LIST_G_SEL_INV_DATA>
</G_SEL_CHECKS>
<G_SEL_CHECKS>
<VENDOR_TYPE_LOOKUP_CODE>VENDOR</VENDOR_TYPE_LOOKUP_CODE>
<C_VENDOR_NUMBER>10002911</C_VENDOR_NUMBER>
<VENDOR_NAME>VENDOR22</VENDOR_NAME>
<PAYMENT_NUMBER_>98011</PAYMENT_NUMBER_>
<LIST_G_SEL_INV_DATA>
<G_SEL_INV_DATA>
<C_IP_CHECK_ID>98011</C_IP_CHECK_ID>
<C_INVOICE_DATE>28-APR-13</C_INVOICE_DATE>
<C_INVOICE_NUM>1234567</C_INVOICE_NUM>
</G_SEL_INV_DATA>
</LIST_G_SEL_INV_DATA>
</G_SEL_CHECKS>
<G_SEL_CHECKS>
<VENDOR_TYPE_LOOKUP_CODE>VENDOR</VENDOR_TYPE_LOOKUP_CODE>
<C_VENDOR_NUMBER>863</C_VENDOR_NUMBER>
<VENDOR_NAME>VENDOR33</VENDOR_NAME>
<PAYMENT_NUMBER_>98012</PAYMENT_NUMBER_>
<LIST_G_SEL_INV_DATA>
<G_SEL_INV_DATA>
<C_IP_CHECK_ID>98012</C_IP_CHECK_ID>
<C_INVOICE_DATE>29-APR-13</C_INVOICE_DATE>
<C_INVOICE_NUM>12345678</C_INVOICE_NUM>
</G_SEL_INV_DATA>
</LIST_G_SEL_INV_DATA>
<LIST_G_SEL_INV_DATA>
<G_SEL_INV_DATA>
<C_IP_CHECK_ID>98012_1</C_IP_CHECK_ID>
<C_INVOICE_DATE>29-APR-13</C_INVOICE_DATE>
<C_INVOICE_NUM>12345678_1</C_INVOICE_NUM>
</G_SEL_INV_DATA>
</LIST_G_SEL_INV_DATA>
</G_SEL_CHECKS>
</LIST_G_SEL_CHECKS>
</XXHWAPSRA>
> Get the data
SELECT hed.Vendor_Type
,hed.vendor_num
,hed.payment_num
,hed.vendor_name
,evt.*
from xx_xml_tst_tbl xxt
,xmltable('/XXHWAPSRA/LIST_G_SEL_CHECKS/G_SEL_CHECKS'
passing (xxt.xml_data)
columns Vendor_Type varchar2(40) path 'VENDOR_TYPE_LOOKUP_CODE'
,Vendor_Num varchar2(40) path 'C_VENDOR_NUMBER'
,Vendor_Name varchar2(246) path 'VENDOR_NAME'
,Payment_Num varchar2(40) path 'PAYMENT_NUMBER_'
,evt xmltype path 'LIST_G_SEL_INV_DATA' -- Inner Layer
) Hed
,xmltable('/LIST_G_SEL_INV_DATA/G_SEL_INV_DATA'
passing (Hed.evt)
columns Check_id varchar2(40) path 'C_IP_CHECK_ID'
,Inv_Date varchar2(40) path 'C_INVOICE_DATE'
,Inv_Num varchar2(40) path 'C_INVOICE_NUM'
) evt
where request_id = 2421932
order by hed.vendor_num ;
> Sample Output
>> If you want to store these data into table.
> Create a table
create table xx_requi_tbl (created varchar2(20), requi_num varchar2(40), po_num varchar2(40), status varchar2(100), bu varchar2(240), type varchar2(40), supplier_name varchar2(256), supplier_site varchar2(256)) ;
>> Insert into table
declare
v_xml_data xmltype := xmltype(bfilename('XXHW_XML_DWNLD', 'o2438606V2.xml'), nls_charset_id('AL32UTF8')) ;
begin
insert into xx_requi_tbl (created, requi_num, po_num, status, bu, type, supplier_name, supplier_site)
select evt.*
from xmltable('/PONOREC/NORECEIPT_DETAILS'
passing (v_xml_data)
columns created varchar2(20) path 'Created'
,requi_num varchar2(40) path 'Requisition_x0020_Num'
,po_num varchar2(40) path 'PO_x0020_Number'
,status varchar2(100) path 'RECEIVING_STATUS'
,bu varchar2(240) path 'Business_x0020_Unit'
,type varchar2(40) path 'Type'
,supplier_name varchar2(256) path 'Supplier_Name'
,supplier_site varchar2(256) path 'Supplier_Site'
) evt ;
commit;
exception
when others then
dbms_output.put_line('Error: '||sqlerrm);
end;
> Select the data
select *
from xx_requi_tbl ;
> Sample Output
No comments:
Post a Comment