HAWOK XML Faq

Example DBMSXMLGEN Generating Complex XML Inputting User Defined Types For Nested XML Documents

• How To Make Money Writing Easy Web Articles

When you input 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's 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:

CREATE TYPE EMP_T AS OBJECT (

"@empno" number, -- empno defined as an attribute! ename varchar2(20)

-- You have defined the empno with an @ sign in front, to denote that it must -- be mapped as an attribute of the enclosing Employee element.

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, denotes the department as containing a list of -- employees. You 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 the 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

-- set the maximum number of rows to be 5, dbms_xmlgen.setMaxRows(qryCtx, 5);

-- set no row tag for this result as we have a single ADT column dbms_xmlgen.setRowTag(qryCtx,null);

loop

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

-- if there were no rows processed, then quit..! exit when dbms_xmlgen.getNumRowsProcessed(qryCtx) = 0;

The MULTISET operator treats the result of the subset of employees working in the department as a list and the CAST around it, cast's 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>

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

0 0