This discussion is archived
4 Replies Latest reply: Oct 18, 2012 5:38 AM by odie_63 RSS

Insert Data in XML format to Oracle Table

Priyanka Pawar Newbie
Currently Being Moderated
Hello All,

I have a requirement where data is coming in XML format and i need to insert it into the Oracle Table. For example I am receiving XML data in following format,
<Results>
<Row>
<BANK_ACCOUNT_ID>10010</BANK_ACCOUNT_ID>
<BANK_ID>300968</BANK_ID>
<LEGAL_ENTITY>Vision Operations</LEGAL_ENTITY>
<BANK_NAME>Bank of America</BANK_NAME>
<BANK_ACCOUNT_NUM>10271-17621-619</BANK_ACCOUNT_NUM>
<BANK_ACCOUNT_NAME>BofA-204</BANK_ACCOUNT_NAME>
<BRANCH_NAME>New York</BRANCH_NAME>
<CURRENCY_CODE>USD</CURRENCY_CODE>
<BALANCE_DATE>2007-11-09</BALANCE_DATE>
<LEDGER_BALANCE>432705900.56</LEDGER_BALANCE>
</Row>
<Row>
<BANK_ACCOUNT_ID>10091</BANK_ACCOUNT_ID>
<BANK_ID>300984</BANK_ID>
<LEGAL_ENTITY>Vision Industries</LEGAL_ENTITY>
<BANK_NAME>Barclay's Bank</BANK_NAME>
<BANK_ACCOUNT_NUM>70986798</BANK_ACCOUNT_NUM>
<BANK_ACCOUNT_NAME>Barclay's Bank Multi Currency-626</BANK_ACCOUNT_NAME>
<BRANCH_NAME>Reading</BRANCH_NAME>
<CURRENCY_CODE>GBP</CURRENCY_CODE>
<BALANCE_DATE>2007-11-14</BALANCE_DATE>
<LEDGER_BALANCE>24244085.24</LEDGER_BALANCE>
</Row>
<Row>
<BANK_ACCOUNT_ID>10127</BANK_ACCOUNT_ID>
<BANK_ID>300968</BANK_ID>
<LEGAL_ENTITY>SSC US 01</LEGAL_ENTITY>
<BANK_NAME>Bank of America</BANK_NAME>
<BANK_ACCOUNT_NUM>4898744</BANK_ACCOUNT_NUM>
<BANK_ACCOUNT_NAME>BofA SSC US 02-7188</BANK_ACCOUNT_NAME>
<BRANCH_NAME>New York</BRANCH_NAME>
<CURRENCY_CODE>USD</CURRENCY_CODE>
<BALANCE_DATE>2007-11-28</BALANCE_DATE>
<LEDGER_BALANCE>10783815.28</LEDGER_BALANCE>
</Row>
</Results>

I want to write PLSQL code which will receive this data with XML tags and insert it into Oracle Table. Is it possible using inbuilt XML functionalities provided in Oracle Database??

Please Guide..

Regards,
Priyanka
  • 1. Re: Insert Data in XML format to Oracle Table
    SaurabhAg Explorer
    Currently Being Moderated
    Hello Priyanka,

    If i understood you correctly, you want to store the entire xml data in oracle table.

    There is a data type called xmltype. So, if any of your column of a table is of datatype xmltype, you should be able to store the xml data in those columns.

    http://www.oracle-base.com/articles/9i/xmltype-datatype.php

    Thanks
    Saurabh
  • 2. Re: Insert Data in XML format to Oracle Table
    odie_63 Guru
    Currently Being Moderated
    That's a frequently asked question.

    Use the search feature :

    Search Terms: <paste the thread title here>
    Category or Forum: XML
    Date Range: All

    Click the <Search> button.
    Sort the results by date.


    After looking at existing posts, if you still have issues, please post :

    - database version
    - what you've tried so far
    - error message
    - where the input XML comes from
  • 3. Re: Insert Data in XML format to Oracle Table
    Priyanka Pawar Newbie
    Currently Being Moderated
    Database Version i am using is : 11.1.0.7

    XML file i will receive using Web Service. Using PL/SQL code i have to insert it to the Oracle table and then i want to fetch values from tags. For that i have created one table with columns of data type "XMLTYPE". I have inserted XLM data in that table,

    1. Create table script
    CREATE TABLE xxios_xml_data_test(xml_data XMLTYPE);

    2. Insert Script
    INSERT INTO xxios_xml_data_test VALUES ( XMLType('<Results>
    <Row>
    <ORG_ID>204</ORG_ID>
    <REQ_NUMBER>14447</REQ_NUMBER>
    <REQ_LINE>1</REQ_LINE>
    <PO_NUMBER>6444</PO_NUMBER>
    <EXPECTED_REC_QTY>1</EXPECTED_REC_QTY>
    <USER_NAME>OPERATIONS</USER_NAME>
    </Row>
    <Row>
    <ORG_ID>204</ORG_ID>
    <REQ_NUMBER>14450</REQ_NUMBER>
    <REQ_LINE>1</REQ_LINE>
    <PO_NUMBER>6445</PO_NUMBER>
    <EXPECTED_REC_QTY>1</EXPECTED_REC_QTY>
    <USER_NAME>OPERATIONS</USER_NAME>
    </Row>
    </Results>'));

    3. Select statement i wrote to get tag values,
    SELECT w.xml_data.extract('/Results/Row/ORG_ID/text()').getStringVal()"ORG_ID",
    w.xml_data.extract('/Results/Row/REQ_NUMBER/text()').getStringVal()"REQ_NUMBER",
    w.xml_data.extract('/Results/Row/REQ_LINE/text()').getStringVal()"REQ_LINE",
    w.xml_data.extract('/Results/Row/PO_NUMBER/text()').getStringVal()"PO_NUMBER",
    w.xml_data.extract('/Results/Row/EXPECTED_REC_QTY/text()').getStringVal()"EXPECTED_REC_QTY",
    w.xml_data.extract('/Results/Row/USER_NAME/text()').getStringVal()"USER_NAME"
    FROM xxios_xml_data_test w

    But problem is XML file is having details of two records if you carefully observ the XML file. But using above select statement i am getting output in following format,
    ORG_ID     REQ_NUMBER     REQ_LINE     PO_NUMBER     EXPECTED_REC_QTY     USER_NAME
    204204     1444714450     11     64446445     11     OPERATIONSOPERATIONS

    How can i get result in following format ??
    ORG_ID     REQ_NUMBER     REQ_LINE     PO_NUMBER     EXPECTED_REC_QTY     USER_NAME
    204     14447     1     6444     1     OPERATIONS
    204     14450     1     6445     1     OPERATIONS

    Regards,
    Priyanka
  • 4. Re: Insert Data in XML format to Oracle Table
    odie_63 Guru
    Currently Being Moderated
    But problem is XML file is having details of two records if you carefully observ the XML file. But using above select statement i am getting output in following format,
    ORG_ID     REQ_NUMBER     REQ_LINE     PO_NUMBER     EXPECTED_REC_QTY     USER_NAME
    204204     1444714450     11     64446445     11     OPERATIONSOPERATIONS
    The table has only one row, so you get only one row as output.
    I'm surprised you didn't find any useful examples showing how to split the data in multiple rows.

    1) Create the table with the following option, it'll optimize storage and query performance for large XML documents :
    CREATE TABLE xxios_xml_data_test(xml_data XMLTYPE)
    XMLTYPE COLUMN xml_data STORE AS SECUREFILE BINARY XML
    ;
    2) Query the table with :
    SQL> select x.*
      2  from xxios_xml_data_test t
      3     , xmltable(
      4         '/Results/Row'
      5         passing t.xml_data
      6         columns ORG_ID           number       path 'ORG_ID'
      7               , REQ_NUMBER       number       path 'REQ_NUMBER'
      8               , REQ_LINE         number       path 'REQ_LINE'
      9               , PO_NUMBER        number       path 'PO_NUMBER'
     10               , EXPECTED_REC_QTY number       path 'EXPECTED_REC_QTY'
     11               , USER_NAME        varchar2(30) path 'USER_NAME'
     12       ) x
     13  ;
     
        ORG_ID REQ_NUMBER   REQ_LINE  PO_NUMBER EXPECTED_REC_QTY USER_NAME
    ---------- ---------- ---------- ---------- ---------------- ------------------------------
           204      14447          1       6444                1 OPERATIONS
           204      14450          1       6445                1 OPERATIONS
     

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points