How Oracle operates XML

A recent series of operations on XML in Oracle Plsql. Combine the knowledge and reference materials used in the work to organize the following related content:

How to generate an XML file:

1, using Dbms_xmlquery and Utl_file built-in package (Scott user execution)

CREATE OR REPLACE DIRECTORY xml_dir as ' d:\app\xml ';  
      
DROP SEQUENCE Seq_filename;  
CREATE SEQUENCE seq_filename   
    minvalue 10000  
    MAXVALUE 99999 by   
    1 START with   
    INCREMENT   
    10000 ;

DECLARE v_filename Varchar2 (m): = ' Empmsg ' | | To_char (seq_filename.nextval) | |  
    XML ';  
    Xml_str Clob;  
    Xml_file Utl_file.file_type;  
    Offset number;  
    Buffer varchar2 (32767);  
Buffer_size number;  
    BEGIN offset: = 1;  
    Buffer_size: = 3000;  
    Xml_file: = Utl_file.fopen (' Xml_dir ', V_filename, ' W ');  
                                             XML_STR: = Dbms_xmlquery.getxml (' Select Empno, ename,  
                                             Job, Mgr, HireDate, Sal, Comm.  
        
    , Deptno from EMP ");  
      while (Offset < dbms_lob.getlength (XML_STR)) loop buffer: = Dbms_lob.substr (Xml_str, buffer_size, offset); Utl_file.put (Xml_file, buffer);  
      Utl_file.fflush (Xml_file);  
    Offset: = offset + buffer_size;  
          
    End Loop;  
Utl_file.fclose (Xml_file); End;

2, using the XmlElement series built-in functions to return XML (SYS user execution)

DECLARE v_filename Varchar2 (m): = ' Empmsg ' | | To_char (scott.seq_filename.nextval) | |  
    XML ';  
    Xml_str Clob;  
    Xml_file Utl_file.file_type;  
    Offset number;  
    Buffer varchar2 (32767);  
Buffer_size number;  
    BEGIN offset: = 1;  
    Buffer_size: = 3000;  
    Xml_file: = Utl_file.fopen (' Xml_dir ', V_filename, ' W '); SELECT XmlElement ("DEPARTMENT", XmlAttributes (department_id as "id", D
                             Epartment_name as "name"), XmlElement ("EMPLOYEES" , (select XMLAgg (XmlElement ("EMPLOYEE", Xmlforest (EMP LOYEE_ID as "id", first_name| | ' '||  
                                                         Last_Name as "name")  
       )                                      ) from Hr.employees EMP  
                              WHERE emp.department_id = dept.department_id)  
        
    ). Getclobval () into the xml_str from hr.departments dept WHERE department_id = 20; while (Offset < dbms_lob.getlength (XML_STR)) loop buffer: = Dbms_lob.substr (Xml_str, buffer_size, offset  
      );  
      Utl_file.put (xml_file, buffer);  
      Utl_file.fflush (Xml_file);  
    Offset: = offset + buffer_size;  
          
    End Loop;  
Utl_file.fclose (Xml_file); End;

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/