8 Replies Latest reply: Jun 10, 2012 5:36 PM by 942747 RSS

    How to write xml data to a file

    557444
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        Thanks a lot Odie. This helped me a lot.