XML : Incorrect format in XML output file and I'm not sure why

Below is a simple example of getting data from the Oracle 11g database (I'm using the dual table in this simple example) into a flat file.

I'm expecting the below format in the file.

  <message>    <production>      <prodCategoryType>test type 1</prodCategoryType>      <prodStatusType>prod status 1</prodStatusType>    </production>  </message>  <message>   <production>    <prodCategoryType>test type 2</prodCategoryType>    <prodStatusType>prod status 2</prodStatusType>   </production>  </message>    

but I'm getting the below instead, which seems to be missing start message and production tag and putting a strange on> in the middle.

  <message>     <production>       <prodCategoryType>test type 1</prodCategoryType>       <prodStatusType>prod status 1</prodStatusType>     </production>  </message>  on>       <prodCategoryType>test type 2</prodCategoryType>       <prodStatusType>prod status 2</prodStatusType>    </production>  </message>    

Am I doing something wrong???

  DECLARE     l_file    UTL_FILE.FILE_TYPE;   l_clob    CLOB;   l_buffer  VARCHAR2(32767);   l_amount  BINARY_INTEGER := 32767;   l_pos     INTEGER := 1;   l_extract_dir               CONSTANT dba_directories.directory_name%TYPE:= 'REPORTS_OUT_DIR';   -- \\data2\data\download\d7prdv1\prsrepreports         l_xmltype XMLTYPE;   l_domdoc dbms_xmldom.DOMDocument;   l_root_node dbms_xmldom.DOMNode;          l_message_node            dbms_xmldom.DOMNode;     l_production_element    dbms_xmldom.DOMElement;   l_production_node       dbms_xmldom.DOMNode;    -- production XML elements, node, text   l_prod_element                   dbms_xmldom.DOMElement;        l_prod_node         dbms_xmldom.DOMNode;   l_prod_t_node          dbms_xmldom.DOMNode;   l_prod_text          dbms_xmldom.DOMText;   -- production XML elements, node, text, node     CURSOR c_production   IS SELECT 'test type 1',            as prodCategoryType             'prod status 1'          as prodStatusType      from dual                  UNION      SELECT 'test type 2',            as prodCategoryType            'prod status 2'          as prodStatusType      from dual;       BEGIN    UTL_FILE.FCLOSE_ALL; -- make sure all file handles are closed for session   l_file := UTL_FILE.fopen(l_extract_dir , 'Sample2.dat', 'w', 32767);     -- Create an empty XML document    l_domdoc := dbms_xmldom.newDomDocument;     -- Create a root node   l_root_node := dbms_xmldom.makeNode(l_domdoc);     -- Create a message root node   l_message_node := dbms_xmldom.appendChild( l_root_node, dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'message' ))                                       );         FOR production_rec in c_production LOOP       l_production_element := dbms_xmldom.createElement(l_domdoc, 'production' );     l_production_node    := dbms_xmldom.appendChild(l_message_node,dbms_xmldom.makeNode(l_production_element));          -- prodCategoryType       l_prod_element := dbms_xmldom.createElement(l_domdoc, 'prodCategoryType' );       l_prod_node   := dbms_xmldom.appendChild(l_production_node,dbms_xmldom.makeNode(l_prod_element));       l_prod_text    := dbms_xmldom.createTextNode(l_domdoc, production_rec.prodCategoryType );       l_prod_t_node   := dbms_xmldom.appendChild(l_prod_node,dbms_xmldom.makeNode(l_prod_text));        -- prodStatusType       l_prod_element := dbms_xmldom.createElement(l_domdoc, 'prodStatusType');       l_prod_node    := dbms_xmldom.appendChild(l_production_node,dbms_xmldom.makeNode(l_prod_element));       l_prod_text    := dbms_xmldom.createTextNode(l_domdoc, production_rec.prodStatusType);       l_prod_t_node   := dbms_xmldom.appendChild(l_prod_node,dbms_xmldom.makeNode(l_prod_text));              l_xmltype   := dbms_xmldom.getXmlType(l_domdoc);          l_clob      := l_xmltype.getClobVal;             DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer);          UTL_FILE.put(l_file, l_buffer);          l_pos := l_pos + l_amount;      END LOOP;     dbms_xmldom.freeDocument(l_domdoc);   UTL_FILE.fclose(l_file);    END;    

No comments:

Post a Comment