XML file creation using xmldom in Oracle package and its disadvantages

Oracle has a package named xmldom, which allows you to easily create an XML file. A simple example is provided to generate test. xml. The content is as follows:

<Staff content = "Name and ID">

<Member>

<Name> Arwen </Name>

<Eno> 123 </Eno>

</Member>

<Member>

<Name> Tom </Name>

<Eno> 456 </Eno>

</Member>

</Staff>

Certificate ---------------------------------------------------------------------------------------------------------------------------------------

-- Generate xml code

Declare
Doc xmldom. domdocument;
Doc_node xmldom. domnode;
Root_node xmldom. domnode;
User_node xmldom. domnode;
Item_node xmldom. domnode;

Root_elmt xmldom. domelement;
User_elmt xmldom. domelement;
Item_elmt xmldom. domelement;

Item_text xmldom. domtext;

Begin
DOC: = xmldom. newdomdocument;
Xmldom. setversion (Doc, '1. 0 ');
Xmldom. setcharset (Doc, 'utf-8 ');
-- Root Node
Doc_node: = xmldom. makenode (DOC );
Root_elmt: = xmldom. createelement (Doc, 'staff ');
Xmldom. setattribute (root_elmt, 'content', 'name and id ');
Root_node: = xmldom. appendchild (doc_node, xmldom. makenode (root_elmt ));

-- Node 1
User_elmt: = xmldom. createelement (Doc, 'member ');
User_node: = xmldom. appendchild (root_node, xmldom. makenode (user_elmt ));

Item_elmt: = xmldom. createelement (Doc, 'name ');
Item_node: = xmldom. appendchild (user_node, xmldom. makenode (item_elmt ));
Item_text: = xmldom. createtextnode (Doc, 'arwen ');
Item_node: = xmldom. appendchild (item_node, xmldom. makenode (item_text ));

Item_elmt: = xmldom. createelement (Doc, 'eno ');
Item_node: = xmldom. appendchild (user_node, xmldom. makenode (item_elmt ));
Item_text: = xmldom. createtextnode (Doc, '20140901 ');
Item_node: = xmldom. appendchild (item_node, xmldom. makenode (item_text ));

-- Node 2
User_elmt: = xmldom. createelement (Doc, 'member ');
User_node: = xmldom. appendchild (root_node, xmldom. makenode (user_elmt ));

Item_elmt: = xmldom. createelement (Doc, 'name ');
Item_node: = xmldom. appendchild (user_node, xmldom. makenode (item_elmt ));
Item_text: = xmldom. createtextnode (Doc, 'Tom ');
Item_node: = xmldom. appendchild (item_node, xmldom. makenode (item_text ));

Item_elmt: = xmldom. createelement (Doc, 'eno ');
Item_node: = xmldom. appendchild (user_node, xmldom. makenode (item_elmt ));
Item_text: = xmldom. createtextnode (Doc, '20140901 ');
Item_node: = xmldom. appendchild (item_node, xmldom. makenode (item_text ));

-- Write to the Operating System File
Xmldom. writetofile (Doc, 'dir' | '\ test. xml'); -- note that you must first create a file directory dir
-- Statement: Create or replace directory dir as 'd: \ Temp'

Xmldom. freedocument (DOC );

End;

 

In this way, it is very convenient to generate xml files and can meet the needs of general applications. however, xmldom has the disadvantage of generating all XML file content in the memory at a time and then writing it to the disk file. if the XML file is too large, for example, there is a table with several GB and you want to save it as an XML file. this may result in insufficient memory and file generation failure. what should I do?

 

1. You may first think of using utl_file to generate a file.

All the content is manually written in XML format, and then saved as a file with the XML suffix. this is indeed feasible. however, if the content of some nodes contains five reserved characters in XML, (&, <,>, ', "are respectively the sum, smaller than, greater than, and single quotation marks, double quotation marks ("double quotation marks"). If we open an XML file in text, we will not see the reserved words in the node content, they will all be converted to the corresponding & amp, & gt, & lt, & apos, & quot. the node specifies the preceding Arwen or 123. If there is a name (A & R <W> e'n ") save it to the XML file and change it to (A & ampr & gtw & LTE & aposn & quot ). if xmldom is used, conversion is performed by default. if you use utl_file, you must manually write the code for conversion.

 

2. Combined use of xmldom and utl_file

Suppose there is a table staff (name varchar2 (30), Eno integer), which contains several GB of content and needs to be converted into an XML file in the format described at the beginning.

-- Generate xml code where the header and tail of the XML file are directly written into the file using utl_file, the node content is generated using xmldom, and then written to the clob variable, write the clob variable value to the XML file using utl_file.

 

Declare

 

Staffinfo utl_file.file_type;

V_temp clob;

Cursor c_table_info is

Select name, Eno from staff;

V_name varchar2 (30 );

V_eno integer;

Doc xmldom. domdocument;
Doc_node xmldom. domnode;
Root_node xmldom. domnode;
User_node xmldom. domnode;
Item_node xmldom. domnode;

Root_elmt xmldom. domelement;
User_elmt xmldom. domelement;
Item_elmt xmldom. domelement;

Item_text xmldom. domtext;

Begin

-- Xml header
Staffinfo: = utl_file.fopen_nchar ('dir', 'test. xml', 'w', 32767); -- As mentioned earlier, you must first create a directory.

Utl_file.put_line_nchar (staffinfo
, '<Staff content = "Name and ID"> ');

Utl_file.fflush (staffinfo); -- write directly to the disk file without staying in the memory.
Utl_file.fclose (staffinfo );

Open c_table_info;

Loop

Fetch c_table_info into v_name, v_eno;

Exit when c_table_info % notfound;

-- XML Node

DOC: = xmldom. newdomdocument;
Xmldom. setcharset (Doc, 'utf-8 ');

Doc_node: = xmldom. makenode (DOC );

User_elmt: = xmldom. createelement (Doc, 'member ');
User_node: = xmldom. appendchild (doc_node, xmldom. makenode (user_elmt ));

Item_elmt: = xmldom. createelement (Doc, 'name ');
Item_node: = xmldom. appendchild (user_node, xmldom. makenode (item_elmt ));
Item_text: = xmldom. createtextnode (Doc, v_name );
Item_node: = xmldom. appendchild (item_node, xmldom. makenode (item_text ));

Item_elmt: = xmldom. createelement (Doc, 'eno ');
Item_node: = xmldom. appendchild (user_node, xmldom. makenode (item_elmt ));
Item_text: = xmldom. createtextnode (Doc, 'eno ');
Item_node: = xmldom. appendchild (item_node, xmldom. makenode (item_text ));

V_temp: = '';
-- Write to the Temporary Variable v_temp
Xmldom. writetoclob (Doc, v_temp );

Staffinfo: = utl_file.fopen_nchar ('dir', 'test. xml', 'A', 32767)

Utl_file.put_line_nchar (staffinfo
, V_temp );

Utl_file.fflush (staffinfo );

Utl_file.fclose (staffinfo );

Xmldom. freedocument (DOC );

End loop;

Close c_table_info;

 

-- XML tail

Staffinfo: = utl_file.fopen_nchar ('dir', 'test. xml', 'a, 32767 );

Utl_file.put_line_nchar (staffinfo
, '</Staff> ');

Utl_file.fflush (staffinfo );

Utl_file.fclose (staffinfo );

 

End;