1 2 Previous Next 20 Replies Latest reply: Aug 22, 2012 1:20 AM by SnigdhaBhanu RSS

    XML 1header and n detail records

    SnigdhaBhanu
      Hi All,

      AM new to XML Files generation.
      I need a procedure which satisfies my business requirement

      My business requirement is

      we need to generate XML target files based on customer number.and item number. The out put file should contain Customer details in header and detail record should contain all the items ,quantity which belong to that customer.

      For example if there are 4 customer's and each customer has 2 items under them then the XML files should be like

      <Header>
      customer number1
      </Header>
      <Detail>
      Item 1
      </Detail>
      <Detail>
      Item 2
      </Detail>

      In the above format i need to generate 4 XML files as i have 4 customers.

      Could you please help me in writing a stored procedure for this requirement.
      Thank you very much for your help in advance.

      Regards,
      Snigdha.

      Edited by: user8595010 on Aug 3, 2012 1:40 AM
        • 1. Re: XML 1header and n detail records
          AlexAnd
          for example
          SQL> create table cust_test(cust_num varchar2(100), detail varchar2(100));
           
          Table created
           
          SQL> 
          SQL> insert into cust_test(cust_num, detail) values ('customer number1', 'Item 1');
           
          1 row inserted
          SQL> insert into cust_test(cust_num, detail) values ('customer number1', 'Item 2');
           
          1 row inserted
          SQL> insert into cust_test(cust_num, detail) values ('customer number2', 'Item 1');
           
          1 row inserted
          SQL> insert into cust_test(cust_num, detail) values ('customer number2', 'Item 2');
           
          1 row inserted
          SQL> insert into cust_test(cust_num, detail) values ('customer number2', 'Item 3');
           
          1 row inserted
           
          SQL> 
          SQL> declare
            2   doc    clob;
            3  begin
            4    for x in (select cust_num from cust_test group by cust_num)
            5      loop
            6  
            7        select xmlserialize(document
            8                            xmlelement("customer",
            9                                       xmlelement("header", cust_num),
           10                                       xmlagg(xmlelement("detail", detail))) as clob)
           11          into doc
           12          from cust_test
           13         where cust_num = x.cust_num
           14         group by cust_num;
           15  
           16          dbms_xslprocessor.clob2file( '<?xml version="1.0" encoding="UTF-8"?>' || doc
           17                               , 'MYDIR'
           18                               , 'customer_'||x.cust_num||'.xml'
           19                               , nls_charset_id('AL32UTF8') );
           20  
           21        end loop;
           22  end;
           23  /
           
          PL/SQL procedure successfully completed
           
          SQL>
          result
          03.08.2012  13:29    <DIR>          .
          03.08.2012  13:29    <DIR>          ..
          03.08.2012  13:29               138 customer_customer number1.xml
          03.08.2012  13:29               161 customer_customer number2.xml
          for customer_customer number1.xml
          <?xml version="1.0" encoding="UTF-8"?><customer><header>customer number1</header><detail>Item 1</detail><detail>Item 2</detail></customer>
          • 2. Re: XML 1header and n detail records
            SnigdhaBhanu
            Thank you so much for ur message....am trying this now...

            Could you please let me know if we use clob in the procedure..will there be any effect to performace..

            am calling this procedure through INFORMATICA ETL Tool...


            Thank you...

            Snigdha.
            • 3. Re: XML 1header and n detail records
              Jason_(A_Non)
              Are you referring to how the example uses a CLOB? Oracle does not provide any methods to write an XMLType data type out to disk so it needs to be converted to another data type in order to be written out to disk. The example uses Oracle supplied methods to perform the data type conversion so you have to assume there is a very negligible performance impact.

              If you are referring to another usage of CLOBs in your code, you will need to specify so we understand what you were intending to ask.
              • 4. Re: XML 1header and n detail records
                SnigdhaBhanu
                Hi,

                Thank you got some idea regarding CLOB.

                Modified the Procedure according to the requirement as below
                am encountering the below issue when am executing it...can you please help me


                declare
                doc clob;
                begin
                for x in (select DISTINCT bix_customer_code from agg_apo_stock group by bix_customer_code)
                loop

                select xmlserialize(document
                xmlelement("RecordType",
                xmlelement("Header", interface_type, partner_id, transfer_date, bix_customer_code, h_bix_article_num, uom, quantity_sold, batch_number),
                xmlagg(xmlelement("detail", item_type, l_bix_article_num, batch_number, usable_until, total_quantity, qc_status, manufacturing_date, expiry_date, xml_version))) as clob)
                into doc
                from agg_apo_stock
                where bix_customer_code = x.bix_customer_code
                group by interface_type, partner_id, transfer_date, bix_customer_code, h_bix_article_num, uom, quantity_sold, batch_number;

                dbms_xslprocessor.clob2file( '<?xml version="1.0" encoding="UTF-8"?>' || doc
                , 'MYDIR'
                , 'BIX_CUSTOMER_'||x.bix_customer_code||'.xml'
                , nls_charset_id('AL32UTF8') );

                end loop;
                end;
                /


                Error report:

                ORA-01422: exact fetch returns more than requested number of rows
                ORA-06512: at line 7
                01422. 00000 - "exact fetch returns more than requested number of rows"
                *Cause:    The number specified in exact fetch is less than the rows returned.
                *Action:   Rewrite the query or change number of rows requested

                Analysis
                I have searched in internet to resolve this issue, in all the forums they are asking to specify the rowcount. If i specify the rowcount i might miss the records existing in the table.

                Question 2 :
                in the procedure the files are stored in 'MYDIR' , could you please let me know where can we see the XML Files?

                Thank you so much for your response.

                Edited by: user8595010 on Aug 7, 2012 2:37 AM
                • 5. Re: XML 1header and n detail records
                  AlexAnd
                  try
                  >
                  xmlelement("Header", interface_type, partner_id, transfer_date, bix_customer_code, h_bix_article_num, uom, quantity_sold, batch_number),
                  >

                  change to
                  xmlelement("Header", xmlforest(interface_type, partner_id, transfer_date, bix_customer_code, h_bix_article_num, uom, quantity_sold, batch_number)),
                  also correct group by clause
                  • 6. Re: XML 1header and n detail records
                    odie_63
                    This will certainly not give what you expect :
                    xmlelement("Header", interface_type, partner_id, transfer_date, bix_customer_code, h_bix_article_num, uom, quantity_sold, batch_number),
                    I'd use this instead (give the proper aliasing for XMLForest items, if necessary) :
                    FOR r IN (
                      select bix_customer_code
                             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
                      , 'MYDIR'
                      , 'BIX_CUSTOMER_'||r.bix_customer_code||'.xml'
                      , nls_charset_id('AL32UTF8') 
                      );
                    
                    END LOOP;
                    Question 2 :
                    in the procedure the files are stored in 'MYDIR' , could you please let me know where can we see the XML Files?
                    MYDIR is an Oracle directory object pointing to a physical location.
                    If you're the one who created it, you should know where it points.
                    If not, query DBA_DIRECTORIES dictionary view.
                    • 7. Re: XML 1header and n detail records
                      954220
                      Hi,
                      Please try it this way
                      DECLARE
                      v_file UTL_FILE.file_type;
                      v_xml CLOB;
                      v_more BOOLEAN := TRUE;
                      BEGIN
                      -- Create XML document from query.
                      v_xml := DBMS_XMLQUERY.getxml('SELECT table_name, tablespace_name FROM user_tables WHERE rownum & 6');

                      -- Output XML document to file.
                      v_file := UTL_FILE.fopen('C:\Development\XML\', 'test1.xml', 'w');
                      WHILE v_more LOOP
                      UTL_FILE.put(v_file, Substr(v_xml, 1, 32767));
                      IF LENGTH(v_xml) > 32767 THEN
                      v_xml := SUBSTR(v_xml, 32768);
                      ELSE
                      v_more := FALSE;
                      END IF;
                      END LOOP;
                      UTL_FILE.fclose(v_file);

                      EXCEPTION
                      WHEN OTHERS THEN
                      DBMS_OUTPUT.put_line(Substr(SQLERRM,1,255));
                      UTL_FILE.fclose(v_file);
                      END;
                      /


                      You can find more options...
                      http://www.oracle-base.com/articles/9i/xml-generation-9i.php

                      Edited by: 951217 on Aug 7, 2012 8:38 AM
                      • 8. Re: XML 1header and n detail records
                        SnigdhaBhanu
                        Hi,

                        Thank you so much for your responce.

                        I have modified the query according to your responce as below..am new to procedure in writting..

                        declare
                        doc clob;
                        begin
                        FOR r IN (
                        select bix_customer_code
                        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
                        , 'MYDIR'
                        , 'BIX_CUSTOMER_'||r.bix_customer_code||'.xml'
                        , nls_charset_id('AL32UTF8')
                        );
                        END LOOP;
                        end;
                        /

                        Error report:
                        ORA-06550: line 5, column 22:
                        PL/SQL: ORA-00923: FROM keyword not found where expected
                        ORA-06550: line 4, column 10:
                        PL/SQL: SQL Statement ignored
                        06550. 00000 - "line %s, column %s:\n%s"
                        *Cause:    Usually a PL/SQL compilation error.


                        Analysis
                        I did my analysis to correct this issue but nothing is working.

                        Help
                        Could you please rectify the error in the above code and post it.

                        Thank you so much...
                        • 9. Re: XML 1header and n detail records
                          AlexAnd
                          >
                          select bix_customer_code
                          xmlserialize...
                          >
                          to
                          select bix_customer_code , -- this
                          xmlserialize...
                          • 10. Re: XML 1header and n detail records
                            SnigdhaBhanu
                            Thank you,

                            After putting the comma the error did not come.
                            I have some questions regarding the MYDIR usage.

                            I have tried using the below command to create directory

                            CREATE DIRECTORY mydir AS '/scratch/file_data';

                            But am getting the error message as below

                            Error report:
                            ORA-29280: invalid directory path
                            ORA-06512: at "SYS.UTL_FILE", line 41
                            ORA-06512: at "SYS.UTL_FILE", line 479
                            ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 317
                            ORA-06512: at line 46
                            29280. 00000 - "invalid directory path"
                            *Cause:    A corresponding directory object does not exist.
                            *Action:   Correct the directory object parameter, or create a corresponding
                            directory object with the CREATE DIRECTORY command.

                            When i was looking about the directories it seems it can store only upto 30bytes.

                            But i need the files to be sent to the below server sihvmetldwhd01
                            Below is the path:
                            \\sihvmetldwhd01\d$\Informatica\9.0.1\server\infa_shared\TgtFiles\MENA ( Windows server)

                            Please help me how can i transfer XML files to the above mentioned server directory.

                            Thank you

                            Regards,
                            Snigdha.
                            • 11. Re: XML 1header and n detail records
                              AlexAnd
                              if oracle server on linux then you can mount windows directory to linux then create oracle directory to this path

                              also be sure that your current user have needed permissions on oracle directory (grant read, write if needed from owner)

                              http://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_5007.htm
                              • 12. Re: XML 1header and n detail records
                                SnigdhaBhanu
                                My Oracle server is on Windows.

                                I have executed the commands existing in the below link
                                http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_5007.htm


                                CREATE DIRECTORY admin AS 'oracle/admin';

                                CREATE OR REPLACE DIRECTORY bfile_dir AS '/private1/LOB/files';

                                the commands are getting successful but i could not see the directory in Client: Oracle SQL Developer
                                Under the Directories structure.
                                Even though i use the directory bfile_dir am getting the following error

                                Error report:
                                ORA-29280: invalid directory path
                                ORA-06512: at "SYS.UTL_FILE", line 41
                                ORA-06512: at "SYS.UTL_FILE", line 479
                                ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 317
                                ORA-06512: at line 46
                                29280. 00000 - "invalid directory path"
                                *Cause:    A corresponding directory object does not exist.
                                *Action:   Correct the directory object parameter, or create a corresponding
                                directory object with the CREATE DIRECTORY command.

                                I want to load the XML files into the directory on server
                                \\sihvmetldwhd01\d$\Informatica\9.0.1\server\infa_shared\TgtFiles\MENA ( Windows)

                                Thank you
                                • 13. Re: XML 1header and n detail records
                                  odie_63
                                  user8595010 wrote:
                                  My Oracle server is on Windows.

                                  I have executed the commands existing in the below link
                                  http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_5007.htm


                                  CREATE DIRECTORY admin AS 'oracle/admin';

                                  CREATE OR REPLACE DIRECTORY bfile_dir AS '/private1/LOB/files';
                                  Really, you've run those commands verbatim?
                                  Those are documentation examples, obviously you have to use your own location.

                                  I want to load the XML files into the directory on server
                                  \\sihvmetldwhd01\d$\Informatica\9.0.1\server\infa_shared\TgtFiles\MENA ( Windows)
                                  CREATE DIRECTORY target_dir AS '\\sihvmetldwhd01\d$\Informatica\9.0.1\server\infa_shared\TgtFiles\MENA';
                                  Don't forget to grant the necessary OS privileges to the Oracle user too.

                                  If this directory is on the same server as the database, then you can directly do :
                                  CREATE DIRECTORY target_dir AS 'd:\Informatica\9.0.1\server\infa_shared\TgtFiles\MENA';
                                  no need to use UNC path in this case.

                                  Edited by: odie_63 on 8 août 2012 10:49
                                  • 14. Re: XML 1header and n detail records
                                    AlexAnd
                                    >
                                    My Oracle server is on Windows.
                                    >
                                    so you must indicate windows path notation 'c:\tmp' not linux '/tmp'

                                    in sqldeveloper
                                    CREATE DIRECTORY test_dir AS 'c:\tmp';
                                    
                                    directory TEST_DIR created.
                                    
                                    
                                    select * from dba_directories
                                    OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
                                    ------------------------------ ------------------------------ --------------
                                    SCOTT                            TEST_DIR                       c:\tmp        
                                    1 2 Previous Next