Hi,
I am having one requirement though the xml file is having lot of tags and elements, its altogether the brief scenario is that one Purchase order has two lines but there are lot of the tags where I am getting skeptical of loading that data into oracle table. Gurus please help me its urgent. Here I am pasting the xml file data
<GEBIZ_PURCHASE_ORDER>
<HEADER>
<ORDER_CODE>external </ORDER_CODE>
<EXTERNAL_SYSTEM_CODE>E</EXTERNAL_SYSTEM_CODE>
<AMENDMENT_NUMBER>1</AMENDMENT_NUMBER>
<VARIATION_NUMBER>1</VARIATION_NUMBER>
<DESCRIPTION>document</DESCRIPTION>
<FINANCIAL_SYSTEM>
<SUB_BUSINESS_UNIT>rp</SUB_BUSINESS_UNIT>
<NFS>
<COST_CENTER_GROUP>21</COST_CENTER_GROUP>
<BUYER_CODE>121</BUYER_CODE>
<FINANCIAL_SYSTEM_ORDER_CODE>23</FINANCIAL_SYSTEM_ORDER_CODE>
<USER_NRIC>8</USER_NRIC>
</NFS>
<SAP>
<PURCHASING_GROUP>3</PURCHASING_GROUP>
</SAP>
</FINANCIAL_SYSTEM>
<STATUS>ELEMENT</STATUS>
<SUPPLIER>
<CODE>3323</CODE>
<NAME>John Grisham</NAME>
<GST_NUMBER>122</GST_NUMBER>
<SITES>
<SITE>
<ID>122</ID>
<NAME>WDE_TS</NAME>
<PHONE>47474</PHONE>
<PHONE_EXTENSION>323</PHONE_EXTENSION>
<FAX>3747-399</FAX>
<TELEX>U383</TELEX>
<EMAIL>h37u0@gmail.com</EMAIL>
<ADDRESS_LINE1>hougang avenue 8,</ADDRESS_LINE1>
<ADDRESS_LINE2>1223 </ADDRESS_LINE2>
<ADDRESS_LINE3>test </ADDRESS_LINE3>
<COUNTRY_CODE>singapore</COUNTRY_CODE>
<PROVINCE> </PROVINCE>
<STATE></STATE>
<CITY>singapore</CITY>
<AREA_CODE>123</AREA_CODE>
<ZIP>530433</ZIP>
<REGION_CODE>UAT</REGION_CODE>
</SITE>
</SITES>
<CONTACT_NAME>SHING </CONTACT_NAME>
<CONTACT_PHONE>23434</CONTACT_PHONE>
<CONTACT_EMAIL>salk@gmail.com</CONTACT_EMAIL>
<CONTACT_FAX> </CONTACT_FAX>
</SUPPLIER>
<USER>
<USER_CODE>RP</USER_CODE>
<ORGANISATION_NAME>RP COLLEGE</ORGANISATION_NAME>
</USER>
<TOTAL_AMOUNT>
<CURRENCY_CODE>SGD</CURRENCY_CODE>
<CURRENCY_RATE>1.8</CURRENCY_RATE>
<CURRENCY_RATE_DATE>24-FEB-2011</CURRENCY_RATE_DATE>
<CURRENCY_RATE_TYPE>1</CURRENCY_RATE_TYPE>
<CURRENCY_AMOUNT>13</CURRENCY_AMOUNT>
</TOTAL_AMOUNT>
<PERIOD_CONTRACT>
<CODE>JUN</CODE>
<AGENCY_CODE>SLIKJ</AGENCY_CODE>
<ADMIN_FEE_SGD_AMOUNT>11</ADMIN_FEE_SGD_AMOUNT>
</PERIOD_CONTRACT>
<PAYMENT_TERMS>10</PAYMENT_TERMS>
<BILL_TO>SHING</BILL_TO>
<JUSTIFICATION></JUSTIFICATION>
<BUYER>
<ORGANISATION_CODE>RP</ORGANISATION_CODE>
<ORGANISATION_NAME>RP</ORGANISATION_NAME>
<NAME>RP </NAME>
<PHONE>13113</PHONE>
<FAX> </FAX>
<EMAIL> </EMAIL>
</BUYER>
<MINISTRY_CODE>23</MINISTRY_CODE>
<DEPARTMENT_CODE>LOGISTICTS</DEPARTMENT_CODE>
<CREATE_TIMESTAMP>12</CREATE_TIMESTAMP>
<TERMINATE_REASON>FAILED</TERMINATE_REASON>
<TERMINATE_TIMESTAMP>12</TERMINATE_TIMESTAMP>
</HEADER>
<APPROVING_OFFICERS>
<APPROVING_OFFICER>
<USER_CODE>JUSJ</USER_CODE>
<NAME>JUSTIFICATION GRANTED</NAME>
<ORGANISATION_CODE>RP</ORGANISATION_CODE>
<ORGANISATION_NAME>RP</ORGANISATION_NAME>
</APPROVING_OFFICER>
</APPROVING_OFFICERS>
<ITEMS>
<ITEM>
<LINE_NUMBER>1</LINE_NUMBER>
<PURCHASE_REQUEST>
<CODE>GOODS</CODE>
<LINE_NUMBER>1</LINE_NUMBER>
</PURCHASE_REQUEST>
<DESCRIPTION>HANDLING CHARGES </DESCRIPTION>
<UNIT_OF_MEASURE>M</UNIT_OF_MEASURE>
<QUANTITY>12</QUANTITY>
<LINE_TYPE>SERVICES</LINE_TYPE>
<UNIT_PRICE>12</UNIT_PRICE>
<PRICE_UNIT>12</PRICE_UNIT>
<TOTAL_AMOUNT>12</TOTAL_AMOUNT>
<STATUS>STATUS</STATUS>
<PART_NUMBER_INFORMATION>
<NATO_STOCK_NUMBER>123</NATO_STOCK_NUMBER>
<MANUFACTURER_PART_NUMBER>12</MANUFACTURER_PART_NUMBER>
<PART_NUMBER>123</PART_NUMBER>
<CAGE_CODE>CG</CAGE_CODE>
<CAGE_NAME>CARRIAGE GOODS</CAGE_NAME>
<ITEM_CONDITION>NORMAL</ITEM_CONDITION>
<MINIMUM_SHELF_LIFE>1 </MINIMUM_SHELF_LIFE>
<SHELF_LIFE_REMAINING>1</SHELF_LIFE_REMAINING>
<CERTIFICATE_OF_CONFORMANCE> </CERTIFICATE_OF_CONFORMANCE>
<EXPORT_LICENSE> </EXPORT_LICENSE>
</PART_NUMBER_INFORMATION>
<MATERIAL_MASTER_CODE>IT112</MATERIAL_MASTER_CODE>
<MATERIAL_GROUP_CODE>STF</MATERIAL_GROUP_CODE>
<PLANT_CODE> RP</PLANT_CODE>
<ITEM_CATEGORY_CODE>RPCODE</ITEM_CATEGORY_CODE>
<ADMIN_FEE_SGD_AMOUNT>100</ADMIN_FEE_SGD_AMOUNT>
<INSTRUCTION_TO_SUPPLIER>DESCRIPTION</INSTRUCTION_TO_SUPPLIER>
<PERIOD_CONTRACT_LINE_NUMBER>1</PERIOD_CONTRACT_LINE_NUMBER>
<LOCATIONS>
<LOCATION>
<LINE_NUMBER>1</LINE_NUMBER>
<QUANTITY>12</QUANTITY>
<DELIVERY_DESTINATION>HARBOUR FRONT</DELIVERY_DESTINATION>
<DELIVERY_DATE>25-FEB-2011</DELIVERY_DATE>
<DELIVERY_TERMS>15 DAYS</DELIVERY_TERMS>
<PORT_OF_ORIGIN>SINGAPORE</PORT_OF_ORIGIN>
<STATUS>DELIVERED</STATUS>
<RECIPIENT>
<USER_CODE>DSC</USER_CODE>
<NAME>DELIVERED</NAME>
<PHONE>121323</PHONE>
<EMAIL>alkgl@gmail.com</EMAIL>
</RECIPIENT>
<DISTRIBUTIONS>
<DISTRIBUTION>
<LINE_NUMBER>1</LINE_NUMBER>
<QUANTITY>12</QUANTITY>
<CHART_OF_ACCOUNT>ooo.aaa.ccc</CHART_OF_ACCOUNT>
<PROJECT_DISTRIBUTION>distributions</PROJECT_DISTRIBUTION>
<PROJECT_CODE> RPC</PROJECT_CODE>
<STATUS>CONFIRMED</STATUS>
</DISTRIBUTION>
</DISTRIBUTIONS>
</LOCATION>
</LOCATIONS>
</ITEM>
<ITEM>
<LINE_NUMBER>2</LINE_NUMBER>
<PURCHASE_REQUEST>
<CODE>GOODS</CODE>
<LINE_NUMBER>2</LINE_NUMBER>
</PURCHASE_REQUEST>
<DESCRIPTION>HANDLING CHARGES </DESCRIPTION>
<UNIT_OF_MEASURE>M</UNIT_OF_MEASURE>
<QUANTITY>23</QUANTITY>
<LINE_TYPE>SERVICES</LINE_TYPE>
<UNIT_PRICE>112</UNIT_PRICE>
<PRICE_UNIT>112</PRICE_UNIT>
<TOTAL_AMOUNT>12000</TOTAL_AMOUNT>
<STATUS>STATUS</STATUS>
<PART_NUMBER_INFORMATION>
<NATO_STOCK_NUMBER>123</NATO_STOCK_NUMBER>
<MANUFACTURER_PART_NUMBER>12</MANUFACTURER_PART_NUMBER>
<PART_NUMBER>123</PART_NUMBER>
<CAGE_CODE>CG</CAGE_CODE>
<CAGE_NAME>CARRIAGE GOODS</CAGE_NAME>
<ITEM_CONDITION>NORMAL</ITEM_CONDITION>
<MINIMUM_SHELF_LIFE>1 </MINIMUM_SHELF_LIFE>
<SHELF_LIFE_REMAINING>1</SHELF_LIFE_REMAINING>
<CERTIFICATE_OF_CONFORMANCE> </CERTIFICATE_OF_CONFORMANCE>
<EXPORT_LICENSE> </EXPORT_LICENSE>
</PART_NUMBER_INFORMATION>
<MATERIAL_MASTER_CODE>IT112</MATERIAL_MASTER_CODE>
<MATERIAL_GROUP_CODE>STF</MATERIAL_GROUP_CODE>
<PLANT_CODE> RP</PLANT_CODE>
<ITEM_CATEGORY_CODE>RPCODE</ITEM_CATEGORY_CODE>
<ADMIN_FEE_SGD_AMOUNT>100</ADMIN_FEE_SGD_AMOUNT>
<INSTRUCTION_TO_SUPPLIER>DESCRIPTION</INSTRUCTION_TO_SUPPLIER>
<PERIOD_CONTRACT_LINE_NUMBER>1</PERIOD_CONTRACT_LINE_NUMBER>
<LOCATIONS>
<LOCATION>
<LINE_NUMBER>1</LINE_NUMBER>
<QUANTITY>12</QUANTITY>
<DELIVERY_DESTINATION>HARBOUR FRONT</DELIVERY_DESTINATION>
<DELIVERY_DATE>15-FEB-2011</DELIVERY_DATE>
<DELIVERY_TERMS>2 DAYS</DELIVERY_TERMS>
<PORT_OF_ORIGIN>SINGAPORE</PORT_OF_ORIGIN>
<STATUS>DELIVERED</STATUS>
<RECIPIENT>
<USER_CODE>DSC</USER_CODE>
<NAME>PROCESS</NAME>
<PHONE>121323</PHONE>
<EMAIL>alkgl@gmail.com</EMAIL>
</RECIPIENT>
<DISTRIBUTIONS>
<DISTRIBUTION>
<LINE_NUMBER>1</LINE_NUMBER>
<QUANTITY>12</QUANTITY>
<CHART_OF_ACCOUNT>ooo.aaa.ccc</CHART_OF_ACCOUNT>
<PROJECT_DISTRIBUTION>distributions</PROJECT_DISTRIBUTION>
<PROJECT_CODE> RPC</PROJECT_CODE>
<STATUS>CONFIRMED</STATUS>
</DISTRIBUTION>
</DISTRIBUTIONS>
</LOCATION>
</LOCATIONS>
</ITEM>
</ITEMS>
</GEBIZ_PURCHASE_ORDER>
------------------------------------------------------------ upto here xml file
For this I've created one sql table like this and I want to insert the above into two rows in this table.
create table xx_po_estimate_details
(
ORDER_CODE VARCHAR2(40),
EXTERNAL_SYSTEM_CODE VARCHAR2(40),
AMENDMENT_NUMBER VARCHAR2(40),
VARIATION_NUMBER VARCHAR2(40),
DESCRIPTION VARCHAR2(40),
SUB_BUSINESS_UNIT VARCHAR2(40),
COST_CENTER_GROUP VARCHAR2(40),
BUYER_CODE VARCHAR2(40),
FINANCIAL_SYSTEM_ORDER_CODE VARCHAR2(40),
USER_NRIC VARCHAR2(40),
PURCHASING_GROUP VARCHAR2(40),
STATUS VARCHAR2(40),
CODE VARCHAR2(40),
NAME VARCHAR2(40),
GST_NUMBER VARCHAR2(40),
ID VARCHAR2(40),
NAME VARCHAR2(40),
PHONE VARCHAR2(40),
PHONE_EXTENSION VARCHAR2(40),
FAX VARCHAR2(40),
TELEX VARCHAR2(40),
EMAIL VARCHAR2(40),
ADDRESS_LINE1 VARCHAR2(40),
ADDRESS_LINE2 VARCHAR2(40),
ADDRESS_LINE3 VARCHAR2(40),
COUNTRY_CODE VARCHAR2(40),
PROVINCE VARCHAR2(40),
STATE VARCHAR2(40),
CITY VARCHAR2(40),
AREA_CODE VARCHAR2(40),
ZIP VARCHAR2(40),
REGION_CODE VARCHAR2(40),
CONTACT_NAME VARCHAR2(40),
CONTACT_PHONE VARCHAR2(40),
CONTACT_EMAIL VARCHAR2(40),
CONTACT_FAX VARCHAR2(40),
USER_CODE VARCHAR2(40),
ORGANISATION_NAME VARCHAR2(40),
CURRENCY_CODE VARCHAR2(40),
CURRENCY_RATE VARCHAR2(40),
CURRENCY_RATE_DATE VARCHAR2(40),
CURRENCY_RATE_TYPE VARCHAR2(40),
CURRENCY_AMOUNT VARCHAR2(40),
CODE VARCHAR2(40),
AGENCY_CODE VARCHAR2(40),
ADMIN_FEE_SGD_AMOUNT VARCHAR2(40),
PAYMENT_TERMS VARCHAR2(40),
BILL_TO VARCHAR2(40),
JUSTIFICATION VARCHAR2(40),
ORGANISATION_CODE VARCHAR2(40),
ORGANISATION_NAME VARCHAR2(40),
NAME VARCHAR2(40),
PHONE VARCHAR2(40),
FAX VARCHAR2(40),
EMAIL VARCHAR2(40),
MINISTRY_CODE VARCHAR2(40),
DEPARTMENT_CODE VARCHAR2(40),
CREATE_TIMESTAMP VARCHAR2(40),
TERMINATE_REASON VARCHAR2(40),
TERMINATE_TIMESTAMP VARCHAR2(40),
USER_CODE VARCHAR2(40),
NAME VARCHAR2(40),
ORGANISATION_CODE VARCHAR2(40),
ORGANISATION_NAME VARCHAR2(40),
LINE_NUMBER VARCHAR2(40),
CODE VARCHAR2(40),
LINE_NUMBER VARCHAR2(40),
DESCRIPTION VARCHAR2(40),
UNIT_OF_MEASURE VARCHAR2(40),
QUANTITY VARCHAR2(40),
LINE_TYPE VARCHAR2(40),
UNIT_PRICE VARCHAR2(40),
PRICE_UNIT VARCHAR2(40),
TOTAL_AMOUNT VARCHAR2(40),
STATUS VARCHAR2(40),
NATO_STOCK_NUMBER VARCHAR2(40),
MANUFACTURER_PART_NUMBER VARCHAR2(40),
PART_NUMBER VARCHAR2(40),
CAGE_CODE VARCHAR2(40),
CAGE_NAME VARCHAR2(40),
ITEM_CONDITION VARCHAR2(40),
MINIMUM_SHELF_LIFE VARCHAR2(40),
SHELF_LIFE_REMAINING VARCHAR2(40),
CERTIFICATE_OF_CONFORMANCE VARCHAR2(40),
EXPORT_LICENSE VARCHAR2(40),
MATERIAL_MASTER_CODE VARCHAR2(40),
MATERIAL_GROUP_CODE VARCHAR2(40),
PLANT_CODE VARCHAR2(40),
ITEM_CATEGORY_CODE VARCHAR2(40),
ADMIN_FEE_SGD_AMOUNT VARCHAR2(40),
INSTRUCTION_TO_SUPPLIER VARCHAR2(40),
PERIOD_CONTRACT_LINE_NUMBER VARCHAR2(40),
LINE_NUMBER VARCHAR2(40),
QUANTITY VARCHAR2(40),
DELIVERY_DESTINATION VARCHAR2(40),
DELIVERY_DATE VARCHAR2(40),
DELIVERY_TERMS VARCHAR2(40),
PORT_OF_ORIGIN VARCHAR2(40),
STATUS VARCHAR2(40),
USER_CODE VARCHAR2(40),
NAME VARCHAR2(40),
PHONE VARCHAR2(40),
EMAIL VARCHAR2(40),
LINE_NUMBER VARCHAR2(40),
QUANTITY VARCHAR2(40),
CHART_OF_ACCOUNT VARCHAR2(40),
PROJECT_DISTRIBUTION VARCHAR2(40),
PROJECT_CODE VARCHAR2(40),
STATUS VARCHAR2(40));
the data should finally look like this;
<header> <line information>
Purchase order infor line Item 1
Purchase order info(same as above) line Item 2
Gurus Please help me in writing one pl/sql script for this, I am a newbie to this one. Please help me this time.
Thanks in advance.
Regards
Prasad