XML输出文件中的格式不正确，我不知道为什么(Incorrect format in XML output file and I'm not sure why)

XML输出文件中的格式不正确，我不知道为什么(Incorrect format in XML output file and I'm not sure why)

下面是一个从Oracle 11g数据库（我在这个简单的例子中使用双表）中获取数据到一个平面文件的简单示例。

我期待文件中的格式如下。

<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>

但是我得到了以下内容，这似乎是缺少开始消息和生产标签，并在中间放置一个奇怪的。

<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>

难道我做错了什么？？？

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;

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;