8 Replies Latest reply: May 15, 2013 12:54 AM by SnigdhaBhanu RSS

    Oracle to XML Data load

    SnigdhaBhanu
      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)
          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
            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)
              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
                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)
                  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
                    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)
                      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
                        i have resolved this by using Informatica mapping