This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Aug 21, 2012 11:20 PM by SnigdhaBhanu RSS

XML 1header and n detail records

SnigdhaBhanu Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    select bix_customer_code
    xmlserialize...
    >
    to
    select bix_customer_code , -- this
    xmlserialize...
  • 10. Re: XML 1header and n detail records
    SnigdhaBhanu Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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

Legend

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