Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Insert XML data into Table from XML File

3398102Apr 13 2018 — edited Apr 19 2018

Hi,

Could you let me know how to insert data from below XML file into the table.

<?xml version="1.0" encoding="UTF-8"?>

<InvoiceList>

<Invoice>

  <InvoiceHeader>

    <InvoiceNum>INV9870</InvoiceNum>

    <VendorID>VE5423</VendorID>

       <InvoiceParty>

            <Role>BMC</Role>

            <Name1>IBM</Name1>

            <Street1>1290 ST</Street1>

            <PostalCode>70600</PostalCode>

            <ContactName>Sam Jersey</ContactName>

       </InvoiceParty>

       <InvoiceParty>

            <Role>IUD</Role>

            <Name1>URI Corp</Name1>

            <Street1>19th ST</Street1>

            <PostalCode>45322</PostalCode>

       </InvoiceParty>

    <InvoicePaymentDue>

      <PaymentDueDate>04132018</PaymentDueDate>

    </InvoicePaymentDue>

   

  </InvoiceHeader>

  <InvoiceLine>

     <LineNum>1</LineNum>

     <UnitPrice>100.00000</UnitPrice>

         <InvoiceLineDistribution>

          <DistributionLineNum>1</DistributionLineNum>

          <Amount>100.00</Amount>   

         </InvoiceLineDistribution>

  </InvoiceLine>

  <InvoiceSummary>

    <InvoiceTotVal>100.00</InvoiceTotVal>

  </InvoiceSummary>

</Invoice>

--2nd Invoice

</InvoiceList>

Below Is the table, need to extract data from above XML file and insert into below table.

Create table XXLE_INVOICE_DETAILS(Invoice_Num VARCHAR2(240),Vendor_ID VARCHAR2(240),

                                  Party_Role_1 VARCHAR2(40),Party_Role_1_Name VARCHAR2(80),Party_Role_1_Street VARCHAR2(240),Party_Role_1_Zip VARCHAR2(10),Party_Role_1_Contact VARCHAR2(240),

                                  Party_Role_2 VARCHAR2(40),Party_Role_2_Name VARCHAR2(80),Party_Role_2_Street VARCHAR2(240),Party_Role_2_Zip VARCHAR2(10),

                                  Payment_DueDate VARCHAR2(10),

                                  Line_Num NUMBER,Unit_Price NUMBER,

                                  Distribution_LineNum NUMBER,Amount NUMBER,

                                  Invoice_TotVal NUMBER);

INSERT INTO XXLE_INVOICE_DETAILS values ('INV9870','VE5423','BMC','IBM','1290 ST','70600','Sam Jersey','IUD','URI Corp','19th ST','45322','04132018',1,100,1,100,100); 

Thanks

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 17 2018
Added on Apr 13 2018
5 comments
7,224 views