Oracle generates XML files

Recently, I have been studying Oracle PLSQL's series of XML operations. Combine the knowledge and references used in the work to sort out the following content:

1. How to generate an XML file:1. Use dbms_xmlquery and utl_file built-in packages (executed by scott)

CREATE OR REPLACE DIRECTORY xml_dir AS 'd:\app\xml';DROP SEQUENCE seq_filename;CREATE SEQUENCE seq_filename     MINVALUE 10000    MAXVALUE 99999     INCREMENT BY 1     START WITH 10000     NOCYCLE;

DECLARE    v_filename  Varchar2(50)  := '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. Use XMLELEMENT built-in functions to return xml (executed by sys Users)

DECLARE    v_filename  Varchar2(50)  := '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"                                , department_name as "NAME"                                )                 , XMLElement("EMPLOYEES"                             , (SELECT XMLAgg( XMLElement("EMPLOYEE"                                                         , XMLForest(employee_id as "ID"                                                                    ,first_name||' '||last_name as "NAME"                                                                    )                                                         )                                             )                                 FROM hr.employees emp                                WHERE emp.department_id = dept.department_id                                )                              )                ).getclobval() INTO 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;

-- XMLElement: a function that converts a relational value to an XML Element in the format of <elementName> value </elementName>
-- XMLAttributes: The function used to set attributes in the XML Element returned by the SQL query.
-- XMLForest: This function returns a set of one or more sub-elements. This function uses the column name as the name of the XML Element and uses the SQL value expression as the content of the XML element, however, the attribute of an element cannot be specified during use.
-- Xmlgroups: A function used to GROUP or aggregate XML data in a group by query.

PS: Use SPOOL to export files:

Set trimspool on set termout on set feedback off set verify off set echo off set pagesize 999 set head off set heading off set long 5000 spool c: \. xmlSELECT XMLElement ("DEPARTMENT", XMLAttributes (department_id as "ID", department_name as "NAME"), XMLElement ("EMPLOYEES", (SELECT xmltransform (XMLElement ("EMPLOYEE ", XMLForest (employee_id as "ID", first_name | ''| last_name as" NAME ") FROM employees emp WHERE emp. department_id = dept. department_id) a FROM departments dept WHERE department_id = 10; spool off 2. How to store XML file content: 3. How to parse XML content: 4. How to Use XMLTABLE: to be continue... ---------------------------------- By Dylan.