15 Generating XML Data from the Database

Example 15-1 XMLElement(): Generating an Element for Each Employee

The following example produces an Emp XML element for each employee, with the employee's name as its content:

SELECT e.employee_id, XMLELEMENT ("Emp", e.fname ||' '|| e.lname) AS "result"
   FROM employees e
   WHERE employee_id > 200;

-- This query produces the following typical result:
-- EMPLOYEE_ID    result
-- ---------------------
--        1001    <Emp>John Smith</Emp>
--        1206    <Emp>Mary Martin</Emp>

2 rows selected

XMLElement() can also be nested to produce XML data with a nested structure.

Example 15-2 XMLElement(): Generating Nested XML

To produce an Emp element for each employee, with elements that provide the employee's name and start date, do the following:

SELECT XMLELEMENT("Emp", XMLELEMENT("name", e.fname ||' '|| e.lname),
                           XMLELEMENT ( "hiredate", e.hire)) AS "result" 
FROM employees e 
WHERE employee_id > 200 ;

This query produces the following typical XML result:

result
-----------------
<Emp> 
  <name>John Smith</name>
  <hiredate>24-MAY-00</hiredate>
</Emp>
<Emp> 
  <name>Mary Martin</name>
  <hiredate>01-FEB-96</hiredate>
</Emp>

2 rows selected

If NLS_DATE_FORMAT is set to YYYY-MM-DD, then the date is in XML schema date format. The same query then produces this result:

result
-----------------
<Emp> 
  <name>John Smith</name>
  <hiredate>2000-05-24</hiredate>
</Emp>
<Emp> 
  <name>Mary Martin</name>
  <hiredate>1996-02-01</hiredate>
</Emp>

2 rows selected

Example 15-3 XMLElement(): Generating an Element for Each Employee with ID and Name Attribute

This example produces an Emp element for each employee, with an id and name attribute:

SELECT XMLELEMENT ("Emp", XMLATTRIBUTES (
                             e.employee_id as "ID",
                             e.fname ||' ' || e.lname AS "name"))
    AS "result"
    FROM employees e
    WHERE employee_id > 200;

This query produces the following typical XML result fragment:

result
--------------
<Emp ID="1001" name="John Smith"/>
<Emp ID="1206" name="Mary Martin"/>

If the name of the element or attribute is being created from the ALIAS specified in the AS clause, then partially escaped mapping is used. If the name of the element or attribute is being created from a column reference, then fully escaped mapping is used. The following example illustrates these mappings:

SELECT XMLELEMENT ("Emp:Exempt", XMLATTRIBUTES (e.fname,
                                                 e.lname AS "name:last",
                                                 e."name:middle"))  
   AS "result"
   FROM employees e
   WHERE employee_id = 1001;

This query could produce the following XML result:

result
--------------
<Emp:Exempt FNAME="John" name:last="Smith" name_x003A_middle="Quincy"/>
</Emp:Exempt>

1 row selected.

Example 15-4 XMLElement(): Using Namespaces to Create a Schema-Based XML Document

The following example illustrates the use of namespaces to create an XML schema-based document. Assuming that an XML schema "http://www.oracle.com/Employee.xsd" exists and has no target namespace, then the following query creates an XMLType instance conforming to that schema:

SET LONG 2000
SELECT XMLELEMENT ("Employee", XMLATTRIBUTES (
                                  'http://www.w3.org/2001/XMLSchema' AS
                                          "xmlns:xsi",
                                  'http://www.oracle.com/Employee.xsd' AS
                                          "xsi:nonamespaceSchemaLocation"),
                    XMLForest(empno, ename, sal))
   AS "result"
   FROM scott.emp
   WHERE deptno = 10;

This creates an XML document that conforms to the Employee.xsd XMLSchema, result:

result
--------------
<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema"
          xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd">
   <EMPNO>7782</EMPNO>
   <ENAME>CLARK</ENAME>
   <SAL>2450</SAL>
</Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema"
          xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd">
   <EMPNO>7839</EMPNO>
   <ENAME>KING</ENAME>
   <SAL>5000</SAL>
</Employee>

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema"
          xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd">
   <EMPNO>7934</EMPNO>
   <ENAME>MILLER</ENAME>
   <SAL>1300</SAL>
</Employee>

3 rows selected.

Example 15-5 XMLElement(): Generating an Element from a User-Defined Type

Using the same example as given in the following XMLSequence() section for generating one XML document from another, you can generate a hierarchical XML for the employee, department example as follows:

CREATE OR REPLACE TYPE emp_t AS OBJECT ("@EMPNO" NUMBER(4),
                                         ENAME VARCHAR2(10));
/

CREATE OR REPLACE TYPE emplist_t AS TABLE OF emp_t;
/

CREATE OR REPLACE TYPE dept_t AS OBJECT ("@DEPTNO" NUMBER(2),
                                         DNAME VARCHAR2(14),
                                         EMP_LIST EMPLIST_T);
/

SELECT XMLElement("Department",
                  dept_t(deptno, dname,
                         CAST(MULTISET(select empno, ename from scott.emp e
                                       where e.deptno = d.deptno)
                              AS emplist_t)))
AS deptxml
FROM scott.dept d
WHERE d.deptno = 10;

This produces an XML document which contains the Department element and the canonical mapping of the dept_t type.

DEPTXML
-------------
<Department>
  <DEPT_T DEPTNO="10">
    <DNAME>ACCOUNTING</DNAME>
  <EMPLIST>
    <EMP_T EMPNO="7782">
      <ENAME>CLARK</ENAME>
    </EMP_T>
    <EMP_T EMPNO="7839">
      <ENAME>KING</ENAME>
    </EMP_T>
    <EMP_T EMPNO="7934">
      <ENAME>MILLER</ENAME>
    </EMP_T>
  </EMPLIST>
  </DEPT_T>
</Department>

1 row selected.

Example 15-16 DBMS_XMLGEN: Generating Simple XML

This example creates an XML document by selecting out the employee data from an object-relational table and putting the resulting CLOB into a table.

CREATE TABLE temp_clob_tab(result CLOB);

DECLARE
   qryCtx DBMS_XMLGEN.ctxHandle;
   result CLOB;
BEGIN
  qryCtx := dbms_xmlgen.newContext('SELECT * from scott.emp');

  -- set the row header to be EMPLOYEE
  DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE');

  -- now get the result
  result := DBMS_XMLGEN.getXML(qryCtx);

  INSERT INTO temp_clob_tab VALUES(result); 

  --close context
  DBMS_XMLGEN.closeContext(qryCtx);
END;
/

This query example generates the following XML:

SELECT * FROM temp_clob_tab;

RESULT
------------------------------------
<?xml version="1.0"?>
<ROWSET>
  <EMPLOYEE>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>17-DEC-80</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>20</DEPTNO>
  </EMPLOYEE>
  <EMPLOYEE>
    <EMPNO>7499</EMPNO>
    <ENAME>ALLEN</ENAME>
    <JOB>SALESMAN</JOB>
    <MGR>7698</MGR>
    <HIREDATE>20-FEB-81</HIREDATE>
    <SAL>1600</SAL>
    <COMM>300</COMM>
    <DEPTNO>30</DEPTNO>
  </EMPLOYEE>
  ...
</ROWSET>

Example 15-17 DBMS_XMLGEN: Generating Simple XML with Pagination

Instead of generating all the XML for all rows, you can use the fetch interface that DBMS_XMLGEN provides to retrieve a fixed number of rows each time. This speeds up response time and also can help in scaling applications that need a Document Object Model (DOM) Application Program Interface (API) on the resulting XML, particularly if the number of rows is large.

The following example illustrates how to use DBMS_XMLGEN to retrieve results from table scott.emp:

-- create a table to hold the results
CREATE TABLE temp_clob_tab(result clob);

declare
   qryCtx dbms_xmlgen.ctxHandle;
   result CLOB;
begin
  -- get the query context;
  qryCtx := dbms_xmlgen.newContext('select * from scott.emp');
  -- set the maximum number of rows to be 5
  dbms_xmlgen.setMaxRows(qryCtx, 5);
  loop 
    -- get the result
    result := dbms_xmlgen.getXML(qryCtx);
    -- if no rows were processed, then quit
    exit when dbms_xmlgen.getNumRowsProcessed(qryCtx) = 0;

    -- Do some processing with the lob data 
    --   Here, we insert the results into a table.
    --   You can print the lob out, output it to a stream, 
    --   put it in a queue, or do any other processing.
    insert into temp_clob_tab values(result);
  end loop;
  --close context
  dbms_xmlgen.closeContext(qryCtx);
end;
/

Here, for each set of 5 rows, you generate an XML document.

Example 15-18 DBMS_XMLGEN: Generating Complex XML

Complex XML can be generated using object types to represent nested structures:

CREATE TABLE new_departments(department_id   NUMBER PRIMARY KEY,
                             department_name VARCHAR2(20));
CREATE TABLE new_employees(employee_id       NUMBER PRIMARY KEY,
                           last_name         VARCHAR2(20),
                           department_id  NUMBER REFERENCES new_departments);
CREATE TYPE emp_t AS OBJECT("@employee_id" NUMBER,
                            last_name VARCHAR2(20));
/
INSERT INTO new_departments VALUES(10, 'SALES');
INSERT INTO new_departments VALUES(20, 'ACCOUNTING');
INSERT INTO new_employees VALUES(30, 'Scott', 10);
INSERT INTO new_employees VALUES(31, 'Marry', 10);
INSERT INTO new_employees VALUES(40, 'John', 20);
INSERT INTO new_employees VALUES(41, 'Jerry', 20);

COMMIT;
  
CREATE TYPE emplist_t AS TABLE OF emp_t;
/

CREATE TYPE dept_t AS OBJECT("@department_id" NUMBER,
                             department_name VARCHAR2(20),
                             emplist emplist_t);
/
DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  result CLOB;
BEGIN
  DBMS_XMLGEN.setRowTag(qryCtx, NULL);
  qryCtx := DBMS_XMLGEN.newContext
     ('SELECT 
         dept_t(department_id, department_name,
                CAST(MULTISET
                      (SELECT e.employee_id, e.last_name
                       FROM new_employees e
                       WHERE e.department_id = d.department_id)
                     AS emplist_t))
         AS deptxml
         FROM new_departments d');
-- now get the result
result := DBMS_XMLGEN.getXML(qryCtx);
INSERT INTO temp_clob_tab VALUES (result);
-- close context
DBMS_XMLGEN.closeContext(qryCtx);
END;
/
SELECT * FROM temp_clob_tab;

Here is the resulting XML:

RESULT
-----------------------
<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <DEPTXML department_id="10">
      <DEPARTMENT_NAME>SALES</DEPARTMENT_NAME>
      <EMPLIST>
        <EMP_T employee_id="30">
          <LAST_NAME>Scott</LAST_NAME>
        </EMP_T>
        <EMP_T employee_id="31">
          <LAST_NAME>Marry</LAST_NAME>
        </EMP_T>
      </EMPLIST>
    </DEPTXML>
  </ROW>
  <ROW>
    <DEPTXML department_id="20">
      <DEPARTMENT_NAME>ACCOUNTING</DEPARTMENT_NAME>
      <EMPLIST>
        <EMP_T employee_id="40">
          <LAST_NAME>John</LAST_NAME>
        </EMP_T>
        <EMP_T employee_id="41">
          <LAST_NAME>Jerry</LAST_NAME>
        </EMP_T>
      </EMPLIST>
    </DEPTXML>
  </ROW>
</ROWSET>

Now, you can select the LOB data from the temp_clob_Tab table and verify the results. The result looks like the sample result shown in section "Sample DBMS_XMLGEN Query Result".

With relational data, the results are a flat non-nested XML document. To obtain nested XML structures, you can use object-relational data, where the mapping is as follows:

Example 15-19 DBMS_XMLGEN: Generating Complex XML #2 - Inputting User Defined Types For Nested XML Documents

When you enter a user-defined type (UDT) value to DBMS_XMLGEN functions, the user-defined type is mapped to an XML document using canonical mapping. In the canonical mapping, user-defined type attributes are mapped to XML elements. Attributes with names starting with "@" are mapped to attributes of the preceding element.

User-defined types can be used for nesting in the resulting XML document. For example, consider tables, EMP and DEPT:

CREATE TABLE DEPT(deptno number primary key, dname varchar2(20));
CREATE TABLE EMP(empno number primary key, ename varchar2(20), 
                 deptno number references dept);

To generate a hierarchical view of the data, that is, departments with employees in them, you can define suitable object types to create the structure inside the database as follows:

-- empno is defined with '@' in front to indicate that it must 
-- be mapped as an attribute of the enclosing Employee element. 
CREATE TYPE EMP_T AS OBJECT("@empno" number,  -- empno defined as attribute
                             ename varchar2(20));
/
CREATE TYPE EMPLIST_T AS TABLE OF EMP_T;
/
CREATE TYPE DEPT_T AS OBJECT("@deptno" number, dname varchar2(20),
                             emplist emplist_t);
/

-- Department type DEPT_T contains a list of employees.
-- We can now query the employee and department tables and get 
-- the result as an XML document, as follows:
DECLARE
   qryCtx DBMS_XMLGEN.ctxHandle;
   RESULT CLOB;
BEGIN
  -- get query context
  qryCtx := DBMS_XMLGEN.newContext(
 'SELECT
  dept_t(deptno, dname, CAST(MULTISET(SELECT empno, ename
                                      FROM emp e
                                      WHERE e.deptno = d.deptno)
                             AS emplist_t))
  AS deptxml
  FROM dept d');
  -- set maximum number of rows to 5,
  DBMS_XMLGEN.setMaxRows(qryCtx, 5);
  -- set no row tag for this result, since there is a single ADT column
  DBMS_XMLGEN.setRowTag(qryCtx, NULL);
  LOOP 
    -- get result
    result := DBMS_XMLGEN.getXML(qryCtx);
    -- if there were no rows processed, then quit
    EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
    -- do something with the result
    -- ...
  END LOOP;
END;
/

The MULTISET operator treats the result of the subset of employees working in the department, as a list and the CAST around it, assigns it to the appropriate collection type. You then create a department instance around it and call the DBMS_XMLGEN routines to create the XML for the object instance. The result is:

<?xml version="1.0"?>
 <ROWSET>
   <DEPTXML deptno="10">
     <DNAME>Sports</DNAME>
     <EMPLIST>
       <EMP_T empno="200">
         <ENAME>John</ENAME>
       </EMP_T>
       <EMP_T empno="300">
         <ENAME>Jack</ENAME>
       </EMP_T>
     </EMPLIST>
   </DEPTXML>
   <DEPTXML deptno="20">
   <! .. other columns >
   </DEPTXML>
 </ROWSET>

The default name ROW is not present because you set that to NULL. The deptno and empno have become attributes of the enclosing element.

Example 15-20 DBMS_XMLGEN: Generating a Purchase Order from the Database in XML Format

This example uses DBMS_XMLGEN.getXMLType() to generate PurchaseOrder in XML format from a relational database using object views. Note that the example is five pages long.

-- Create relational schema and define Object views
-- Note: DBMS_XMLGEN Package maps UDT attribute names
--       starting with '@' to XML attributes
------------------------------------------------------
-- Purchase Order Object View Model

-- PhoneList Varray object type
CREATE TYPE PhoneList_vartyp AS VARRAY(10) OF VARCHAR2(20)
/
-- Address object type
CREATE TYPE Address_typ AS OBJECT(Street VARCHAR2(200),
                                  City   VARCHAR2(200),
                                  State  CHAR(2),
                                  Zip    VARCHAR2(20))
/
-- Customer object type
CREATE TYPE Customer_typ AS OBJECT(CustNo    NUMBER,
                                   CustName  VARCHAR2(200),
                                   Address   Address_typ,
                                   PhoneList PhoneList_vartyp)
/
-- StockItem object type
CREATE TYPE StockItem_typ AS OBJECT("@StockNo" NUMBER,
                                    Price      NUMBER,
                                    TaxRate    NUMBER)
/
-- LineItems object type
CREATE TYPE LineItem_typ AS OBJECT("@LineItemNo" NUMBER,
                                   Item          StockItem_typ,
                                   Quantity      NUMBER,
                                   Discount      NUMBER)
/
-- LineItems nested table
CREATE TYPE LineItems_ntabtyp AS TABLE OF LineItem_typ 
/
-- Purchase Order object type
CREATE TYPE PO_typ AUTHID CURRENT_USER
    AS OBJECT(PONO            NUMBER,
              Cust_ref        REF Customer_typ,
              OrderDate       DATE,
              ShipDate        TIMESTAMP,
              LineItems_ntab  LineItems_ntabtyp,
              ShipToAddr      Address_typ)
/

-- Create Purchase Order relational model tables

--Customer table
CREATE TABLE Customer_tab(CustNo   NUMBER NOT NULL,
                          CustName VARCHAR2(200),
                          Street   VARCHAR2(200),
                          City     VARCHAR2(200),
                          State    CHAR(2),
                          Zip      VARCHAR2(20),
                          Phone1   VARCHAR2(20),
                          Phone2   VARCHAR2(20),
                          Phone3   VARCHAR2(20),
                          CONSTRAINT cust_pk PRIMARY KEY (CustNo))
ORGANIZATION INDEX OVERFLOW;

-- Purchase Order table
CREATE TABLE po_tab (PONo       NUMBER, /* purchase order number */
                     Custno     NUMBER  /*  Foreign KEY referencing customer */
                                CONSTRAINT po_cust_fk REFERENCES Customer_tab, 
                     OrderDate  DATE, /*  date of order */  
                     ShipDate   TIMESTAMP, /* date to be shipped */    
                     ToStreet   VARCHAR2(200), /* shipto address */    
                     ToCity     VARCHAR2(200),    
                     ToState    CHAR(2),    
                     ToZip      VARCHAR2(20),
                     CONSTRAINT po_pk PRIMARY KEY(PONo));    

--Stock Table
CREATE TABLE Stock_tab (StockNo NUMBER CONSTRAINT stock_uk UNIQUE,
                        Price   NUMBER,
                        TaxRate NUMBER);

--Line Items table
CREATE TABLE LineItems_tab(LineItemNo NUMBER,
                           PONo       NUMBER
                                      CONSTRAINT li_po_fk REFERENCES po_tab,
                           StockNo    NUMBER,
                           Quantity   NUMBER,
                           Discount   NUMBER,
                           CONSTRAINT li_pk PRIMARY KEY (PONo, LineItemNo));

-- create Object views

--Customer Object View
CREATE OR REPLACE VIEW Customer OF Customer_typ
   WITH OBJECT IDENTIFIER(CustNo)
   AS SELECT c.Custno, C.custname,
             Address_typ(C.Street, C.City, C.State, C.Zip),
             PhoneList_vartyp(Phone1, Phone2, Phone3)
      FROM Customer_tab c;

--Purchase order view
CREATE OR REPLACE VIEW PO OF PO_typ
  WITH OBJECT IDENTIFIER (PONo)
   AS SELECT P.PONo, MAKE_REF(Customer, P.Custno), P.OrderDate, P.ShipDate,
             CAST(MULTISET(
                    SELECT LineItem_typ(L.LineItemNo, StockItem_typ(L.StockNo,
                                                                    S.Price,
                                                                    S.TaxRate),
                                        L.Quantity, L.Discount)
                    FROM LineItems_tab L, Stock_tab S
                    WHERE L.PONo = P.PONo and S.StockNo=L.StockNo)
                  AS LineItems_ntabtyp),
             Address_typ(P.ToStreet,P.ToCity, P.ToState, P.ToZip)
      FROM PO_tab P;

-- Create table with XMLType column to store purchase order in XML format
CREATE TABLE po_xml_tab(poid  NUMBER,
                        poDoc XMLType) /* PO in XML format */
/

--------------------
-- Populate data
-------------------
-- Establish Inventory
INSERT INTO Stock_tab VALUES(1004, 6750.00, 2);
INSERT INTO Stock_tab VALUES(1011, 4500.23, 2);
INSERT INTO Stock_tab VALUES(1534, 2234.00, 2);
INSERT INTO Stock_tab VALUES(1535, 3456.23, 2);

-- Register Customers
INSERT INTO Customer_tab
  VALUES (1, 'Jean Nance', '2 Avocet Drive',
         'Redwood Shores', 'CA', '95054',
         '415-555-1212', NULL, NULL);
INSERT INTO Customer_tab
  VALUES (2, 'John Nike', '323 College Drive',
         'Edison', 'NJ', '08820',
         '609-555-1212', '201-555-1212', NULL);

-- Place Orders
INSERT INTO PO_tab
  VALUES (1001, 1, '10-APR-1997', '10-MAY-1997',
          NULL, NULL, NULL, NULL);
INSERT INTO PO_tab
  VALUES (2001, 2, '20-APR-1997', '20-MAY-1997',
         '55 Madison Ave', 'Madison', 'WI', '53715');

-- Detail Line Items
INSERT INTO LineItems_tab VALUES(01, 1001, 1534, 12,  0);
INSERT INTO LineItems_tab VALUES(02, 1001, 1535, 10, 10);
INSERT INTO LineItems_tab VALUES(01, 2001, 1004,  1,  0);
INSERT INTO LineItems_tab VALUES(02, 2001, 1011,  2,  1);

-------------------------------------------------------
-- Use DBMS_XMLGEN Package to generate purchase order in XML format
-- and store XMLType in po_xml table
-------------------------------------------------------
DECLARE
   qryCtx DBMS_XMLGEN.ctxHandle;
   pxml XMLType;
   cxml CLOB;
BEGIN
  -- get query context;
  qryCtx := DBMS_XMLGEN.newContext('SELECT pono,deref(cust_ref) customer,
                                           p.OrderDate,
                                           p.shipdate,
                                           lineitems_ntab lineitems,
                                           shiptoaddr
                                    FROM po p');
  -- set maximum number of rows to be 1,
  DBMS_XMLGEN.setMaxRows(qryCtx, 1);
  -- set ROWSET tag to null and ROW tag to PurchaseOrder
  DBMS_XMLGEN.setRowSetTag(qryCtx, NULL);
  DBMS_XMLGEN.setRowTag(qryCtx,'PurchaseOrder');
  LOOP 
    -- get purchase order in XML format
    pxml := DBMS_XMLGEN.getXMLType(qryCtx);
    -- if there were no rows processed, then quit
    EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
    -- Store XMLType po in po_xml table (get the pono out)
    INSERT INTO po_xml_tab(poid, poDoc)
      VALUES(pxml.extract('//PONO/text()').getNumberVal(), pxml);
  END LOOP;
END;
/
---------------------------
-- list XML PurchaseOrders
---------------------------
SET LONG 100000
SET PAGES 100
SELECT x.podoc.getClobVal() xpo FROM po_xml_tab x;

This produces the following purchase-order XML documents:

PurchaseOrder 1001:

<?xml version="1.0"?>
  <PurchaseOrder>
    <PONO>1001</PONO>
    <CUSTOMER>
      <CUSTNO>1</CUSTNO>
      <CUSTNAME>Jean Nance</CUSTNAME>
      <ADDRESS>
        <STREET>2 Avocet Drive</STREET>
        <CITY>Redwood Shores</CITY>
        <STATE>CA</STATE>
        <ZIP>95054</ZIP>
      </ADDRESS>
      <PHONELIST>
        <VARCHAR2>415-555-1212</VARCHAR2>
      </PHONELIST>
    </CUSTOMER>
    <ORDERDATE>10-APR-97</ORDERDATE>
    <SHIPDATE>10-MAY-97 12.00.00.000000 AM</SHIPDATE>
    <LINEITEMS>
      <LINEITEM_TYP LineItemNo="1">
        <ITEM StockNo="1534">
          <PRICE>2234</PRICE>
          <TAXRATE>2</TAXRATE>
        </ITEM>
        <QUANTITY>12</QUANTITY>
        <DISCOUNT>0</DISCOUNT>
      </LINEITEM_TYP>
      <LINEITEM_TYP LineItemNo="2">
        <ITEM StockNo="1535">
          <PRICE>3456.23</PRICE>
          <TAXRATE>2</TAXRATE>
        </ITEM>
        <QUANTITY>10</QUANTITY>
        <DISCOUNT>10</DISCOUNT>
      </LINEITEM_TYP>
    </LINEITEMS>
    <SHIPTOADDR/>
  </PurchaseOrder>

PurchaseOrder 2001:

<?xml version="1.0"?>
  <PurchaseOrder>
    <PONO>2001</PONO>
    <CUSTOMER>
      <CUSTNO>2</CUSTNO>
      <CUSTNAME>John Nike</CUSTNAME>
      <ADDRESS>
        <STREET>323 College Drive</STREET>
        <CITY>Edison</CITY>
        <STATE>NJ</STATE>
        <ZIP>08820</ZIP>
      </ADDRESS>
      <PHONELIST>
        <VARCHAR2>609-555-1212</VARCHAR2>
        <VARCHAR2>201-555-1212</VARCHAR2>
      </PHONELIST>
    </CUSTOMER>
    <ORDERDATE>20-APR-97</ORDERDATE>
    <SHIPDATE>20-MAY-97 12.00.00.000000 AM</SHIPDATE>
    <LINEITEMS>
      <LINEITEM_TYP LineItemNo="1">
        <ITEM StockNo="1004">
          <PRICE>6750</PRICE>
          <TAXRATE>2</TAXRATE>
        </ITEM>
        <QUANTITY>1</QUANTITY>
        <DISCOUNT>0</DISCOUNT>
      </LINEITEM_TYP>
      <LINEITEM_TYP LineItemNo="2">
        <ITEM StockNo="1011">
          <PRICE>4500.23</PRICE>
          <TAXRATE>2</TAXRATE>
        </ITEM>
        <QUANTITY>2</QUANTITY>
        <DISCOUNT>1</DISCOUNT>
      </LINEITEM_TYP>
    </LINEITEMS>
    <SHIPTOADDR>
      <STREET>55 Madison Ave</STREET>
      <CITY>Madison</CITY>
      <STATE>WI</STATE>
      <ZIP>53715</ZIP>
    </SHIPTOADDR>
  </PurchaseOrder>

Example 15-21 DBMS_XMLGEN: Generating a New Context Handle from a Passed-in PL/SQL Ref Cursor

CREATE OR REPLACE FUNCTION joe3 RETURN CLOB
IS 
  ctx1    NUMBER := 2; 
  ctx2    NUMBER; 
  xmldoc  CLOB; 
  page    NUMBER := 0; 
  xmlpage BOOLEAN := TRUE; 
  refcur  SYS_REFCURSOR;
BEGIN 
   OPEN refcur FOR 'SELECT * FROM emp WHERE ROWNUM < :1' USING ctx1;
   ctx2 := DBMS_XMLGEN.newContext(refcur);
   ctx1 := 4;
   OPEN refcur FOR 'SELECT * FROM emp WHERE ROWNUM < :1' USING ctx1;
   ctx1 := 5;
   OPEN refcur FOR 'SELECT * FROM emp WHERE ROWNUM < :1' USING ctx1;
   DBMS_LOB.createtemporary(xmldoc, TRUE);   
    -- xmldoc will have 4 rows
   xmldoc := DBMS_XMLGEN.getXML(ctx2, DBMS_XMLGEN.NONE); 
   DBMS_XMLGEN.closeContext(ctx2);
   RETURN xmldoc;
END; 
/

Example 15-22 DBMS_XMLGEN: Specifying Null Handling

SQL> @tkxmgnd10
SQL> CONNECT system/manager
Connected.
SQL> 
SQL> GRANT CONNECT, RESOURCE TO gnd10 IDENTIFIED BY gnd10;
Grant succeeded.
SQL> CONNECT gnd10/gnd10
Connected.
SQL> SET SERVEROUTPUT ON SIZE 200000
SQL> -- null_handle: 1 => NULL_ATTR, 2 => EMPTY_TAG
SQL> CREATE OR REPLACE FUNCTION getXML(sql_query VARCHAR2, null_handle NUMBER,
  2                                    rset_tag VARCHAR2 := 'ROWSET',
  3                                    rtag VARCHAR2 := 'ROW')
  4  RETURN CLOB IS
  5    ctx NUMBER;
  6    xmldoc CLOB;
  7  BEGIN
  8    ctx := DBMS_XMLGEN.newContext(sql_query);
  9  
 10    IF (nvl(rset_tag, 'X') != 'ROWSET') THEN
 11       DBMS_XMLGEN.setRowSetTag(ctx, rset_tag);
 12    END IF;
 13  
 14    IF (nvl(rtag, 'Y') != 'ROW') THEN
 15       DBMS_XMLGEN.setRowTag(ctx, rtag);
 16    END IF;
 17  
 18    DBMS_XMLGEN.setNullHandling(ctx, null_handle);
 19  
 20    xmldoc := DBMS_XMLGEN.getXML(ctx);
 21    DBMS_XMLGEN.closeContext(ctx);
 22    RETURN xmldoc;
 23  END;
 24  /
Function created.

Example 15-23 DBMS_XMLGEN : Generating a Hierarchical XML Document Containing Recursive Elements From the Result of a Hierarchical Query (Formed by a CONNECT BY Clause)

The DBMS_XMLGEN package contains the function newContextFromHierarchy(). It takes a hierarchical query string, which is typically formulated with a CONNECT BY clause, as an argument and returns a context to be used to generate a hierarchical XML document with recursive elements.

The hierarchical query returns two columns, the level number (a pseudo-column generated by CONNECT BY query) and an XMLType. The level is used to determine the position of the XMLType value within the hierarchy of the result XML document.

Setting skip number of rows or maximum number of rows for a context created from newContextFromHierarchy() is an error.

For example, you can generate a Manager employee hierarchy by using DBMS_ XMLGEN.newContextFormHierarchy().

SQL> set serveroutput on size 200000
SQL> set long 200000
SQL>

SQL> create table sqlx_display(id number, xmldoc XMLType);

Table created.

SQL>
SQL> -- Test 2: XMLelement with scott schema
SQL> declare
  2          qryctx dbms_xmlgen.ctxhandle;
  3          result XMLType;
  4  begin
  5    qryctx := dbms_xmlgen.newcontextFromHierarchy(
  6    'select level, xmlelement("emp", xmlelement("enumber", empno),
  7           xmlelement("name", ename),
  8           xmlelement("Salary", sal),
  9           xmlelement("Hiredate", hiredate)) from scott.emp
 10          start with ename=''KING'' connect by prior empno=mgr
 11          order siblings by hiredate');
 12    result := dbms_xmlgen.getxmltype(qryctx);
 13    dbms_output.put_line('<result num rows>');
 14    dbms_output.put_line(to_char(dbms_xmlgen.getNumRowsProcessed(qryctx
)));
 15    dbms_output.put_line('</result num rows>');
 16    insett into sqlx_display values  (2, result);
 17    commit;
 18    dbms_xmlgen.closecontext(qryctx);
 19  end;
 20  /
<result num rows>
14
</result num rows>

PL/SQL procedure successfully completed.

SQL>
SQL> select xmldoc from sqlx_display where id = 2;

XMLDOC
------------------------------------------------------------------------
<?xml version="1.0"?>
<emp>
  <enumber>7839</enumber>
  <name>KING</name>
  <Salary>5000</Salary>
  <Hiredate>17-NOV-81</Hiredate>
  <emp>
    <enumber>7566</enumber>
    <name>JONES</name>
    <Salary>2975</Salary>
    <Hiredate>02-APR-81</Hiredate>
    <emp>
      <enumber>7902</enumber>
      <name>FORD</name>
      <Salary>3000</Salary>
      <Hiredate>03-DEC-81</Hiredate>
      <emp>
        <enumber>7369</enumber>
        <name>SMITH</name>
        <Salary>800</Salary>
        <Hiredate>17-DEC-80</Hiredate>
      </emp>
    </emp>
    <emp>
      <enumber>7788</enumber>
      <name>SCOTT</name>
      <Salary>3000</Salary>
      <Hiredate>19-APR-87</Hiredate>
      <emp>
        <enumber>7876</enumber>
        <name>ADAMS</name>
        <Salary>1100</Salary>
        <Hiredate>23-MAY-87</Hiredate>
      </emp>
    </emp>
  </emp>
  <emp>
    <enumber>7698</enumber>
    <name>BLAKE</name>
    <Salary>2850</Salary>
    <Hiredate>01-MAY-81</Hiredate>
    <emp>
      <enumber>7499</enumber>
      <name>ALLEN</name>
      <Salary>1600</Salary>
      <Hiredate>20-FEB-81</Hiredate>
    </emp>
    <emp>
      <enumber>7521</enumber>
      <name>WARD</name>
      <Salary>1250</Salary>
      <Hiredate>22-FEB-81</Hiredate>
    </emp>
    <emp>
      <enumber>7844</enumber>
      <name>TURNER</name>
      <Salary>1500</Salary>
      <Hiredate>08-SEP-81</Hiredate>
    </emp>
    <emp>
      <enumber>7654</enumber>
      <name>MARTIN</name>
      <Salary>1250</Salary>
      <Hiredate>28-SEP-81</Hiredate>
    </emp>
    <emp>
      <enumber>7900</enumber>
      <name>JAMES</name>
      <Salary>950</Salary>
      <Hiredate>03-DEC-81</Hiredate>
    </emp>
  </emp>
  <emp>
    <enumber>7782</enumber>
    <name>CLARK</name>
    <Salary>2450</Salary>
    <Hiredate>09-JUN-81</Hiredate>
    <emp>
      <enumber>7934</enumber>
      <name>MILLER</name>
      <Salary>1300</Salary>
      <Hiredate>23-JAN-82</Hiredate>
    </emp>
  </emp>
</emp>

1 row selected.

By default, the RowSet tag is NULL. That is, there is no default rowset tag used to enclose the XML result. However, you can explicitly set the rowset tag by using the setRowSetTag() procedure, as follows:

SQL>
SQL> create table gg(x XMLType);

Table created.
SQL> declare
  2          qryctx dbms_xmlgen.ctxhandle;
  3          result clob;
  4  begin
  5          qryctx := dbms_xmlgen.newcontextFromHierarchy('select level,
  6                  xmlelement("NAME", name) as myname from tc
  7                  connect by prior id = pid start with id = 1');
  8          dbms_xmlgen.setRowSetTag(qryctx, 'mynum_hierarchy');
  9          result:=dbms_xmlgen.getxml(qryctx);
 10          
 11          dbms_output.put_line('<result num rows>');
 12          dbms_output.put_line(to_char(dbms_xmlgen.getNumRowsProcessed(qryctx
)));
 13          dbms_output.put_line('</result num rows>');
 14          insert into gg values(xmltype(result));
 15          commit;
 16          dbms_xmlgen.closecontext(qryctx);
 17  end;
 18  /
<result num rows>
6
</result num rows>

PL/SQL procedure successfully completed.

SQL> select * from gg;

X
------------------------------------------------------------------------------
<?xml version="1.0"?>
<mynum_hierarchy>
  <NAME>top
    <NAME>second1
      <NAME>third3</NAME>
    </NAME>
    <NAME>second2
      <NAME>third1</NAME>
      <NAME>third2</NAME>
    </NAME>
  </NAME>
</mynum_hierarchy>

Example 15-24 DBMS_XMLGEN : Using setBindValue() to Bind Variables in the Query String for DBMS_XMLGEN

The query string that is used to create context can contain host variables and then bind values to it by using setBindValue() before the execution of the query.

SET SERVEROUTPUT ON SIZE 200000
SET LONG 200000
 
-- bind one variable 
DECLARE
  2  ctx NUMBER;
  3  xmldoc CLOB;
  4  BEGIN
  5    ctx := DBMS_XMLGEN.newContext('SELECT * FROM emp WHERE empno = :NO');
  6
  7    DBMS_XMLGEN.setBindValue(ctx, 'NO', '7369');
  8    xmldoc := DBMS_XMLGEN.getXML(ctx);
  9    printClobOut(xmldoc);
 10    DBMS_XMLGEN.closeContext(ctx);
 11  EXCEPTION
 12    WHEN OTHERS THEN
 13    DBMS_XMLGEN.closeContext(ctx);
 14    RAISE;
 15  END;
 16  /
| <?xml version="1.0"?>
| <ROWSET>
|   <ROW>
|     <EMPNO>7369</EMPNO>
|     <ENAME>SMITH</ENAME>
|     <JOB>CLERK</JOB>
|     <MGR>7902</MGR>
|     <HIREDATE>17-DEC-80</HIREDATE>
|     <SAL>800</SAL>
|     <DEPTNO>20</DEPTNO>
|   </ROW>
| </ROWSET>
 
SQL> --bind one variable twice with different values
SQL> DECLARE
  2  ctx NUMBER;
  3  xmldoc CLOB;
  4  BEGIN
  5    ctx := DBMS_XMLGEN.newContext('SELECT * FROM emp
                                      WHERE hiredate = :MDATE');
  6
  7    DBMS_XMLGEN.setBindValue(ctx, 'MDATE', '17-DEC-80');
  8    xmldoc := DBMS_XMLGEN.getXML(ctx);
  9    printClobOut(xmldoc);
 10
 11    DBMS_XMLGEN.setBindValue(ctx, 'MDATE', '20-FEB-81');
 12    xmldoc := DBMS_XMLGEN.getXML(ctx);
 13    printClobOut(xmldoc);
 14    DBMS_XMLGEN.closeContext(ctx);
 15  EXCEPTION
 16    WHEN OTHERS THEN
 17    DBMS_XMLGEN.closeContext(ctx);
 18    RAISE;
 19  END;
 20  /
| <?xml version="1.0"?>
| <ROWSET>
|   <ROW>
|     <EMPNO>7369</EMPNO>
|     <ENAME>SMITH</ENAME>
|     <JOB>CLERK</JOB>
|     <MGR>7902</MGR>
|     <HIREDATE>17-DEC-80</HIREDATE>
|     <SAL>800</SAL>
|     <DEPTNO>20</DEPTNO>
|   </ROW>
| </ROWSET>
| <?xml version="1.0"?>
| <ROWSET>
|   <ROW>
|     <EMPNO>7499</EMPNO>
|     <ENAME>ALLEN</ENAME>
|     <JOB>SALESMAN</JOB>
|     <MGR>7698</MGR>
|     <HIREDATE>20-FEB-81</HIREDATE>
|     <SAL>1600</SAL>
|     <COMM>300</COMM>
|     <DEPTNO>30</DEPTNO>
|   </ROW>
| </ROWSET>
 
PL/SQL procedure successfully completed.
SQL> -- bind two variables 
SQL> DECLARE
  2  ctx NUMBER;
  3  xmldoc CLOB;
  4  BEGIN
  5    ctx := DBMS_XMLGEN.newContext('SELECT * FROM emp
  6                                   WHERE empno = :NO
  7                                     AND hiredate = :MDATE');
  8    DBMS_XMLGEN.setBindValue(ctx, 'NO', '7369');
  9    DBMS_XMLGEN.setBindValue(ctx, 'MDATE', '17-DEC-80');
 10    xmldoc := DBMS_XMLGEN.getXML(ctx);
 11    printClobOut(xmldoc);
 12    DBMS_XMLGEN.closeContext(ctx);
 13  EXCEPTION
 14    WHEN OTHERS THEN
 15    DBMS_XMLGEN.closeContext(ctx);
 16    RAISE;
 17  END;
 18  /
| <?xml version="1.0"?>
| <ROWSET>
|   <ROW>
|     <EMPNO>7369</EMPNO>
|     <ENAME>SMITH</ENAME>
|     <JOB>CLERK</JOB>
|     <MGR>7902</MGR>
|     <HIREDATE>17-DEC-80</HIREDATE>
|     <SAL>800</SAL>
|     <DEPTNO>20</DEPTNO>
|   </ROW>
| </ROWSET>

Example 15-27 Creating a Formatting Object with createFormat

You can use the static member function createformat to implement the XMLFormat object. This function has most of the values defaulted. For example:

STATIC FUNCTION createFormat(enclTag IN VARCHAR2 := 'ROWSET',
                             schemaType IN VARCHAR2 := 'NO_SCHEMA',
                             schemaName IN VARCHAR2 := NULL,
                             targetNameSpace IN VARCHAR2 := NULL,
                             dburlPrefix IN VARCHAR2 := NULL, 
                             processingIns IN VARCHAR2 := NULL)
RETURN 
  XMLGenFormatType,
  MEMBER PROCEDURE genSchema (spec IN VARCHAR2),
  MEMBER PROCEDURE setSchemaName(schemaName IN VARCHAR2),
  MEMBER PROCEDURE setTargetNameSpace(targetNameSpace IN VARCHAR2),
  MEMBER PROCEDURE setEnclosingElementName(enclTag IN VARCHAR2), 
  MEMBER PROCEDURE setDbUrlPrefix(prefix IN VARCHAR2),
  MEMBER PROCEDURE setProcessingIns(pi IN VARCHAR2),
  CONSTRUCTOR FUNCTION XMLGenFormatType(enclTag IN VARCHAR2 := 'ROWSET',
                                        schemaType IN VARCHAR2 := 'NO_SCHEMA',
                                        schemaName IN VARCHAR2 := NULL,
                                        targetNameSpace IN VARCHAR2 := NULL,
                                        dbUrlPrefix IN VARCHAR2 := NULL, 
                                        processingIns IN VARCHAR2 := NULL)
RETURN SELF AS RESULT

Example 15-28 SYS_XMLGEN(): Converting a Scalar Value to an XML Document Element Contents

When you enter a scalar value to SYS_XMLGEN(), it converts the scalar value to an element containing the scalar value. For example:

SELECT SYS_XMLGEN(empno) FROM scott.emp WHERE ROWNUM < 2;

returns an XML document that contains the empno value as an element, as follows:

SYS_XMLGEN(EMPNO)
-------------------------
<?xml version="1.0"?>
<EMPNO>7369</EMPNO>

1 row selected.

The enclosing element name, in this case EMPNO, is derived from the column name passed to the operator. Also, note that the result of the SELECT statement is a row containing a XMLType.

Example 15-29 Generating Default Column Name, ROW

In the last example, you used the column name EMPNO for the document. If the column name cannot be derived directly, then the default name ROW is used. For example, in the following case:

SELECT SYS_XMLGEN(empno).getclobval()
FROM scott.emp 
WHERE ROWNUM < 2;

you get the following XML output:

SYS_XMLGEN(EMPNO).GETCLOBVAL()
--------------------------------------
<?xml version="1.0"?>
<EMPNO>7369</EMPNO>

because the function cannot infer the name of the expression. You can override the default ROW tag by supplying an XMLFormat (the old name was "XMLGenFormatType") object to the first argument of the operator.

Example 15-30 Overriding the Default Column Name: Supplying an XMLFormat Object to the Operator's First Argument

For example, in the last case, if you wanted the result to have EMPNO as the tag name, then you can supply a formatting argument to the function, as follows:

SELECT SYS_XMLGEN(empno *2,
                  xmlformat.createformat('EMPNO')).getClobVal()
FROM scott.emp
WHERE ROWNUM < 2;

This results in the following XML:

SYS_XMLGEN(EMPNO*2,XMLFORMAT.CREATEFORMAT('EMPNO')).GETCLOBVAL()
--------------------------------------------------------------------
<?xml version="1.0"?>
<EMPNO>14738</EMPNO>

1 row selected.

Example 15-31 SYS_XMLGEN(): Converting a User-Defined Type to XML

When you enter a user-defined type value to SYS_XMLGEN(), the user-defined type gets mapped to an XML document using a canonical mapping. In the canonical mapping the user-defined type attributes are mapped to XML elements.

Any type attributes with names starting with @ are mapped to an attribute of the preceding element. User-defined types can be used to get nesting within the result XML document.

Using the same example as given in the DBMS_XMLGEN section, you can generate a hierarchical XML for the employee, department example as follows:

CREATE OR REPLACE TYPE scott.emp_t AS OBJECT(empno    NUMBER(4),
                                             ename    VARCHAR2(10),
                                             job      VARCHAR2(9),
                                             mgr      NUMBER(4),
                                             hiredate DATE,
                                             sal      NUMBER(7, 2),
                                             comm     NUMBER(7, 2));
/
CREATE OR REPLACE TYPE scott.emplist_t AS TABLE OF emp_t;
/
CREATE OR REPLACE TYPE scott.dept_t AS OBJECT(deptno  NUMBER(2),
                                              dname   VARCHAR2(14),
                                              loc     VARCHAR2(13),
                                              emplist emplist_t);
/
SELECT SYS_XMLGEN(dept_t(deptno, dname, d.loc,
                         CAST(MULTISET(SELECT emp_t(e.empno, e.ename, e.job,
                                                    e.mgr, e.hiredate, e.sal,
                                                    e.comm)
                                       FROM scott.emp e
                                       WHERE e.deptno = d.deptno)
                              AS emplist_t))).getClobVal()
AS deptxml
FROM scott.dept d;

The MULTISET operator treats the result of the subset of employees working in the department as a list and the CAST around it, assigns it to the appropriate collection type. You then create a department instance around it and call SYS_XMLGEN() to create the XML for the object instance.

The result is:

DEPTXML
------------------
<?xml version="1.0"?>
<ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>Accounting</DNAME>
  <LOC>NEW YORK</LOC>
  <EMPLIST>
    <EMP_T>
      <EMPNO>7782</EMPNO>
      <ENAME>CLARK</ENAME>
      <JOB>MANAGER</JOB>
      <MGR>7839</MGR>
      <HIREDATE>09-JUN-81</HIREDATE>
      <SAL>2450</SAL>
    </EMP_T>
    <EMP_T>
      <EMPNO>7839</EMPNO>
      <ENAME>KING</ENAME>
      <JOB>PRESIDENT</JOB>
      <HIREDATE>17-NOV-81</HIREDATE>
      <SAL>5000</SAL>
    </EMP_T>
  </EMPLIST>
</ROW>
<?xml version="1.0"?>
<ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <LOC>DALLAS</LOC>
  <EMPLIST>
    <EMP_T>
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>
      <HIREDATE>17-DEC-80</HIREDATE>
      <SAL>800</SAL>
    </EMP_T>
    
    <EMP_T>
      <EMPNO>7566</EMPNO>
      <ENAME>JONES</ENAME>
      <JOB>MANAGER</JOB>
      <MGR>7839</MGR>
      <HIREDATE>02-APR-87</HIREDATE>
      <SAL>3000</SAL>
    </EMP_T>
    <EMP_T>
      <EMPNO>7788</EMPNO>
      <ENAME>SCOTT</ENAME>
      <JOB>ANALYST</JOB>
      <MGR>7566</MGR>
      <HIREDATE>19-APR-87</HIREDATE>
      <SAL>3000</SAL>
    </EMP_T>
    <EMP_T>
      <EMPNO>7876</EMPNO>
      <ENAME>ADAMS</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7788</MGR>
      <HIREDATE>23-MAY-87</HIREDATE>
      <SAL>1100</SAL>
    </EMP_T>
    <EMP_T>
      <EMPNO>7902</EMPNO>
      <ENAME>FORD</ENAME>
      <JOB>ANALYST</JOB>
      <MGR>7566</MGR>
      <HIREDATE>03-DEC-81</HIREDATE>
      <SAL>3000</SAL>
    </EMP_T>
</EMPLIST>
</ROW>
<?xml version="1.0"?>
<ROW>
  <DEPTNO>30</DEPTNO>
  <DNAME>SALES</DNAME>
  <LOC>CHICAGO</LOC>
  <EMPLIST>
    <EMP_T>
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>20-FEB-81</HIREDATE>
      <SAL>1600</SAL>
      <COMM>300</COMM>
    </EMP_T>
    
    <EMP_T>
      <EMPNO>7521</EMPNO>
      <ENAME>WARD</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>22-FEB-81</HIREDATE>
      <SAL>1250</SAL>
      <COMM>500</COMM>
    </EMP_T>
    <EMP_T>
      <EMPNO>7654</EMPNO>
      <ENAME>MARTIN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>28-SEP-81</HIREDATE>
      <SAL>1250</SAL>
      <COMM>1400</COMM>
    </EMP_T>
    <EMP_T>
      <EMPNO>7698</EMPNO>
      <ENAME>BLAKE</ENAME>
      <JOB>MANAGER</JOB>
      <MGR>7839</MGR>
      <HIREDATE>01-MAY-81</HIREDATE>
      <SAL>2850</SAL>
    </EMP_T>
    <EMP_T>
      <EMPNO>7844</EMPNO>
      <ENAME>TURNER</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>08-SEP-81</HIREDATE>
      <SAL>1500</SAL>
      <COMM>0</COMM>
    </EMP_T>
    <EMP_T>
      <EMPNO>7900</EMPNO>
      <ENAME>JAMES</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7698</MGR>
      <HIREDATE>03-DEC-81</HIREDATE>
      <SAL>950</SAL>
    </EMP_T>
  </EMPLIST>
</ROW>

<?xml version="1.0"?>
<ROW>
 <DEPTNO>40</DEPTNO>
 <DNAME>OPERATIONS</DNAME>
 <LOC>BOSTON</LOC>
 <EMPLIST/>
</ROW>

4 rows selected.

The default name ROW is present because the function cannot deduce the name of the input operand directly.

Example 15-32 SYS_XMLGEN(): Converting an XMLType Instance

If you pass an XML document into SYS_XMLGEN(), then SYS_XMLGEN() encloses the document (or fragment) with an element, whose tag name is the default ROW, or the name passed in through the formatting object. This functionality can be used to turn document fragments into well formed documents.

For example, the extract() operation on the following document, can return a fragment. If you extract out the EMPNO elements from the following document:

<DOCUMENT>
  <EMPLOYEE>
    <ENAME>John</ENAME>
    <EMPNO>200</EMPNO>
  </EMPLOYEE>
  <EMPLOYEE>
    <ENAME>Jack</ENAME>
    <EMPNO>400</EMPNO>
  </EMPLOYEE>
  <EMPLOYEE>
    <ENAME>Joseph</ENAME>
    <EMPNO>300</EMPNO>
  </EMPLOYEE>
</DOCUMENT>

using the following statement:

SELECT e.podoc.extract('/DOCUMENT/EMPLOYEE/ENAME') 
  FROM po_xml_tab e;

then you get an XML document fragment such as the following:

<ENAME>John</ENAME>
<ENAME>Jack</ENAME>
<ENAME>Joseph</ENAME>

You can make this fragment a valid XML document, by calling SYS_XMLGEN() to put an enclosing element around the document, as follows:

SELECT SYS_XMLGEN(e.podoc.extract('/DOCUMENT/EMPLOYEE/ENAME')).getclobval()
  FROM po_xml_tab e;

This places an element ROW around the result, as follows:

<?xml version="1.0"?>
<ROW>
  <ENAME>John</ENAME>
  <ENAME>Jack</ENAME>
  <ENAME>Joseph</ENAME>
</ROW>

Example 15-33 SYS_XMLGEN(): Using SYS_XMLGEN() with Object Views

-- create purchase order object type
CREATE OR REPLACE TYPE PO_typ AUTHID CURRENT_USER
  AS OBJECT(PONO            NUMBER,
            Customer        Customer_typ,
            OrderDate       DATE,
            ShipDate        TIMESTAMP,
            LineItems_ntab  LineItems_ntabtyp,
            ShipToAddr      Address_typ)
/
--Purchase order view
CREATE OR REPLACE VIEW PO OF PO_typ
  WITH OBJECT IDENTIFIER (PONO)
   AS SELECT P.PONo, Customer_typ(P.Custno, C.CustName, C.Address, C.PhoneList),
             P.OrderDate, P.ShipDate,
             CAST(MULTISET(
                    SELECT 
                      LineItem_typ(L.LineItemNo,
                                   StockItem_typ(L.StockNo,S.Price,S.TaxRate),
                                                 L.Quantity, L.Discount)
                      FROM LineItems_tab L, Stock_tab S
                      WHERE L.PONo = P.PONo AND S.StockNo=L.StockNo)
                  AS LineItems_ntabtyp),
             Address_typ(P.ToStreet, P.ToCity, P.ToState, P.ToZip)
        FROM PO_tab P, Customer C
        WHERE P.CustNo=C.custNo;

-------------------------------------------------------
-- Use SYS_XMLGEN() to generate PO in XML format
-------------------------------------------------------
SET LONG 20000
SET PAGES 100
SELECT 
  SYS_XMLGEN(value(p),
             sys.xmlformat.createFormat('PurchaseOrder')).getClobVal() PO
  FROM po p 
  WHERE p.pono=1001;

This returns the Purchase Order in XML format:

<?xml version="1.0"?>
<PurchaseOrder>
  <PONO>1001</PONO>
  <CUSTOMER>
    <CUSTNO>1</CUSTNO>
    <CUSTNAME>Jean Nance</CUSTNAME>
    <ADDRESS>
      <STREET>2 Avocet Drive</STREET>
      <CITY>Redwood Shores</CITY>
      <STATE>CA</STATE>
      <ZIP>95054</ZIP>
    </ADDRESS>
    <PHONELIST>
      <VARCHAR2>415-555-1212</VARCHAR2>
    </PHONELIST>
  </CUSTOMER>
  <ORDERDATE>10-APR-97</ORDERDATE>
  <SHIPDATE>10-MAY-97 12.00.00.000000 AM</SHIPDATE>
  <LINEITEMS_NTAB>
    <LINEITEM_TYP LineItemNo="1">
      <ITEM StockNo="1534">
        <PRICE>2234</PRICE>
        <TAXRATE>2</TAXRATE>
      </ITEM>
      <QUANTITY>12</QUANTITY>
      <DISCOUNT>0</DISCOUNT>
    </LINEITEM_TYP>
    <LINEITEM_TYP LineItemNo="2">
      <ITEM StockNo="1535">
        <PRICE>3456.23</PRICE>
        <TAXRATE>2</TAXRATE>
      </ITEM>
      <QUANTITY>10</QUANTITY>
      <DISCOUNT>10</DISCOUNT>
    </LINEITEM_TYP>
  </LINEITEMS_NTAB>
  <SHIPTOADDR/>
</PurchaseOrder>

Example 15-37 Using XMLAgg ORDER BY Clause

For example, in the following expression the result is aggregated according to the order of the dev field:

SELECT XMLAgg(XMLElement("Dev", 
                          XMLAttributes(dev AS "id", 
                                        dev_total AS "total"),
                         devname AS "name") 
              ORDER BY dev) 
  FROM tab1 dev_total;

Example 15-38 Returning a Rowset using XMLSequence in the TABLE Clause

SELECT extractValue(value(t), '/*/Last') as LAST, 
       extractValue(value(t), '/*/First') as FIRST 
   FROM movies m, 
        table(xmlsequence(extract(value(m), 
                                  '/Movie/Cast/Actor | '/Movie/Cast/Actress)))
   ORDER BY LAST;