This discussion is archived
8 Replies Latest reply: May 14, 2013 10:54 PM by SnigdhaBhanu RSS

Oracle to XML Data load

SnigdhaBhanu Newbie
Currently Being Moderated
Hi Friends

I have created and executed the procedure to generate XML Files from Oracle table.

I have got only one record in XML Files.

Requirement:_

All the records having Batch number with "Dummy" should be loaded in one XML File.
If Batch number is not Dummy then all the records should be loaded in another XML File.

XML Format should be ( as per requirement )_


<Inventory>
     <Bix_customer_number>12345 --- ( header 1)     
     <Article Number>123<Article Number/> -- Line item 1
<quantity>58.32<quantity/>
<batchnumber>87965<<batchnumber/>
<Article Number>689<Article Number/> -- Line item 2
<quantity>5.358<quantity/>
<batchnumber>52454<<batchnumber/>
<Article Number>879<Article Number/> -- Line item 3
<quantity>10.896<quantity/>
<batchnumber>546<<batchnumber/>
     <Bix_customer_number/> -- End of header
<Bix_customer_number>84635143 --- ( header 2 )     
     <Article Number>4414<Article Number/> -- Line item 1
<quantity>878<quantity/>
<batchnumber>25134<<batchnumber/>
<Article Number>853<Article Number/> -- Line item 2
<quantity>5.358<quantity/>
<batchnumber>54646<<batchnumber/>
     <Article Number>4561<Article Number/> -- Line item 3
<quantity>10.896<quantity/>
<batchnumber>53134<<batchnumber/>
     <Bix_customer_number/> -- End of header

<Bix_customer_number>8521533 --- ( header 3)
     <Article Number>4414<Article Number/> -- Line item 1
<quantity>878<quantity/>
<batchnumber>25134<<batchnumber/>
     <Article Number>853<Article Number/> -- Line item 2
<quantity>5.358<quantity/>
<batchnumber>54646<<batchnumber/>
     <Article Number>4561<Article Number/> -- Line item 3
<quantity>10.896<quantity/>
<batchnumber>53134<<batchnumber/>
     <Bix_customer_number/> -- End of header


<Inventory/>


Procedure*

Declare
doc clob;

begin

FOR r IN (
select batch_number,
xmlserialize(document
xmlelement("RecordType",
xmlelement("Header",
xmlforest( interface_type
, partner_id
, transfer_date
, bix_customer_code
, h_bix_article_num
, uom
, quantity_sold
, batch_number )
),
xmlagg(
xmlelement("detail",
xmlforest( item_type
, l_bix_article_num
, batch_number
, usable_until
, total_quantity
, qc_status
, manufacturing_date
, expiry_date
, xml_version )
)
)
)
as clob
) as xmldoc
from agg_apo_stock
group by interface_type
, partner_id
, transfer_date
, bix_customer_code
, h_bix_article_num
, uom
, quantity_sold
, batch_number
)
LOOP


dbms_xslprocessor.clob2file(
'<?xml version="1.0" encoding="UTF-8"?>' || r.xmldoc
, 'EP_DATA'
, 'Batch_number_'||r.batch_number||'.xml'
, nls_charset_id('AL32UTF8')
);
END LOOP;
end;


Result got from this procedure ( not as per Requirement )_


File Name: Batch_number_DUMMY.xml

Data:

<?xml version="1.0" encoding="UTF-8" ?>
- <RecordType>
- <Header>
<INTERFACE_TYPE>DWLP</INTERFACE_TYPE>
<PARTNER_ID>BICUSSA</PARTNER_ID>
<TRANSFER_DATE>20120814</TRANSFER_DATE>
<BIX_CUSTOMER_CODE>0001101437</BIX_CUSTOMER_CODE>
<H_BIX_ARTICLE_NUM>920174</H_BIX_ARTICLE_NUM>
<UOM>ST</UOM>
<QUANTITY_SOLD>46</QUANTITY_SOLD>
<BATCH_NUMBER>DUMMY</BATCH_NUMBER>
</Header>
- <detail>
<ITEM_TYPE>P</ITEM_TYPE>
<L_BIX_ARTICLE_NUM>920174</L_BIX_ARTICLE_NUM>
<BATCH_NUMBER>DUMMY</BATCH_NUMBER>
<USABLE_UNTIL>29993112</USABLE_UNTIL>
<MANUFACTURING_DATE>19000101</MANUFACTURING_DATE>
<EXPIRY_DATE>29993112</EXPIRY_DATE>
<_xFFFF_XML_VERSION>000</_xFFFF_XML_VERSION>
</detail>
</RecordType>


Help Required_

Please help me to modify the above procedure to meet the business requirement.


Thank you, Please help me in this regards.

Snigdha.
  • 1. Re: Oracle to XML Data load
    Jason_(A_Non) Expert
    Currently Being Moderated
    For the lazy amongst us, please include some INSERT statements that populate agg_apo_stock with your test data. Also please see the FAQ for how to use the
     tag to retain formatting on code.                                                                                                                                                                                                                                                                                                                                                                                                        
  • 2. Re: Oracle to XML Data load
    SnigdhaBhanu Newbie
    Currently Being Moderated
    Hi,

    I think i have not completely understood your reply.

    My source table is : agg_apo_stock

    Insert statement :

    insert into agg_apo_stock(INTERFACE_TYPE,PARTNER_ID,TRANSFER_DATE,BIX_CUSTOMER_CODE,H_BIX_ARTICLE_NUM,
    UOM,QUANTITY_SOLD,ITEM_TYPE,L_BIX_ARTICLE_NUM,BATCH_NUMBER,USABLE_UNTIL,TOTAL_QUANTITY,QC_STATUS,
    MANUFACTURING_DATE,EXPIRY_DATE,XML_VERSION,CREATED_DATE)
    values ('DWLP','BICUSSA','20120814','0001101437','300174','ST','14890','P','300174','DUMMY','29993112','23','X','19000101','29993112','0','sysdate')

    Target XML Files needed:_

    *1.*Inventory_XML_BATCH.XML
    *2.*Inventory_XML_NOBATCH.XML
  • 3. Re: Oracle to XML Data load
    Jason_(A_Non) Expert
    Currently Being Moderated
    Your sample shows one record in the table. Why do you expect to generate two output files from one record? You will need two or more records in your table to generate the two output files you are hoping for.
  • 4. Re: Oracle to XML Data load
    SnigdhaBhanu Newbie
    Currently Being Moderated
    Hi

    I have just given an insert statement as a sample.

    There are many records in the table.
    some of the records are

    insert into agg_apo_stock(INTERFACE_TYPE,PARTNER_ID,TRANSFER_DATE,BIX_CUSTOMER_CODE,H_BIX_ARTICLE_NUM,
    UOM,QUANTITY_SOLD,ITEM_TYPE,L_BIX_ARTICLE_NUM,BATCH_NUMBER,USABLE_UNTIL,TOTAL_QUANTITY,QC_STATUS,
    MANUFACTURING_DATE,EXPIRY_DATE,XML_VERSION,CREATED_DATE)
    values ('DWLP','BICUSSA','20120814','0001101437','300174','ST','14890','P','300174','12345','29993112','23','X','19000101','29993112','0')

    insert into agg_apo_stock(INTERFACE_TYPE,PARTNER_ID,TRANSFER_DATE,BIX_CUSTOMER_CODE,H_BIX_ARTICLE_NUM,
    UOM,QUANTITY_SOLD,ITEM_TYPE,L_BIX_ARTICLE_NUM,BATCH_NUMBER,USABLE_UNTIL,TOTAL_QUANTITY,QC_STATUS,
    MANUFACTURING_DATE,EXPIRY_DATE,XML_VERSION,CREATED_DATE)
    values ('DWLP','BICUSSA','20120815','0001101437','300175','ST','10','P','300175','65712','29993112','23','X','19000101','29993112','1')

    insert into agg_apo_stock(INTERFACE_TYPE,PARTNER_ID,TRANSFER_DATE,BIX_CUSTOMER_CODE,H_BIX_ARTICLE_NUM,
    UOM,QUANTITY_SOLD,ITEM_TYPE,L_BIX_ARTICLE_NUM,BATCH_NUMBER,USABLE_UNTIL,TOTAL_QUANTITY,QC_STATUS,
    MANUFACTURING_DATE,EXPIRY_DATE,XML_VERSION,CREATED_DATE)
    values ('DWLP','BICUSSA','20120815','0001101437','300176','ST','60','P','300175','534684','29993112','23','X','19000101','29993112','1')

    insert into agg_apo_stock(INTERFACE_TYPE,PARTNER_ID,TRANSFER_DATE,BIX_CUSTOMER_CODE,H_BIX_ARTICLE_NUM,
    UOM,QUANTITY_SOLD,ITEM_TYPE,L_BIX_ARTICLE_NUM,BATCH_NUMBER,USABLE_UNTIL,TOTAL_QUANTITY,QC_STATUS,
    MANUFACTURING_DATE,EXPIRY_DATE,XML_VERSION,CREATED_DATE)
    values ('DWLP','BICUSSA','20120816','0001105537','300569','ST','20','P','300569','DUMMY','29993112','23','X','19000101','29993112','0')

    insert into agg_apo_stock(INTERFACE_TYPE,PARTNER_ID,TRANSFER_DATE,BIX_CUSTOMER_CODE,H_BIX_ARTICLE_NUM,
    UOM,QUANTITY_SOLD,ITEM_TYPE,L_BIX_ARTICLE_NUM,BATCH_NUMBER,USABLE_UNTIL,TOTAL_QUANTITY,QC_STATUS,
    MANUFACTURING_DATE,EXPIRY_DATE,XML_VERSION,CREATED_DATE)
    values ('DWLP','BICUSSA','20120817','0001105537','300568','ST','30','P','300568','DUMMY','29993112','23','X','19000101','29993112','1')

    insert into agg_apo_stock(INTERFACE_TYPE,PARTNER_ID,TRANSFER_DATE,BIX_CUSTOMER_CODE,H_BIX_ARTICLE_NUM,
    UOM,QUANTITY_SOLD,ITEM_TYPE,L_BIX_ARTICLE_NUM,BATCH_NUMBER,USABLE_UNTIL,TOTAL_QUANTITY,QC_STATUS,
    MANUFACTURING_DATE,EXPIRY_DATE,XML_VERSION,CREATED_DATE)
    values ('DWLP','BICUSSA','20120817','0001105537','300375','ST','36','P','300375','DUMMY','29993112','23','X','19000101','29993112','1')


    There should be 2 XML files need to be generated based on batch number.
    IF batch number is dummy all the records should be in one XML
    If batch number exists then all the records should be in another XML

    The first 3 records should be in one XML as
    1 record header and next 2 records are details
    Header record contain the customer number and detail records will contain the BIX_ARTICLE_NUMBER's


    Next 3 records should be in another XML as
    1 record header and next 2 records are details

    Please help in writting a procedure for the above requirement.

    Thank you

    Snigdha.
  • 5. Re: Oracle to XML Data load
    Jason_(A_Non) Expert
    Currently Being Moderated
    1) Please provide a CREATE TABLE statement to go along with your inserts. I started too, but gave up after the issues in #2.

    2) Please fix your insert as you list 17 columns but only include VALUES for 16 of them. You also appear to have the column or data order for CREATED_DATE and XML_VERSION reversed. I would suggest you review the others to ensure they are correct too.

    Why are dates being stored as string and why in the YYYYDDMM format? I have never seen that pattern before.
  • 6. Re: Oracle to XML Data load
    SnigdhaBhanu Newbie
    Currently Being Moderated
    Hi,

    Am storing the date format in YYYYMMDD format because in the output XML files i need the date in that format only.

    Below are the insert statements

    insert into agg_apo_stock(INTERFACE_TYPE,PARTNER_ID,TRANSFER_DATE,BIX_CUSTOMER_CODE,H_BIX_ARTICLE_NUM,
    UOM,QUANTITY_SOLD,ITEM_TYPE,L_BIX_ARTICLE_NUM,BATCH_NUMBER,USABLE_UNTIL,TOTAL_QUANTITY,QC_STATUS,
    MANUFACTURING_DATE,EXPIRY_DATE,XML_VERSION)
    values ('DWLP','BICUSSA','20120814','0001101437','300174','ST','14890','P','300174','12345','29993112','23','X','19000101','29993112','0')

    insert into agg_apo_stock(INTERFACE_TYPE,PARTNER_ID,TRANSFER_DATE,BIX_CUSTOMER_CODE,H_BIX_ARTICLE_NUM,
    UOM,QUANTITY_SOLD,ITEM_TYPE,L_BIX_ARTICLE_NUM,BATCH_NUMBER,USABLE_UNTIL,TOTAL_QUANTITY,QC_STATUS,
    MANUFACTURING_DATE,EXPIRY_DATE,XML_VERSION)
    values ('DWLP','BICUSSA','20120815','0001101437','300175','ST','10','P','300175','65712','29993112','23','X','19000101','29993112','1')

    insert into agg_apo_stock(INTERFACE_TYPE,PARTNER_ID,TRANSFER_DATE,BIX_CUSTOMER_CODE,H_BIX_ARTICLE_NUM,
    UOM,QUANTITY_SOLD,ITEM_TYPE,L_BIX_ARTICLE_NUM,BATCH_NUMBER,USABLE_UNTIL,TOTAL_QUANTITY,QC_STATUS,
    MANUFACTURING_DATE,EXPIRY_DATE,XML_VERSION)
    values ('DWLP','BICUSSA','20120815','0001101437','300176','ST','60','P','300175','534684','29993112','23','X','19000101','29993112','1')

    insert into agg_apo_stock(INTERFACE_TYPE,PARTNER_ID,TRANSFER_DATE,BIX_CUSTOMER_CODE,H_BIX_ARTICLE_NUM,
    UOM,QUANTITY_SOLD,ITEM_TYPE,L_BIX_ARTICLE_NUM,BATCH_NUMBER,USABLE_UNTIL,TOTAL_QUANTITY,QC_STATUS,
    MANUFACTURING_DATE,EXPIRY_DATE,XML_VERSION)
    values ('DWLP','BICUSSA','20120816','0001105537','300569','ST','20','P','300569','DUMMY','29993112','23','X','19000101','29993112','0')

    insert into agg_apo_stock(INTERFACE_TYPE,PARTNER_ID,TRANSFER_DATE,BIX_CUSTOMER_CODE,H_BIX_ARTICLE_NUM,
    UOM,QUANTITY_SOLD,ITEM_TYPE,L_BIX_ARTICLE_NUM,BATCH_NUMBER,USABLE_UNTIL,TOTAL_QUANTITY,QC_STATUS,
    MANUFACTURING_DATE,EXPIRY_DATE,XML_VERSION)
    values ('DWLP','BICUSSA','20120817','0001105537','300568','ST','30','P','300568','DUMMY','29993112','23','X','19000101','29993112','1')

    insert into agg_apo_stock(INTERFACE_TYPE,PARTNER_ID,TRANSFER_DATE,BIX_CUSTOMER_CODE,H_BIX_ARTICLE_NUM,
    UOM,QUANTITY_SOLD,ITEM_TYPE,L_BIX_ARTICLE_NUM,BATCH_NUMBER,USABLE_UNTIL,TOTAL_QUANTITY,QC_STATUS,
    MANUFACTURING_DATE,EXPIRY_DATE,XML_VERSION)
    values ('DWLP','BICUSSA','20120817','0001105537','300375','ST','36','P','300375','DUMMY','29993112','23','X','19000101','29993112','1')



    Create Statement.
    CREATE TABLE "EP_DATA"."AGG_APO_STOCK"
    (     "INTERFACE_TYPE" VARCHAR2(10 BYTE),
         "PARTNER_ID" VARCHAR2(10 BYTE),
         "TRANSFER_DATE" VARCHAR2(10 BYTE),
         "BIX_CUSTOMER_CODE" VARCHAR2(10 BYTE),
         "H_BIX_ARTICLE_NUM" VARCHAR2(10 BYTE),
         "UOM" VARCHAR2(5 BYTE),
         "QUANTITY_SOLD" NUMBER(15,3),
         "ITEM_TYPE" VARCHAR2(5 BYTE),
         "L_BIX_ARTICLE_NUM" VARCHAR2(112 BYTE),
         "BATCH_NUMBER" VARCHAR2(12 BYTE),
         "USABLE_UNTIL" VARCHAR2(16 BYTE),
         "TOTAL_QUANTITY" NUMBER(15,3),
         "QC_STATUS" VARCHAR2(5 BYTE),
         "MANUFACTURING_DATE" VARCHAR2(10 BYTE),
         "EXPIRY_DATE" VARCHAR2(10 BYTE),
         "XML_VERSION" VARCHAR2(10 BYTE)
    )

    Please correct the procedure written.
    Thank you.
  • 7. Re: Oracle to XML Data load
    Jason_(A_Non) Expert
    Currently Being Moderated
    Am storing the date format in YYYYMMDD format because in the output XML files i need the date in that format only.
    You need to look at the [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions200.htm#SQLRF06129]to_char functionality of SQL, including the [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#i34924]Datetime Format Models that are available to you. Here is a quick demo.
    Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
     
    SQL> 
    SQL> create table date_example(date_col  DATE);
     
    Table created
    SQL> insert into date_example select sysdate from dual;
     
    1 row inserted
    SQL> select date_col,
      2         to_char(date_col, 'YYYYMMDD') fmt1,
      3         to_char(date_col, 'YYYYMMDD HH24:MI:SS') fmt2
      4    from date_example;
     
    DATE_COL    FMT1     FMT2
    ----------- -------- -----------------
    8/27/2012 9 20120827 20120827 09:29:05
    The XML format you listed in your first post for this thread is not valid XML. Spaces are not allowed in node names and there are extra starting tags for your batchnumber node. The contents of Bix_customer_number cannot be both data and XML, at least in the situation I think you are trying to do. The sample XML you provided is no where close to the XML format you list as a requirement so I'm not sure which is correct.

    As you are learning, in order to receive timely and accurate help, you need to provide all the details for someone to replicate your issue. When your sample data, sample code, and requirements all line up then we can help you better.
  • 8. Re: Oracle to XML Data load
    SnigdhaBhanu Newbie
    Currently Being Moderated
    i have resolved this by using Informatica mapping

Legend

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