12 Replies Latest reply: May 9, 2012 2:48 PM by odie_63 RSS

    XML File into Oracle table

    N@*841964*
      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
        • 1. Re: XML File into Oracle table
          odie_63
          Hi,

          Are you a colleague/friend of Nagendra who posted here earlier with the same account?

          Take a look at the solution I proposed there, it should give you ideas for your current requirement.

          Load xml data in Oracle table
          • 2. Re: XML File into Oracle table
            N@*841964*
            Hi Odie,

            Yeah he is my colleague and we're new team, using this group newly so not known of the earlier forum. He has given me your reference and inputs. Instead of going to the huge file I broke few tags initially I created like this. I am posting the steps what ever I have performed.

            create or replace directory xmldir as '/u01/oratest/xml_load';

            create table xxpo_estimate_details
            (ORDER_CODE varchar2(100) ,
            EXTERNAL_SYSTEM_CODE varchar2(100),
            AMENDMENT_NUMBER varchar2(100) ,
            VARIATION_NUMBER varchar2(100) ,
            DESCRIPTION varchar2(100) ,
            SUB_BUSINESS_UNIT varchar2(100) ,
            COST_CENTER_GROUP varchar2(100) ,
            BUYER_CODE varchar2(100) ,
            FINANCIAL_SYSTEM_ORDER_CODE varchar2(100),
            USER_NRIC varchar2(100) ,
            PURCHASING_GROUP varchar2(100) ,
            FS_STATUS varchar2(100) ,
            PR_LINE_NUMBER varchar2(100) ,
            PR_DESCRIPTION varchar2(100) ,
            UNIT_OF_MEASURE varchar2(100) ,
            PR_QUANTITY varchar2(100) ,
            LINE_TYPE varchar2(100),
            UNIT_PRICE varchar2(100) ,
            PRICE_UNIT varchar2(100),
            TOTAL_AMOUNT varchar2(100) ,
            PR_STATUS varchar2(100));

            this xml file which I made it short for initial testing

            <----PO_DETAILS_COPY.xml ------>

            <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>
            </HEADER>
            <ITEMS>
            <ITEM>
            <LINE_NUMBER>1</LINE_NUMBER>
            <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>
            </ITEM>
            <ITEM>
            <LINE_NUMBER>2</LINE_NUMBER>
            <DESCRIPTION> FREIGHT HANDLING CHARGES </DESCRIPTION>
            <UNIT_OF_MEASURE>M</UNIT_OF_MEASURE>
            <QUANTITY>13</QUANTITY>
            <LINE_TYPE>SERVICES</LINE_TYPE>
            <UNIT_PRICE>14</UNIT_PRICE>
            <PRICE_UNIT>14</PRICE_UNIT>
            <TOTAL_AMOUNT>1200</TOTAL_AMOUNT>
            <STATUS>STATUSDELIVERED</STATUS>
            </ITEM>
            </ITEMS>
            </GEBIZ_PURCHASE_ORDER>

            and this is the script I have written for uploading the data into table.

            declare
            acct_doc xmltype := xmltype( bfilename('XMLDIR','PO_DETAILS_COPY.xml'), nls_charset_id('AL32UTF8') );
            BEGIN
            insert into xxpo_estimate_details
            (ORDER_CODE ,
            EXTERNAL_SYSTEM_CODE ,
            AMENDMENT_NUMBER ,
            VARIATION_NUMBER ,
            DESCRIPTION ,
            SUB_BUSINESS_UNIT ,
            COST_CENTER_GROUP ,
            BUYER_CODE ,
            FINANCIAL_SYSTEM_ORDER_CODE ,
            USER_NRIC ,
            PURCHASING_GROUP ,
            FS_STATUS ,
            PR_LINE_NUMBER ,
            PR_DESCRIPTION ,
            UNIT_OF_MEASURE ,
            PR_QUANTITY ,
            LINE_TYPE ,
            UNIT_PRICE ,
            PRICE_UNIT ,
            TOTAL_AMOUNT ,
            PR_STATUS
            )
            SELECT X1.ORDER_CODE,
            X1.EXTERNAL_SYSTEM_CODE,
            X1.AMENDMENT_NUMBER,
            X1.VARIATION_NUMBER,
            X1.DESCRIPTION ,
            X1.SUB_BUSINESS_UNIT,
            X1.COST_CENTER_GROUP,
            X1.BUYER_CODE ,
            X1.FINANCIAL_SYSTEM_ORDER_CODE,
            X1.USER_NRIC ,
            X1.PURCHASING_GROUP ,
            X1.STATUS,
            X2.LINE_NUMBER ,
            X2.DESCRIPTION ,
            X2.UNIT_OF_MEASURE ,
            X2.QUANTITY ,
            X2.LINE_TYPE ,
            X2.UNIT_PRICE ,
            X2.PRICE_UNIT ,
            X2.TOTAL_AMOUNT ,
            X2.STATUS
            FROM XMLTABLE(
            '/GEBIZ_PURCHASE_ORDER/HEADER'
            passing acct_doc
            columns header_no for ordinality,
            ORDER_CODE varchar2(100) path 'ORDER_CODE',
            EXTERNAL_SYSTEM_CODE varchar2(100) path 'EXTERNAL_SYSTEM_CODE',
            AMENDMENT_NUMBER VARCHAR2(100) path 'AMENDMENT_NUMBER',
            VARIATION_NUMBER VARCHAR2(10) PATH 'VARIATION_NUMBER',
            DESCRIPTION VARCHAR2(100) PATH 'DESCRIPTION',
            SUB_BUSINESS_UNIT VARCHAR2(100) PATH 'FINANCIAL_SYSTEM/SUB_BUSINESS_UNIT',
            COST_CENTER_GROUP VARCHAR2(10) PATH 'FINANCIAL_SYSTEM/NFS/COST_CENTER_GROUP',
            BUYER_CODE VARCHAR2(50) PATH 'FINANCIAL_SYSTEM/NFS/BUYER_CODE',
            FINANCIAL_SYSTEM_ORDER_CODE VARCHAR2(50) PATH 'FINANCIAL_SYSTEM/NFS/FINANCIAL_SYSTEM_ORDER_CODE',
            USER_NRIC VARCHAR2(50) PATH 'FINANCIAL_SYSTEM/NFS/USER_NRIC',
            PURCHASING_GROUP VARCHAR2(50) PATH 'FINANCIAL_SYSTEM/SAP/PURCHASING_GROUP',
            STATUS VARCHAR2(50) PATH 'STATUS'
            ) x1,
            xmltable(
            '$d/GEBIZ_PURCHASE_ORDER/ITEMS[$hn]/ITEM'
            passing acct_doc as "d",
            x1.header_no as "hn"
            columns LINE_NUMBER varchar2(10) path 'LINE_NUMBER',
            DESCRIPTION varchar2(100) path 'DESCRIPTION',
            UNIT_OF_MEASURE VARCHAR2(100) PATH 'UNIT_OF_MEASURE',
            QUANTITY VARCHAR2(10) PATH 'QUANTITY',
            LINE_TYPE VARCHAR2(100) PATH 'LINE_TYPE',
            UNIT_PRICE VARCHAR2(10) PATH 'UNIT_PRICE',
            PRICE_UNIT VARCHAR2(10) PATH 'PRICE_UNIT',
            TOTAL_AMOUNT VARCHAR2(10) PATH 'TOTAL_AMOUNT',
            STATUS VARCHAR2(120) PATH 'STATUS'
            ) x2 ;
            EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('ERROR ENCOUNTERED '||SQLERRM);
            END;

            Now that I am able to enter the data successfully, the thing is whenever if I am referencing the xml column as 'Number' as the table column is 'Varchar2' that time its not inserting any data. Please let me know this is upto two levels I did. Further I've one level for the 'Lines' where for each line I've multiple distributions.
            Then can I put one more xml table as x3, then in that case how can I refer both Po Header and PO Line in the x3 table.

            Please give me in your inputs. Appreciate you help.

            Thanks in advance.

            Regards
            Prasad V
            • 3. Re: XML File into Oracle table
              odie_63
              the thing is whenever if I am referencing the xml column as 'Number' as the table column is 'Varchar2' that time its not inserting any data.
              Any error observed?
              Remove the exception handler to see it explicitely.

              Which column are you declaring as NUMBER, and what value is there in the corresponding element in the file?
              It might be a conversion issue due to database/session NLS settings.
              Further I've one level for the 'Lines' where for each line I've multiple distributions.
              Then can I put one more xml table as x3, then in that case how can I refer both Po Header and PO Line in the x3 table.
              You have to join another XMLtable for each set of repeating elements you need to unnest.

              See how you can pass a node set from one XMLTable to the next :
              SELECT -- Header data :
                     x1.order_code,
                     x1.external_system_code,
                     x1.amendment_number,
                     x1.variation_number,
                     x1.description ,
                     x1.sub_business_unit,
                     x1.cost_center_group,
                     x1.buyer_code ,
                     x1.financial_system_order_code,
                     x1.user_nric ,
                     x1.purchasing_group ,
                     x1.status,
                     -- Item data :
                     x2.line_number ,
                     x2.description ,
                     x2.unit_of_measure ,
                     x2.quantity ,
                     x2.line_type ,
                     x2.unit_price ,
                     x2.price_unit ,
                     x2.total_amount ,
                     x2.status,
                     -- Location data :
                     x3.line_number,
                     x3.quantity,
                     x3.delivery_destination,
                     -- Distribution data : 
                     x4.line_number,
                     x4.quantity,
                     x4.status
              FROM XMLTable('/GEBIZ_PURCHASE_ORDER'
                     passing xmltype( bfilename('XMLDIR', 'PO_DETAILS_COPY.xml'), nls_charset_id('AL32UTF8') )
                     columns 
                       order_code                  varchar2(100) path 'HEADER/ORDER_CODE',
                       external_system_code        varchar2(100) path 'HEADER/EXTERNAL_SYSTEM_CODE',
                       amendment_number            varchar2(100) path 'HEADER/AMENDMENT_NUMBER',
                       variation_number            varchar2(10)  path 'HEADER/VARIATION_NUMBER',
                       description                 varchar2(100) path 'HEADER/DESCRIPTION',
                       sub_business_unit           varchar2(100) path 'HEADER/FINANCIAL_SYSTEM/SUB_BUSINESS_UNIT',
                       cost_center_group           varchar2(10)  path 'HEADER/FINANCIAL_SYSTEM/NFS/COST_CENTER_GROUP',
                       buyer_code                  varchar2(50)  path 'HEADER/FINANCIAL_SYSTEM/NFS/BUYER_CODE',
                       financial_system_order_code varchar2(50)  path 'HEADER/FINANCIAL_SYSTEM/NFS/FINANCIAL_SYSTEM_ORDER_CODE',
                       user_nric                   varchar2(50)  path 'HEADER/FINANCIAL_SYSTEM/NFS/USER_NRIC',
                       purchasing_group            varchar2(50)  path 'HEADER/FINANCIAL_SYSTEM/SAP/PURCHASING_GROUP',
                       status                      varchar2(50)  path 'HEADER/STATUS',
                       items                       xmltype       path 'ITEMS'
                   ) x1,
                   XMLTable('/ITEMS/ITEM'
                     passing x1.items
                     columns 
                       line_number     varchar2(10)  path 'LINE_NUMBER',
                       description     varchar2(100) path 'DESCRIPTION',
                       unit_of_measure varchar2(100) path 'UNIT_OF_MEASURE',
                       quantity        varchar2(10)  path 'QUANTITY',
                       line_type       varchar2(100) path 'LINE_TYPE',
                       unit_price      varchar2(10)  path 'UNIT_PRICE',
                       price_unit      varchar2(10)  path 'PRICE_UNIT',
                       total_amount    varchar2(10)  path 'TOTAL_AMOUNT',
                       status          varchar2(120) path 'STATUS',
                       locations       xmltype       path 'LOCATIONS'
                   ) x2,
                   XMLTable('/LOCATIONS/LOCATION'
                     passing x2.locations
                     columns 
                       line_number          number        path 'LINE_NUMBER',
                       quantity             number        path 'QUANTITY',
                       delivery_destination varchar2(100) path 'DELIVERY_DESTINATION',
                       distributions        xmltype       path 'DISTRIBUTIONS'
                   ) x3,
                   XMLTable('/DISTRIBUTIONS/DISTRIBUTION'
                     passing x3.distributions
                     columns 
                       line_number number        path 'LINE_NUMBER',
                       quantity    number        path 'QUANTITY',
                       status      varchar2(100) path 'STATUS'
                   ) x4
              ;
              • 4. Re: XML File into Oracle table
                N@*841964*
                Hi odie,

                Please help me in resolving my issue.

                i have two tables like this now.

                create table xxrp_account_header
                (
                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_CODE VARCHAR2(40),
                USER_NRIC VARCHAR2(40),
                STATUS VARCHAR2(40)
                );


                create table xxrp_account_details
                (
                ORDER_CODE varchar2(40),
                line_number varchar2(10),
                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)
                );

                The same xml file I've used for loading data into two tables. Now that child table is having one link between xxrp_account_header
                and xxrp_account_details. Order_code is column which is linking column between two.

                I am pasting the my xml file one more time
                ----------------------------------------------------------------------------------------------
                <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>
                </HEADER>
                <ITEMS>
                <ITEM>
                <LINE_NUMBER>1</LINE_NUMBER>
                <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>
                </ITEM>
                <ITEM>
                <LINE_NUMBER>2</LINE_NUMBER>
                <DESCRIPTION> FREIGHT HANDLING CHARGES </DESCRIPTION>
                <UNIT_OF_MEASURE>M</UNIT_OF_MEASURE>
                <QUANTITY>13</QUANTITY>
                <LINE_TYPE>SERVICES</LINE_TYPE>
                <UNIT_PRICE>14</UNIT_PRICE>
                <PRICE_UNIT>14</PRICE_UNIT>
                <TOTAL_AMOUNT>1200</TOTAL_AMOUNT>
                <STATUS>STATUSDELIVERED</STATUS>
                </ITEM>
                </ITEMS>
                </GEBIZ_PURCHASE_ORDER>
                -----------------------------------------

                I've created one script like this

                declare
                acct_doc xmltype := xmltype( bfilename('XMLDIR','PO_DETAILS_COPY.xml'), nls_charset_id('AL32UTF8') );
                BEGIN
                insert into xxrp_account_header
                (ORDER_CODE ,
                EXTERNAL_SYSTEM_CODE ,
                AMENDMENT_NUMBER ,
                VARIATION_NUMBER ,
                DESCRIPTION ,
                SUB_BUSINESS_UNIT ,
                COST_CENTER_GROUP ,
                BUYER_CODE ,
                FINANCIAL_SYSTEM_CODE ,
                USER_NRIC ,
                STATUS
                )
                SELECT
                order_code,
                external_system_code,
                amendment_number,
                variation_number,
                description ,
                sub_business_unit,
                cost_center_group,
                buyer_code ,
                financial_system_order_code,
                user_nric ,
                status
                FROM XMLTable('/GEBIZ_PURCHASE_ORDER'
                passing acct_doc
                columns header_no for ordinality,
                order_code varchar2(100) path 'HEADER/ORDER_CODE',
                external_system_code varchar2(100) path 'HEADER/EXTERNAL_SYSTEM_CODE',
                amendment_number varchar2(100) path 'HEADER/AMENDMENT_NUMBER',
                variation_number varchar2(10) path 'HEADER/VARIATION_NUMBER',
                description varchar2(100) path 'HEADER/DESCRIPTION',
                sub_business_unit varchar2(100) path 'HEADER/FINANCIAL_SYSTEM/SUB_BUSINESS_UNIT',
                cost_center_group varchar2(10) path 'HEADER/FINANCIAL_SYSTEM/NFS/COST_CENTER_GROUP',
                buyer_code varchar2(50) path 'HEADER/FINANCIAL_SYSTEM/NFS/BUYER_CODE',
                financial_system_order_code varchar2(50) path 'HEADER/FINANCIAL_SYSTEM/NFS/FINANCIAL_SYSTEM_ORDER_CODE',
                user_nric varchar2(50) path 'HEADER/FINANCIAL_SYSTEM/NFS/USER_NRIC',
                status varchar2(50) path 'HEADER/STATUS'
                ) ;


                insert into xxrp_account_details
                ( order_code,
                line_number,
                description,
                unit_of_measure,
                quantity,
                line_type,
                unit_price,
                price_unit,
                total_amount,
                status
                )
                select
                order_code,
                line_number,
                description,
                unit_of_measure,
                quantity,
                line_type,
                unit_price,
                price_unit,
                total_amount,
                status
                FROM XMLTABLE(
                'for $i in /GEBIZ_PURCHASE_ORDER
                return
                for $j in $i/following-sibling::ITEMS[1]/ITEM
                return element r {$i, $j}'
                passing acct_doc
                columns
                order_code varchar2(100) path 'HEADER/ORDER_CODE',
                line_number varchar2(100) path 'ITEMS/ITEM/LINE_NUMBER',
                description varchar2(100) path 'ITEMS/ITEM/DESCRIPTION',
                unit_of_measure varchar2(100) path 'ITEMS/ITEM/UNIT_OF_MEASURE',
                quantity varchar2(100) path 'ITEMS/ITEM/QUANTITY',
                line_type varchar2(100) path 'ITEMS/ITEM/LINE_TYPE',
                UNIT_PRICE varchar2(100) path 'ITEMS/ITEM/UNIT_PRICE',
                PRICE_UNIT varchar2(100) path 'ITEMS/ITEM/PRICE_UNIT',
                TOTAL_AMOUNT varchar2(100) path 'ITEMS/ITEM/TOTAL_AMOUNT',
                STATUS varchar2(100) path 'ITEMS/ITEM/STATUS'
                );
                end;
                /

                First insert into the table xxrp_account_header is happening perfectly , no issue with that.

                Where as coming to the xxrp_account_details, by using xquery in the second statement, I was able to insert only header table twice, which I was expecting,
                but no line data is reflecting in child table against child records.

                It is simply showing the two header records repeatedly no more child data appearing, can you please help me in finding what is error in this pl/sql script. I am in dier need of this requirement.


                Thanks for in advance for your help.


                Regards
                Nagendra
                • 5. Re: XML File into Oracle table
                  odie_63
                  Hi,

                  Try these two :

                  -- Header
                  SELECT order_code,
                         external_system_code,
                         amendment_number,
                         variation_number,
                         description,
                         sub_business_unit,
                         cost_center_group,
                         buyer_code,
                         financial_system_order_code,
                         user_nric,
                         status
                  FROM XMLTable(
                   '/GEBIZ_PURCHASE_ORDER'
                   passing acct_doc
                   columns
                     order_code                  varchar2(100) path 'HEADER/ORDER_CODE',
                     external_system_code        varchar2(100) path 'HEADER/EXTERNAL_SYSTEM_CODE',
                     amendment_number            varchar2(100) path 'HEADER/AMENDMENT_NUMBER',
                     variation_number            varchar2(10)  path 'HEADER/VARIATION_NUMBER',
                     description                 varchar2(100) path 'HEADER/DESCRIPTION',
                     sub_business_unit           varchar2(100) path 'HEADER/FINANCIAL_SYSTEM/SUB_BUSINESS_UNIT',
                     cost_center_group           varchar2(10)  path 'HEADER/FINANCIAL_SYSTEM/NFS/COST_CENTER_GROUP',
                     buyer_code                  varchar2(50)  path 'HEADER/FINANCIAL_SYSTEM/NFS/BUYER_CODE',
                     financial_system_order_code varchar2(50)  path 'HEADER/FINANCIAL_SYSTEM/NFS/FINANCIAL_SYSTEM_ORDER_CODE',
                     user_nric                   varchar2(50)  path 'HEADER/FINANCIAL_SYSTEM/NFS/USER_NRIC',
                     status                      varchar2(50)  path 'HEADER/STATUS'
                  );
                  -- Details
                  SELECT order_code,
                         line_number,
                         description,
                         unit_of_measure,
                         quantity,
                         line_type,
                         unit_price,
                         price_unit,
                         total_amount,
                         status
                  FROM XMLTable(
                   'for $i in /GEBIZ_PURCHASE_ORDER
                    return
                     for $j in $i/ITEMS/ITEM
                     return element r {$i/HEADER/ORDER_CODE, $j}'
                   passing acct_doc
                   columns
                     order_code      varchar2(100) path 'ORDER_CODE',
                     line_number     varchar2(100) path 'ITEM/LINE_NUMBER',
                     description     varchar2(100) path 'ITEM/DESCRIPTION',
                     unit_of_measure varchar2(100) path 'ITEM/UNIT_OF_MEASURE',
                     quantity        varchar2(100) path 'ITEM/QUANTITY',
                     line_type       varchar2(100) path 'ITEM/LINE_TYPE',
                     unit_price      varchar2(100) path 'ITEM/UNIT_PRICE',
                     price_unit      varchar2(100) path 'ITEM/PRICE_UNIT',
                     total_amount    varchar2(100) path 'ITEM/TOTAL_AMOUNT',
                     status          varchar2(100) path 'ITEM/STATUS'
                  );
                  • 6. Re: XML File into Oracle table
                    N@*841964*
                    Hi Odie,

                    Thanks for your help. It really worked.


                    Regards
                    Nagendra
                    • 7. Re: XML File into Oracle table
                      936142
                      Hi Odie,

                      I am trying to load an xml file with a similar structure as described in this thread. Your suggestion works and brings exactly the results I am seeking. But if I try the same on a large xml file(~50Mb) the process takes forever to complete the insertion. I am extremely new into the technology so I could definitely be not knowing the obvious. Can you please guide me what I should do to be able to load data(considering the given xsd,xml in this thread) in the fastest manner possible.I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                      Thanks a lot
                      • 8. Re: XML File into Oracle table
                        odie_63
                        Where does the XML reside when you do the insert? Variable, file, column (which datatype)?

                        When dealing with large documents, the key is to load the file in an XMLType table/column first and do all subsequent queries from there.
                        It allows Oracle to perform internal optimizations and leverage the power of SQL as if it were relational data.

                        Given your version, I suggest you first try with a binary XMLType table :
                        CREATE TABLE tmp_xml OF XMLType
                        XMLType STORE AS SECUREFILE BINARY XML;
                        INSERT INTO tmp_xml VALUES (<insert the source here>);
                        SELECT *
                        FROM tmp_xml t
                            , XMLTable('/some_path' passing t.object_value
                        ...
                        • 9. Re: XML File into Oracle table
                          936142
                          Hi Odie,

                          Thank you so much! Your suggestion worked wonders.

                          My XML resides in a file on the os file system linked to oracle by a directory.
                          I was trying to insert it into an object relational xmltype table( to be able to automatically get object-relational rows and columns). This was taking like 50 mins for a 30mb file.
                          Selecting directly from xml doc file took forever
                          Changing the table type to binary xmltype and inserting shorted the time to 4 mins.

                          I already told you about my naivity on the subject, so pardon me if I am asking too much - do we have something like object-relational+binary option? Thanks again for your help!
                          • 10. Re: XML File into Oracle table
                            odie_63
                            I was trying to insert it into an object relational xmltype table( to be able to automatically get object-relational rows and columns). This was taking like 50 mins for a 30mb file.
                            Do you mean a schema-based XMLType table, created after registering the corresponding XML schema(s)?

                            If so, it shouldn't take that long.
                            do we have something like object-relational+binary option?
                            Nope.
                            However, there's schema-based binary XML too.
                            • 11. Re: XML File into Oracle table
                              936142
                              This is what I did -

                              Register

                              begin
                              DBMS_XMLSCHEMA.registerSchema(
                              SCHEMAURL => 'http://www.w3.org/2001/XMLSchema/xml_test.xsd',
                              SCHEMADOC => bfilename('XMLTEST','sample.xsd'),
                              LOCAL => TRUE,
                              GENTYPES => TRUE,
                              genBean => FALSE,
                              GENTABLES => FALSE,
                              CSID => nls_charset_id('AL32UTF8'),
                              OPTIONS => DBMS_XMLSCHEMA.REGISTER_BINARYXML,(tried with and without this option- does this mean schema based binary XML?)
                              ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE);
                              END;
                              /

                              Create table-
                              CREATE TABLE tmp_xml (
                              filename varchar2(100),
                              xml_data XMLTYPE)
                              XMLTYPE xml_data STORE AS OBJECT RELATIONAL
                              XMLSCHEMA "http://www.w3.org/2001/XMLSchema/xml_test.xsd"
                              ELEMENT "Item";

                              Insert -
                              INSERT INTO tmp_xml values ('data.xml',XMLType(bfilename('XMLTEST', 'data,xml'),nls_charset_id('AL32UTF8')));

                              Then as you suggested I gave STORE AS SECUREFILE BINARY XML, insertion time reduced, selecting a few nodes (10) was very quick , then I wrote you the note and ran a bigger query with 200 columns and 3000 rows which took 25 mins(not there but better)

                              What do you see wrong here?
                              • 12. Re: XML File into Oracle table
                                odie_63
                                What do you see wrong here?
                                Nothing wrong, you did the right things regarding registering with DBMS_XMLSCHEMA.REGISTER_BINARYXML (it's mandatory to use a schema-based binary storage later).

                                I was just concerned about the insert time you've reported with OR storage. 50 mins seems an enormous lot of time for just a 30MB file.
                                Using OR storage, I've already loaded 100MB files in less than 10 seconds (via FTP and the XDB repository).

                                The XML schema might need some annotations to control the underlying relational structure that is created during registration, but I would need to see the schema and a sample XML document to be sure.

                                If you're interested in improving OR storage performance or queries over the binary XML column (using XML indexes for example), you may create your own thread in the {forum:id=34} forum and provide a test case.