Introduction to Oracle XML operations in various scenarios

Introduction to Oracle XML operations in various scenarios

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
Min value 10000
Max value 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;

Oracle parses XML data

Parsing XML strings using stored procedures in Oracle

How Does Oracle EBS use commands to upload XML/BI Publisher data definition files and template files?

Oracle Jdeveloper uploads XML files to Dynamic Data Tables

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 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
Sets LONG 5000
Spool c: \ a. xml
SELECT XMLElement ("DEPARTMENT"
, XMLAttributes (department_id as "ID"
, Department_name as "NAME"
)
, XMLElement ("EMPLOYEES"
, (SELECT XMLElement ("EMPLOYEE"
, XMLForest (employee_id as "ID"
, First_name | ''| last_name as" NAME"
)
)
)
FROM employees emp
WHERE emp. department_id = dept. department_id
)
)
)
FROM orders ments dept
WHERE department_id = 10;
Spool off

For more details, please continue to read the highlights on the next page:

1 2 3 4 Next Page