This discussion is archived
13 Replies Latest reply: Nov 12, 2012 4:50 AM by 973268 RSS

Loading XML file into Oracle XML Table/OWB

973268 Newbie
Currently Being Moderated
Hi Gurus,

Need help in loading the XML file into oracle XML table /OWB.

Below is the sample XML file

<GEBIZ_PURCHASE_ORDER xmlns="http://ordabc.xmlbeans.order.importexport.de">
<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 I have created the below table and procedure

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));


declare
acct_doc xmltype := xmltype( bfilename('FLAT_FILES','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(
'/GEBIZ_PURCHASE_ORDER/ITEMS/ITEM'
passing acct_doc
columns header_no for ordinality
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;

when I run the above procedure it is running successfully but the records are not loading into table.

when i remove the attribute xmlns="http://ordabc.xmlbeans.order.importexport.de" and run the procedure then the records are loading.

can you please suggest is there any way to the xml file without modifying? (removing the attribute)

If choose XML table /OWB for loading XML files then what are challeges/issues we may face.
  • 1. Re: Loading XML file into Oracle XML Table/OWB
    odie_63 Guru
    Currently Being Moderated
    Add XMLNAMESPACES clause on both XMLTable expressions :
    ...
    
    FROM XMLTABLE(
      XMLNamespaces(default 'http://ordabc.xmlbeans.order.importexport.de')
    , '/GEBIZ_PURCHASE_ORDER/HEADER'
    
    ...
  • 2. Re: Loading XML file into Oracle XML Table/OWB
    973268 Newbie
    Currently Being Moderated
    Hi Odie,

    Thanks alot... :-)

    Can we load structured/Unstructured/attributes/CDATA XML files by using XML table?
  • 3. Re: Loading XML file into Oracle XML Table/OWB
    odie_63 Guru
    Currently Being Moderated
    Can we load structured/Unstructured/attributes/CDATA XML files by using XML table?
    What do you mean by "structured/unstructured"? Please explain with an example.
  • 4. Re: Loading XML file into Oracle XML Table/OWB
    973268 Newbie
    Currently Being Moderated
    Hi Gurus,

    Please help me the way for loading below xml file using xmltype?

    this XML file contains the combination of attributes and elements.

    <?xml version="1.0" encoding="UTF-8" ?>
    - <Feed xmlns="http://www.abcvoi.com/xs/PRR/xyz/5.7" name="ES" extractDate="2012-11-05T05:39:26.080-06:00">
    - <Product id="703" disabled="false" removed="false">
    <Source>ES</Source>
    <ExternalId>703</ExternalId>
    <Name>Casual men Dress</Name>
    - <Names>
    <Name locale="da_DK">Casual men Dress</Name>
    <Name locale="de">children Dress</Name>
    <Name locale="de_AT">men Dress</Name>
    <Name locale="en_GB">women Dress</Name>
    </Names>
    <Description>Celebrating 40 years</Description>
    - <Descriptions>
    <Description locale="da_DK">women</Description>
    <Description locale="de">jähriges</Description>
    <Description locale="de_AT">Men</Description>
    <Description locale="sv_SE">child</Description>
    </Descriptions>
    </Product>
    </Feed>

    Edited by: 970265 on Nov 9, 2012 4:13 AM
  • 5. Re: Loading XML file into Oracle XML Table/OWB
    973268 Newbie
    Currently Being Moderated
    Hi Odie,

    can you please suggest the way for loading of XML attributes?

    Thanks
  • 6. Re: Loading XML file into Oracle XML Table/OWB
    odie_63 Guru
    Currently Being Moderated
    What's the expected output/table structure?
  • 7. Re: Loading XML file into Oracle XML Table/OWB
    973268 Newbie
    Currently Being Moderated
    Hi Odie,

    below is the table structure.

    create table feed ( Product id varchar2(20),
    source varchar2(20),
    externalid varchar2(20),
    name varchar2(20),
    name1 varchar2(20),
    locale varchar2(20),
    description varchar2(20),
    description1 varchar2(20),
    locale 1varchar2(20))

    Thanks
  • 8. Re: Loading XML file into Oracle XML Table/OWB
    odie_63 Guru
    Currently Being Moderated
    So I take it you only want to store the first occurrence of repeating elements?
    SQL> select *
      2  from xmltable(
      3         xmlnamespaces(default 'http://www.abcvoi.com/xs/PRR/xyz/5.7')
      4       , '/Feed/Product'
      5         passing xmltype(bfilename('TEST_DIR','feed.xml'), nls_charset_id('AL32UTF8'))
      6         columns product_id   varchar2(20) path '@id'
      7               , source       varchar2(20) path 'Source'
      8               , external_id  varchar2(20) path 'ExternalId'
      9               , name         varchar2(20) path 'Name'
     10               , name1        varchar2(20) path 'Names/Name[1]'
     11               , locale       varchar2(20) path 'Names/Name[1]/@locale'
     12               , description  varchar2(20) path 'Description'
     13               , description1 varchar2(20) path 'Descriptions/Description[1]'
     14               , locale1      varchar2(20) path 'Descriptions/Description[1]/@locale'
     15       )
     16  ;
     
    PRODUCT_ID           SOURCE               EXTERNAL_ID          NAME                 NAME1                LOCALE               DESCRIPTION          DESCRIPTION1         LOCALE1
    -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
    703                  ES                   703                  Casual men Dress     Casual men Dress     da_DK                Celebrating 40 years women                da_DK
     
  • 9. Re: Loading XML file into Oracle XML Table/OWB
    973268 Newbie
    Currently Being Moderated
    Hi Odie,

    The XML file which contains multiple name, description and locale information, but the query which you have shared is showing only one column.

    Please suggest the way for loading all the information.

    Thanks
  • 10. Re: Loading XML file into Oracle XML Table/OWB
    odie_63 Guru
    Currently Being Moderated
    The XML file which contains multiple name, description and locale information, but the query which you have shared is showing only one column.
    I'm aware of that.
    The fact is you can't load all the information into a single table.
    Uncorrelated repeating groups such as Descriptions and Names have to be loaded in separate tables.

    Is that what you want?
  • 11. Re: Loading XML file into Oracle XML Table/OWB
    973268 Newbie
    Currently Being Moderated
    yes... can you please help me with the query for loading into seperate tables?
  • 12. Re: Loading XML file into Oracle XML Table/OWB
    odie_63 Guru
    Currently Being Moderated
    Something like this : http://odieweblog.wordpress.com/2012/05/10/how-to-load-xml-data-into-multiple-tables/
    SQL> insert all
      2    when name_seq = 1 and desc_seq = 1
      3         then into feed (productid, source, externalid, name, description)
      4                 values (productid, source, externalid, h_name, h_desc)
      5    when desc_seq = 1
      6         then into feed_names (productid, locale, name)
      7                       values (productid, n_locale, name)
      8    when name_seq = 1
      9         then into feed_descriptions (productid, locale, description)
     10                              values (productid, d_locale, description)
     11  select h.productid, h.source, h.externalid, h.name as h_name, h.description as h_desc
     12       , x1.locale as n_locale, x1.name, x1.name_seq
     13       , x2.locale as d_locale, x2.description, x2.desc_seq
     14  from xmltable(
     15         xmlnamespaces(default 'http://www.abcvoi.com/xs/PRR/xyz/5.7')
     16       , '/Feed/Product'
     17         passing xmltype(bfilename('TEST_DIR','feed.xml'), nls_charset_id('AL32UTF8'))
     18         columns productid    varchar2(20) path '@id'
     19               , source       varchar2(20) path 'Source'
     20               , externalid   varchar2(20) path 'ExternalId'
     21               , name         varchar2(20) path 'Name'
     22               , description  varchar2(20) path 'Description'
     23               , names        xmltype      path 'Names'
     24               , descriptions xmltype      path 'Descriptions'
     25       ) h
     26     , xmltable(
     27         xmlnamespaces(default 'http://www.abcvoi.com/xs/PRR/xyz/5.7')
     28       , '/Names/Name'
     29         passing h.names
     30         columns locale       varchar2(20) path '@locale'
     31               , name         varchar2(20) path 'text()'
     32               , name_seq     for ordinality
     33       ) x1
     34     , xmltable(
     35         xmlnamespaces(default 'http://www.abcvoi.com/xs/PRR/xyz/5.7')
     36       , '/Descriptions/Description'
     37         passing h.descriptions
     38         columns locale       varchar2(20) path '@locale'
     39               , description  varchar2(20) path 'text()'
     40               , desc_seq     for ordinality
     41       ) x2
     42  ;
     
    9 rows inserted
     
    SQL> select * from feed;
     
    PRODUCTID            SOURCE               EXTERNALID           NAME                 DESCRIPTION
    -------------------- -------------------- -------------------- -------------------- --------------------
    703                  ES                   703                  Casual men Dress     Celebrating 40 years
     
    SQL> select * from feed_names;
     
    PRODUCTID            LOCALE               NAME
    -------------------- -------------------- --------------------
    703                  da_DK                Casual men Dress
    703                  de                   children Dress
    703                  de_AT                men Dress
    703                  en_GB                women Dress
     
    SQL> select * from feed_descriptions;
     
    PRODUCTID            LOCALE               DESCRIPTION
    -------------------- -------------------- --------------------
    703                  da_DK                women
    703                  de                   jähriges
    703                  de_AT                Men
    703                  sv_SE                child
     
    This solution assumes there are always at least one Name and one Description under Names and Descriptions (respectively) for each Product.
    If not, you'll need to add OUTER JOINs and modify the conditions accordingly in WHEN statements.
  • 13. Re: Loading XML file into Oracle XML Table/OWB
    973268 Newbie
    Currently Being Moderated
    Thanks Guru :-)

Legend

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