XMLElement, XMLAttributes, XMLAgg, XMLForest - Examples

 In the last few post I have given many examples of on XMLDB and the power and flexibility of this Oracle utility to manipulate and format data as per you requirement.

In this post I will be putting most of the attributes together and showing some useful examples of using these attributes in the queries below.

SELECT XMLElement(
         "Department",
         XMLAttributes(d.Department_id AS "DepartmentId"),
         XMLForest(d.department_name AS "Name"),
         XMLElement(
           "Location",
           XMLForest(street_address AS "Address",
                     city AS "City",
                     state_province AS "State",
                     postal_code AS "Zip",
                     country_name AS "Country")),
           XMLElement(
             "EmployeeList",
             (SELECT XMLAgg(
                       XMLElement(
                         "Employee",
                         XMLAttributes(e.employee_id AS "employeeNumber"),
                         XMLForest(
                           e.first_name AS "FirstName", 
                           e.last_name AS "LastName",
                           e.email AS "EmailAddress",
                           e.phone_number AS "PHONE_NUMBER",
                           e.hire_date AS "StartDate",
                           j.job_title AS "JobTitle",
                           e.salary AS "Salary",
                           m.first_name || ' ' || m.last_name AS "Manager"),
                         XMLElement("Commission", e.commission_pct)))
                FROM hr.employees e, hr.employees m, hr.jobs j
                WHERE e.department_id = d.department_id
                  AND j.job_id = e.job_id
                  AND m.employee_id = e.manager_id)))
  AS XML
  FROM hr.departments d, hr.countries c, hr.locations l
  WHERE department_name = 'Executive'
    AND d.location_id = l.location_id
    AND l.country_id  = c.country_id;

The output of the query is as below

<Department DepartmentId="90"><Name>Executive</Name><Location><Address>2004 Charade Rd</Address><City>Seattle</City><State>Washington</State><Zip>98199</Zip><Country>United States of America</Country></Location><EmployeeList>
<Employee employeeNumber="101"><FirstName>Neena</FirstName><LastName>Kochhar</LastName><EmailAddress>NKOCHHAR</EmailAddress><PHONE_NUMBER>515.123.4568</PHONE_NUMBER><StartDate>2005-09-21</StartDate><JobTitle>Administration Vice President</JobTitle><Salary>17000</Salary><Manager>Steven King</Manager><Commission></Commission></Employee>
<Employee employeeNumber="102"><FirstName>Lex</FirstName><LastName>De Haan</LastName><EmailAddress>LDEHAAN</EmailAddress><PHONE_NUMBER>515.123.4569</PHONE_NUMBER><StartDate>2001-01-13</StartDate><JobTitle>Administration Vice President</JobTitle><Salary>17000</Salary><Manager>Steven King</Manager><Commission></Commission></Employee>
</EmployeeList></Department>

An Example to extract elements from an XMLType variable

DECLARE
  x XMLType := XMLType(
    '<?xml version="1.0" ?> 
<person>
   <row>
       <name>Tom</name>
       <Address>
           <State>California</State>
           <City>Los angeles</City>
       </Address>
   </row>
   <row>
       <name>Jim</name>
       <Address>
           <State>California</State>
           <City>Los angeles</City>
       </Address>
   </row>
</person>');
BEGIN
  FOR r IN (
    SELECT ExtractValue(Value(p),'/row/name/text()') as name
          ,ExtractValue(Value(p),'/row/Address/State/text()') as state
          ,ExtractValue(Value(p),'/row/Address/City/text()') as city
    FROM   TABLE(XMLSequence(Extract(x,'/person/row'))) p
    ) LOOP
    -- do whatever you want with r.name, r.state, r.city
  END LOOP;
END;  

An example to extract and show XML variables in tabular fomat

SELECT *  
FROM XMLTABLE('/person/row'  
         PASSING   
            xmltype('
                <person>
                   <row>
                       <name>Tom</name>
                       <Address>
                           <State>California</State>
                           <City>Los angeles</City>
                       </Address>
                   </row>
                   <row>
                       <name>Jim</name>
                       <Address>
                           <State>California</State>
                           <City>Los angeles</City>
                       </Address>
                   </row>
                </person>
            ')
         COLUMNS  
            --describe columns and path to them:  
            name  varchar2(20)    PATH './name',  
            state varchar2(20)    PATH './Address/State',  
            city  varchar2(20)    PATH './Address/City'
     ) xmlt  
;

NAME                  STATE                   CITY 

Tom                     California             Los angeles 

Jim                      California             Los angeles

Below is an example of taking an XML type as CLOB input parameter, extract the elements and storing into a normal employee details table

CREATE OR REPLACE PROCEDURE ADDEMP
    (xml IN CLOB)
AS
BEGIN
    INSERT INTO EMPLOYEE (EMPID,EMPNAME,EMPDETAIL,CREATEDBY,CREATED)
    SELECT 
        ExtractValue(column_value,'/ROOT/EMPID') AS EMPID
       ,ExtractValue(column_value,'/ROOT/EMPNAME') AS EMPNAME
       ,ExtractValue(column_value,'/ROOT/EMPDETAIL') AS EMPDETAIL
       ,ExtractValue(column_value,'/ROOT/CREATEDBY') AS CREATEDBY
       ,ExtractValue(column_value,'/ROOT/CREATEDDATE') AS CREATEDDATE
    FROM   TABLE(XMLSequence( XMLType(xml))) XMLDUMMAY;

    COMMIT;
END; 

References