Oracle XML DB - An Example

Oracle XML DB Oracle XML support is provided as XML DB which can be used to store, generate, manipulate, manage and query XML data in the database. This is compliant with the W3C (Worldwide Web consortium). 

The main components of the XML DB are XMLType framework and the XML DB Repository. XMLType is an Oracle defined datatype for storing XML data and the 

XML Repository contains XML content in a directory like hierarchy. 

It is a light weight repository where you can organise and manage database content, including XML content using a file/folder/URL metaphor. 

Some other key components of XML DB are an XML parser, XML oriented SQL functions to support XML, DOM support XML schema etc. 

Some key features and uses of XMLType are 

• XMLType may be used to represent a document or document fragment in SQL. 
• It has built in member functions to operate on XML content. 
• XMLType may be used in PLSQL as variables, return values and parameters. 
• XMLType APIs are provided for both PLSQL and JAVA programming. 

Beginning with Oracle 9i on XML Type is also supported on the client via FTP, HTTP and Webdav.

Below is a brief example of passing employee data in an oracle collection as an input parameter to a procedure which will convert this data rowwise into XML format and insert into the database as an XML TYPE datatype. 

Create the table to store employee data in XML format.

CREATE TABLE EMP_XML_DATA
(
  employee_id            NUMBER,
  emp_details_xml        XMLTYPE,
  insert_date            DATE,
  insert_user_id         NUMBER  
) TABLESPACE USERS;

Create a table to store Employee data in normal format.

CREATE TABLE HR.EMP_MASTER 
   (	EMPLOYEE_ID NUMBER, 
	EMPLOYEE_NAME VARCHAR2(100 BYTE), 
	EMPLOYEE_DOB DATE, 
	EMPLOYEE_DESIG VARCHAR2(10 BYTE), 
	EMPLOYEE_DOJ DATE, 
	GENDER VARCHAR2(1 BYTE), 
	DEPARTMENT_ID NUMBER
   ) 
TABLESPACE USERS ;

Insert test data into the EMP_MASTER table

Insert into EXPORT_TABLE (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_DOB,EMPLOYEE_DESIG,EMPLOYEE_DOJ,GENDER,DEPARTMENT_ID) values (10455,'Stephen King10455',to_date('21-JUL-62','DD-MON-RR'),'CEO',to_date('21-JUL-91','DD-MON-RR'),'M',10);
Insert into EXPORT_TABLE (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_DOB,EMPLOYEE_DESIG,EMPLOYEE_DOJ,GENDER,DEPARTMENT_ID) values (10456,'Stephen King10456',to_date('21-JUL-62','DD-MON-RR'),'CEO',to_date('21-JUL-91','DD-MON-RR'),'M',10);
Insert into EXPORT_TABLE (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_DOB,EMPLOYEE_DESIG,EMPLOYEE_DOJ,GENDER,DEPARTMENT_ID) values (10457,'Stephen King10457',to_date('21-JUL-62','DD-MON-RR'),'CEO',to_date('21-JUL-91','DD-MON-RR'),'M',10);
Insert into EXPORT_TABLE (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_DOB,EMPLOYEE_DESIG,EMPLOYEE_DOJ,GENDER,DEPARTMENT_ID) values (10458,'Stephen King10458',to_date('21-JUL-62','DD-MON-RR'),'CEO',to_date('21-JUL-91','DD-MON-RR'),'M',10);
Insert into EXPORT_TABLE (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_DOB,EMPLOYEE_DESIG,EMPLOYEE_DOJ,GENDER,DEPARTMENT_ID) values (10459,'Stephen King10459',to_date('21-JUL-62','DD-MON-RR'),'CEO',to_date('21-JUL-91','DD-MON-RR'),'M',10);
Insert into EXPORT_TABLE (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_DOB,EMPLOYEE_DESIG,EMPLOYEE_DOJ,GENDER,DEPARTMENT_ID) values (10460,'Stephen King10460',to_date('21-JUL-62','DD-MON-RR'),'CEO',to_date('21-JUL-91','DD-MON-RR'),'M',10);
Insert into EXPORT_TABLE (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_DOB,EMPLOYEE_DESIG,EMPLOYEE_DOJ,GENDER,DEPARTMENT_ID) values (10461,'Stephen King10461',to_date('21-JUL-62','DD-MON-RR'),'CEO',to_date('21-JUL-91','DD-MON-RR'),'M',10);
Insert into EXPORT_TABLE (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_DOB,EMPLOYEE_DESIG,EMPLOYEE_DOJ,GENDER,DEPARTMENT_ID) values (10462,'Stephen King10462',to_date('21-JUL-62','DD-MON-RR'),'CEO',to_date('21-JUL-91','DD-MON-RR'),'M',10);
Insert into EXPORT_TABLE (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_DOB,EMPLOYEE_DESIG,EMPLOYEE_DOJ,GENDER,DEPARTMENT_ID) values (10463,'Stephen King10463',to_date('21-JUL-62','DD-MON-RR'),'CEO',to_date('21-JUL-91','DD-MON-RR'),'M',10);
Insert into EXPORT_TABLE (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_DOB,EMPLOYEE_DESIG,EMPLOYEE_DOJ,GENDER,DEPARTMENT_ID) values (10464,'Stephen King10464',to_date('21-JUL-62','DD-MON-RR'),'CEO',to_date('21-JUL-91','DD-MON-RR'),'M',10);

Create an oracle object type variable to hold employee data.

CREATE OR REPLACE TYPE TYP_EMP_OBJ IS OBJECT
(
    employee_id      NUMBER,
    employee_name    VARCHAR2(100),
    date_of_birth    DATE,
    gender           VARCHAR2(1),
    department_id    NUMBER,
    designation      VARCHAR2(100)	
);

Create an employee detail array to hold multiple records.

CREATE OR REPLACE TYPE TYP_EMP_TBL IS TABLE OF TYP_EMP_OBJ;

Create a procedure which will take the employee array as input and for each row, convert this data into XML format and store it in XMLTYPE datatype in EMP_XML_DATA for each employee id.

create or replace PROCEDURE PR_INSERT_EMP_XML_DETAILS( p_emp_details_arr           TYP_EMP_TBL,
                                                         p_error_code       OUT      NUMBER
)
AS 

v_employee_name    VARCHAR2(100);
v_emp_arr                TYP_EMP_TBL := TYP_EMP_TBL(TYP_EMP_OBJ(NULL,NULL,NULL,NULL,NULL,NULL));

v_emp_details    XMLTYPE;
v_emp_str_details    VARCHAR2(4000);


BEGIN

  FOR i IN p_emp_details_arr.FIRST .. p_emp_details_arr.LAST
  LOOP --{
    
            SELECT xmlelement("employee",
                          xmlelement("emp", xmlattributes(employee_id as "EmployeeId", employee_name as "Employee Name")),
                          xmlelement("Date of Birth",date_of_birth),
                          xmlelement("Gender",gender),
                          xmlelement("Department Id",department_id),
                          xmlelement("Designation",designation)
                          )
           INTO v_emp_details                          
           FROM TABLE(p_emp_details_arr)
           WHERE employee_id = p_emp_details_arr(i).employee_id;
           
          INSERT INTO EMP_XML_DATA
          (
            EMPLOYEE_ID,
            EMP_DETAILS_XML,
            INSERT_DATE,
            INSERT_USER_ID
          )
          VALUES
          (
              p_emp_details_arr(i).employee_id,
              v_emp_details, 
              SYSDATE,
              1001
          );

          dbms_output.put_line(v_emp_details.getStringVal());
  
        END LOOP; --}
        COMMIT;
        
END PR_INSERT_EMP_XML_DETAILS;

Create a PLSQL block which will take employee data in a collection and pass it to PR_INSERT_EMP_XML_DETAILS to store employee data in XML format.

DECLARE

p_emp_details_arr          TYP_EMP_TBL;
v_error_code                      NUMBER;

BEGIN

   SELECT
          CAST(MULTISET(
            SELECT
                employee_id,
                employee_name,
                employee_dob,
                GENDER,
                DEPARTMENT_ID,
                employee_desig
             FROM EMP_MASTER
             WHERE ROWNUM < 11
            )
            as TYP_EMP_TBL)
            INTO p_emp_details_arr
            FROM DUAL; 
    
    PR_INSERT_EMP_XML_DETAILS( p_emp_details_arr, v_error_code);
    
END;

select the xml formatted data from EMP_XML_DATA for any employeed id

SELECT EMP_DETAILS_XML FROM EMP_XML_DATA WHERE EMPLOYEE_ID = 10455;

<employee><emp EmployeeId="10455" Employee Name="Stephen King10455"></emp><Date of Birth>1962-07-21</Date of Birth><Gender>M</Gender><Department Id>10</Department Id><Designation>CEO</Designation></employee>