This discussion is archived
8 Replies Latest reply: Jun 10, 2012 3:36 PM by 942747 RSS

How to write xml data to a file

557444 Newbie
Currently Being Moderated
Hi all,

We have a requirement of writing the xml data into a file in given directory in the server. Generating the xml data using the sql query below.

SELECT XMLELEMENT ("ROW",
XMLELEMENT ("CELL", xmlattributes ('EBIZCZMDL_01' AS "colname"), inventory_item_id),
XMLELEMENT ("CELL", xmlattributes ('EBIZFFMT_01' AS "COLNAME"), attribute29),
XMLELEMENT ("CELL", xmlattributes ('COMMON' AS "COLNAME"),inventory_item_id || '' || attribute29 || 'ITM')
AS "RESULT") "XMLDATA"
FROM apps.mtl_system_items_b

When we try to write the the data from this query to a file using UTL_FILE.put_line, the script gives the error
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

If somebody can help us pls.......

Thanks in advance
  • 1. Re: How to write xml data to a file
    odie_63 Guru
    Currently Being Moderated
    Hi,

    UTL_FILE.PUT_LINE writes VARCHAR2 buffers to a file.
    The query returns an XMLType instance.

    Use getClobVal or getStringVal methods (or XMLSerialize in 11g) to convert the XMLType into a character stream.

    BTW, if you're on 10g or above, the most straightforward method to write the file is to call DBMS_XSLPROCESSOR.clob2file.
  • 2. Re: How to write xml data to a file
    557444 Newbie
    Currently Being Moderated
    Hi,

    Thanks for the reply....
    We are using 10g so I used dbms_xslprocessor.clob2file to write the data to the file.
    but I expected 10 lines in the file where as I see only one line.....
    The same SQL will give 10 rows when it is run independently
    Wat could be the issue?

    Thanks
  • 3. Re: How to write xml data to a file
    odie_63 Guru
    Currently Being Moderated
    The same SQL will give 10 rows when it is run independently
    Wat could be the issue?
    Are you using a cursor loop?

    Just to be certain, the complete file should look like the following, right?
    <ROW>
     <CELL COLNAME="EBIZCZMDL_01">some value</CELL>
     <CELL COLNAME="EBIZFFMT_01">some value</CELL>
     <CELL COLNAME="COMMON">some value</CELL>
    </ROW>
    <ROW>
     <CELL COLNAME="EBIZCZMDL_01">some value</CELL>
     <CELL COLNAME="EBIZFFMT_01">some value</CELL>
     <CELL COLNAME="COMMON">some value</CELL>
    </ROW>
    <ROW>
     <CELL COLNAME="EBIZCZMDL_01">some value</CELL>
     <CELL COLNAME="EBIZFFMT_01">some value</CELL>
     <CELL COLNAME="COMMON">some value</CELL>
    </ROW>
    ...
    If so, you can aggregate all rows in the same query, this way :
    DECLARE
    
     v_xmldoc CLOB;
    
    BEGIN
    
     SELECT XMLAgg(
              XMLElement("ROW",
                XMLElement("CELL", xmlattributes('EBIZCZMDL_01' AS "colname"), inventory_item_id)
              , XMLElement("CELL", xmlattributes('EBIZFFMT_01' AS "COLNAME"), attribute29)
              , XMLElement("CELL", xmlattributes('COMMON' AS "COLNAME"), inventory_item_id || '' || attribute29 || 'ITM') 
              )
            ).getClobVal()
     INTO v_xmldoc
     FROM apps.mtl_system_items_b
     ;
    
     dbms_xslprocessor.clob2file(v_xmldoc, 'XML_DIR', 'myfile.xml');
    
    END;
    /
  • 4. Re: How to write xml data to a file
    557444 Newbie
    Currently Being Moderated
    Hi Odie,

    We are looking at the xml data to be like the below:

    - <xref xmlns="http://xmlns.oracle.com/xref">
    - <table name="NAPP_ITEM_ITEMID">
    - <columns>
    <column name="EBIZFFMT_01" />
    <column name="COMMON" />
    <column name="EBIZQOT_01" />
    <column name="EBIZCZMDL_01" />
    <column name="EBIZCZGOLD_01" />
    </columns>
    - <rows>
    - <row>
    <cell colName="EBIZFFMT_01">154</cell>
    <cell colName="COMMON">154811809990111362727</cell>
    <cell colName="EBIZQOT_01">81</cell>
    <cell colName="EBIZCZMDL_01">91</cell>
    <cell colName="EBIZCZGOLD_01">991</cell>
    </row>
    - <row>
    <cell colName="EBIZFFMT_01">180</cell>
    <cell colName="COMMON">180811809990111362727</cell>
    <cell colName="EBIZQOT_01">871</cell>
    <cell colName="EBIZCZMDL_01">731</cell>
    <cell colName="EBIZCZGOLD_01">341</cell>
    </row>
    </rows>
    </table>
    </xref>

    We have managed half way trying to get the structure. As we are new to this XML Programming.
    The call to dbms_xslprocessor.clob2file is within the cursor loop for the given SQL.

    DECLARE
    l_file_name VARCHAR2 (30);
    l_file_path VARCHAR2 (200);

    CURSOR xml_cur
    IS
    SELECT XMLELEMENT
    ("ROW",
    XMLELEMENT ("CELL",
    xmlattributes ('EBIZCZMDL_01' AS "colname"),
    inventory_item_id
    ),
    XMLELEMENT ("CELL",
    xmlattributes ('EBIZFFMT_01' AS "COLNAME"),
    attribute29
    ),
    XMLELEMENT ("CELL",
    xmlattributes ('COMMON' AS "COLNAME"),
    inventory_item_id || '' || attribute29 || 'ITM'
    ) AS "RESULT"
    ) "XMLDATA"
    FROM apps.mtl_system_items_b
    WHERE attribute29 IS NOT NULL;
    BEGIN
    l_file_path := '/usr/tmp';
    l_file_name := 'TEST_XREF4.xml';

    FOR xml_rec IN xml_cur
    LOOP

    dbms_xslprocessor.clob2file(xml_rec.XMLDATA, l_file_path, l_file_name, nls_charset_id('UTF8') );

    END LOOP;

    END;

    Please see the above code snippet and help us if we are doing something wrong.

    Thank you
  • 5. Re: How to write xml data to a file
    557444 Newbie
    Currently Being Moderated
    Sorry change in the query


    SELECT xmltype.getClobVal(XMLELEMENT
    ("ROW",
    XMLELEMENT ("CELL",
    xmlattributes ('EBIZCZMDL_01' AS "colname"),
    inventory_item_id
    ),
    XMLELEMENT ("CELL",
    xmlattributes ('EBIZFFMT_01' AS "COLNAME"),
    attribute29
    ),
    XMLELEMENT ("CELL",
    xmlattributes ('COMMON' AS "COLNAME"),
    inventory_item_id || '' || attribute29 || 'ITM'
    ) AS "RESULT"
    )) "XMLDATA"
    FROM apps.mtl_system_items_b
    WHERE attribute29 IS NOT NULL;
  • 6. Re: How to write xml data to a file
    odie_63 Guru
    Currently Being Moderated
    The call to dbms_xslprocessor.clob2file is within the cursor loop for the given SQL.
    That's obviously the problem.
    The file gets overwritten at every iteration.

    Do not use a cursor at all.

    See example below, it should be close to your requirement :
    DECLARE
    
       l_file_name      VARCHAR2 (30);
       l_file_path      VARCHAR2 (200);
    
       l_xmldoc         CLOB;
    
    
    BEGIN
     
       l_file_path := '/usr/tmp';
       l_file_name := 'TEST_XREF4.xml';
    
       SELECT XMLElement("xref", xmlattributes('http://xmlns.oracle.com/xref' as "xmlns"), 
                XMLElement("table",
                  XMLElement("columns",
                    XMLElement("column", xmlattributes('EBIZFFMT_01' as "name"))
                  , XMLElement("column", xmlattributes('COMMON' as "name"))
                  , XMLElement("column", xmlattributes('EBIZQOT_01' as "name"))
                  , XMLElement("column", xmlattributes('EBIZCZMDL_01' as "name"))
                  , XMLElement("column", xmlattributes('EBIZCZGOLD_01' as "name"))
                  ),
                  XMLElement("rows",
                    XMLAgg(
                      XMLElement("row",
                        XMLElement("cell", xmlattributes('EBIZCZMDL_01' AS "colName"), inventory_item_id)
                      , XMLElement("cell", xmlattributes('EBIZFFMT_01' AS "colName"), attribute29)
                      , XMLElement("cell", xmlattributes('COMMON' AS "colName"), inventory_item_id || '' || attribute29 || 'ITM') 
                      )
                    )
                  )
                )
              ).getClobVal()
       INTO l_xmldoc
       FROM apps.mtl_system_items_b
       WHERE attribute29 IS NOT NULL
       ;
    
       dbms_xslprocessor.clob2file(l_xmldoc, l_file_path, l_file_name, nls_charset_id('UTF8'));
    
    END;
    /
    BTW, the directory parameter in DBMS_XSLPROCESSOR.CLOB2FILE should be an Oracle directory object, not a literal path.
  • 7. Re: How to write xml data to a file
    557444 Newbie
    Currently Being Moderated
    Thanks a lot Odie.
    This should be fine.
    Will keep you posted

    thnks,
    Sree
  • 8. Re: How to write xml data to a file
    942747 Newbie
    Currently Being Moderated
    Thanks a lot Odie. This helped me a lot.

Legend

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