| Oracle9i XML Database Developer's Guide - Oracle XML DB Release 2 (9.2) Part Number A96620-02 |
|
|
View PDF |
| Oracle9i XML Database Developer's Guide - Oracle XML DB Release 2 (9.2) Part Number A96620-02 |
|
|
View PDF |
This chapter describes Oracle XML DB options for generating XML from the database. It explains in detail, the SQLX standard functions and Oracle-provided functions and packages for generating XML data from relational content.
It contains these sections:
Oracle9i supports native XML generation. In this release, Oracle provides you with several new options for generating or regenerating XML data when stored in:
Figure 10-1 illustrates the Oracle XML DB options you can use to generate XML from Oracle9i database.
The following SQLX functions are supported in Oracle XML DB:
The following are Oracle extension functions to SQLX:
Oracle XML DB supports DBMS_XMLGEN, a PL/SQL supplied package. DBMS_XMLGEN generates XML from SQL queries. See "Generating XML from Oracle9i Database Using DBMS_XMLGEN".
Oracle XML DB also supports the following Oracle-provided SQL functions that generate XML from SQL queries:
. Note that only the cursor version of this function generates XML. This function is also classified as an SQLX function."Generating XML Using XSQL Pages Publishing Framework" can also be used to generate XML from Oracle9i database.
XSQL Pages Publishing Framework, also known as XSQL Servlet, is part of the XDK for Java.
XML SQL Utility (XSU) enables you to perform the following tasks on data in XMLType tables and columns:
XMLElement(), XMLForest(), XMLConcat(), and XMLAgg() belong to the SQLX standard, an emerging SQL standard for XML. Because these are emerging standards the syntax and semantics of these functions are subject to change in the future in order to conform to the standard.
All of the generation functions convert user-defined types (UDTs) to their canonical XML format. In the canonical mapping the user-defined type's attributes are mapped to XML elements.
XMLElement() function is based on the emerging SQL XML standard. It takes an element name, an optional collection of attributes for the element, and zero or more arguments that make up the element's content and returns an instance of type XMLType. See Figure 10-2. The XML_attributes_clause is described in the following section.
It is similar to SYS_XMLGEN(), but unlike SYS_XMLGEN(), XMLElement() does not create an XML document with the prolog (the XML version information). It allows multiple arguments and can include attributes in the XML returned.
XMLElement() is primarily used to construct XML instances from relational data. It takes an identifier that is partially escaped to give the name of the root XML element to be created. The identifier does not have to be a column name, or column reference, and cannot be an expression. If the identifier specified is NULL, then no element is returned.
As part of generating a valid XML element name from an SQL identifier, characters that are disallowed in an XML element name are escaped. Partial escaping implies that SQL identifiers other than the ":" sign which are not representable in XML, are escaped using the # sign followed by the character's unicode representation in hexadecimal format. This can be used to specify namespace prefixes for the elements being generated. The fully escaped mapping escapes all non-XML characters in the SQL identifier name, including the ":" character.
XMLElement() also takes an optional XMLAttributes() clause, which specifies the attributes of that element. This can be followed by a list of values that make up the children of the newly created element. See Figure 10-3.
In the XMLAttributes() clause, the value expressions are evaluated to get the values for the attributes. For a given value expression, if the AS clause is omitted, the fully escaped form of the column name is used as the name of the attribute. If the AS clause is specified, then the partially escaped form of the alias is used as the name of the attribute. If the expression evaluates to NULL, then no attribute is created for that expression. The type of the expression cannot be an object type or collection.
The list of values that follow the XMLAttributes() clause are converted to XML format, and are made as children of the top-level element. If the expression evaluates to NULL, then no element is created for that expression.
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: -- ID result -- -------------------- -- 1001 <Emp>John Smith</Emp> -- 1206 <Emp>Mary Martin</Emp>
XMLElement() can also be nested to produce XML data with a nested structure.
To produce an Emp element for each employee, with elements that provide the employee's name and start date:
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>2000-05-24</hiredate> </Emp> <Emp> <name>Mary Martin</name> <hiredate>1996-02-01</hiredate> </Emp>
|
Note: Attributes, if they are specified, appear in the second argument of " |
This example produces an Emp element for each employee, with an id and name attribute:
SELECT XMLELEMENT ( "Emp", XMLATTRIBUTES (e.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 ... ;
This query could produce the following XML result:
<Emp:Exempt FNAME="John" name:last="Smith" name_x003A_middle="Quincy" /> ...
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:
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 = 100;
This creates an XML document that conforms to the Employee.xsd XMLSchema, result:
-------------- <Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:nonamespaceSchemaLocation="http://www.oracle.com/Employee.xsd"> <EMPNO>1769</EMPNO> <ENAME>John</ENAME> <SAL>200000</SAL> </Employee>
Using the same example as given in the following DBMS_XMLGEN section (Example 10-18, "DBMS_XMLGEN: Generating Complex XML"), you can generate a hierarchical XML for the employee, department example as follows:
SELECT XMLElement("Department", 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;
This produces an XML document which contains the Department element and the canonical mapping of the dept_t type.
<Department> <DEPT_T DEPTNO="100"> <DNAME>Sports</DNAME> <EMPLIST> <EMP_T EMPNO="200"> <ENAME>John</ENAME> <EMP_T> <EMP_T> <ENAME>Jack</ENAME> </EMP_T> </EMPLIST> </DEPT_T> </Department>
XMLForest() function produces a forest of XML elements from the given list of arguments. The arguments may be value expressions with optional aliases. Figure 10-4 describes the XMLForest() syntax.
The list of value expressions are converted to XML format. For a given expression, if the AS clause is omitted, the fully escaped form of the column name is used as the name of the enclosing tag of the element.
For an object type or collection, the AS clause is mandatory, and for other types, it can still be optionally specified. If the AS clause is specified, then the partially escaped form of the alias is used as the name of the enclosing tag. If the expression evaluates to NULL, then no element is created for that expression.
This example generates an Emp element for each employee, with a name attribute and elements with the employee's start date and department as the content.
SELECT XMLELEMENT("Emp", XMLATTRIBUTES ( e.fname ||' '|| e.lname AS "name" ), XMLForest ( e.hire, e.dept AS "department")) AS "result" FROM employees e;
This query might produce the following XML result:
<Emp name="John Smith"> <HIRE>2000-05-24</HIRE> <department>Accounting</department> </Emp> <Emp name="Mary Martin"> <HIRE>1996-02-01</HIRE> <department>Shipping</department> </Emp>
You can also use XMLForest() to generate XML from user-defined types (UDTs). Using the same example as given in the following DBMS_XMLGEN section (Example 10-18, "DBMS_XMLGEN: Generating Complex XML"), you can generate a hierarchical XML for the employee, department example as follows:
SELECT XMLForest( dept_t(deptno,dname, CAST(MULTISET( select empno, ename from emp e where e.deptno = d.deptno) AS emplist_t)) AS "Department") AS deptxml FROM dept d;
This produces an XML document which contains the Department element and the canonical mapping of the dept_t type.
<Department DEPTNO="100"> <DNAME>Sports</DNAME> <EMPLIST> <EMP_T EMPNO="200"> <ENAME>John</ENAME> </EMP_T> <EMP_T> <ENAME>Jack</ENAME> </EMP_T> </EMPLIST></Department>
XMLSequence() function returns a sequence of XMLType. The function returns an XMLSequenceType which is a VARRAY of XMLType instances. Since this function returns a collection, it can be used in the FROM clause of SQL queries. See Figure 10-5.
The XMLSequence() function has two forms
XMLType instance and returns a VARRAY of top-level nodes. This form can be used to shred XML fragments into multiple rows.REFCURSOR argument, with an optional instance of the XMLFormat object and returns the VARRAY of XMLTypes corresponding to each row of the cursor. This form can be used to construct XMLType instances from arbitrary SQL queries. Note that in this release, this use of XMLFormat does not support XML schemas.XMLSequence() is essential for effective SQL queries involving XMLTypes.
Suppose you had the following XML document containing employee information:
<EMPLOYEES> <EMP> <EMPNO>112</EMPNO> <EMPNAME>Joe</EMPNAME> <SALARY>50000</SALARY> </EMP> <EMP> <EMPNO>217</EMPNO> <EMPNAME>Jane</EMPNAME> <SALARY>60000</SALARY> </EMP> <EMP> <EMPNO>412</EMPNO>7 <EMPNAME>Jack</EMPNAME> <SALARY>40000</SALARY> </EMP> </EMPLOYEES>
To create a new XML document containing only those employees who make $50,000 or more for each year, you can use the following syntax:
SELECT SYS_XMLAGG(value(e), xmlformat('EMPLOYEES')) FROM TABLE(XMLSequence(Extract(doc, '/EMPLOYEES/EMP'))) e WHERE EXTRACTVALUE(value(e), '/EMP/SALARY') >= 50000;
This returns the following XML document:
<EMPLOYEES> <EMP> <EMPNO>112</EMPNO> <EMPNAME>Joe</EMPNAME> <SALARY>50000</SALARY> </EMP> <EMP> <EMPNO>217</EMPNO> <EMPNAME>Jane</EMPNAME> <SALARY>60000</SALARY> </EMP> </EMPLOYEES>
Notice how XMLExtract() was used to extract out all the employees:
XMLExtract() returns a fragment of EMP elements.XMLSequence() creates a collection of these top level elements into XMLType instances and returns that.TABLE function was then used to makes the collection into a table value which can be used in the FROM clause of queries.Here XMLSequence() creates an XML document for each row of the cursor expression and returns the value as an XMLSequenceType. The XMLFormat object can be used to influence the structure of the resulting XML documents. For example, a call such as:
SELECT value(e).getClobVal() FROM TABLE(XMLSequence(Cursor(SELECT * FROM emp))) e;
might return the following XML:
XMLType --------------------------------- <ROW> <EMPNO>300</EMPNO> <ENAME>John</ENAME> </ROW> <ROW> <EMPNO>413</EMPNO> <ENAME>Jane</ENAME> </ROW> <ROW> <EMPNO>968</EMPNO> <ENAME>Jack</ENAME> </ROW> ...
The row tag used for each row can be changed using the XMLFormat object.
XMLSequence() being a TABLE function, can be used to unnest the elements inside an XML document. If you have a XML documents such as:
<Department deptno="100"> <DeptName>Sports</DeptName> <EmployeeList> <Employee empno="200"> <Ename>John</Ename> <Salary>33333</Salary> </Employee> <Employee empno="300"> <Ename>Jack</Ename> <Salary>333444</Salary> </Employee> </EmployeeList> </Department> <Department deptno="200"> <DeptName>Garment</DeptName> <EmployeeList> <Employee empno="400"> <Ename>Marlin</Ename> <Salary>20000</Salary> </Employee> </EmployeeList> </Department>
stored in an XMLType table dept_xml_tab, you can use the XMLSequence() function to unnest the Employee list items as top level SQL rows:
CREATE TABLE dept_xml_tab OF XMLTYPE; INSERT INTO dept_xml_tab VALUES( xmltype('<Department deptno="100"> <DeptName>Sports</DeptName><EmployeeList> <Employee empno="200"><Ename>John</Ename><Salary>33333</Salary></Employee> <Employee empno="300"><Ename>Jack</Ename><Salary>333444</Salary></Employee> </EmployeeList></Department>'));INSERT INTO dept_xml_tab VALUES ( xmltype('<Department deptno="200">
<DeptName>Sports</DeptName><EmployeeList> <Employee empno="400"><Ename>Marlin</Ename><Salary>20000</Salary></Employee> </EmployeeList></Department>'));SELECT extractvalue(value(d),'/Department/@deptno') as deptno, extractvalue(value(e),'/Employee/@empno') as empno, extractvalue(value(e),'/Employee/Ename') as ename FROM dept_xml_tab d, TABLE(XMLSequence(extract(value(d),'/Department/EmployeeList/Employee'))) e;
This returns the following:
DEPTNO EMPNO ENAME --------------------------------- 100 200 John 100 300 Jack 200 400 Marlin 3 rows selected
For each row in table dept_xml_tab, the TABLE function is evaluated. Here, the extract() function creates a new XMLType instance that contains a fragment of all employee elements. This is fed to the XMLSequence() which creates a collection of all employees.
The TABLE function then explodes the collection elements into multiple rows which are correlated with the parent table dept_xml_tab. Thus you get a list of all the parent dept_xml_tab rows with the associated employees.
The extractValue() functions extract out the scalar values for the department number, employee number, and name.
XMLConcat() function concatenates all the arguments passed in to create a XML fragment. Figure 10-6 shows the XMLConcat() syntax. XMLConcat() has two forms:
XMLSequenceType, which is a VARRAY of XMLType and returns a single XMLType instance that is the concatenation of all of the elements of the varray. This form is useful to collapse lists of XMLTypes into a single instance.XMLType values and concatenates them together. If one of the value is null, it is ignored in the result. If all the values are NULL, the result is NULL. This form is used to concatenate arbitrary number of XMLType instances in the same row. XMLAgg() can be used to concatenate XMLType instances across rows.
This example shows how XMLConcat() returns the concatenation of XMLTypes from the XMLSequenceType:
SELECT XMLConcat(XMLSequenceType( xmltype('<PartNo>1236</PartNo>'), xmltype('<PartName>Widget</PartName>'), xmltype('<PartPrice>29.99</PartPrice>'))).getClobVal() FROM dual;
returns a single fragment of the form:
<PartNo>1236</PartNo> <PartName>Widget</PartName> <PartPrice>29.99</PartPrice>
The following example creates an XML element for the first and the last names and then concatenates the result:
SELECT XMLConcat ( XMLElement ("first", e.fname), XMLElement ("last", e.lname)) AS "result" FROM employees e ;
This query might produce the following XML document:
<first>Mary</first> <last>Martin</last> <first>John</first> <last>Smith</last>
XMLAgg() is an aggregate function that produces a forest of XML elements from a collection of XML elements. Figure 10-7 describes the XMLAgg() syntax, where the order_by_clause is:
ORDER BY [list of: expr [ASC|DESC] [NULLS {FIRST|LAST} ] ]
and number literals are not interpreted as column positions. For example, ORDER BY 1 does not mean order by the first column. Instead the number literals are interpreted just as any other literal.
As with XMLConcat(), any arguments that are null are dropped from the result. XMLAgg() function is similar to the SYS_XMLAGG() function except that it returns a forest of nodes, and does not take the XMLFormat() parameter. This function can be used to concatenate XMLType instances across multiple rows. It also allows an optional ORDER BY clause to order the XML values being aggregated.
XMLAgg() is an aggregation function and hence produces one aggregated XML result for each group. If there is no group by specified in the query, then it returns a single aggregated XML result for all the rows of the query. NULL values are dropped from the result.
The following example produces a Department element containing Employee elements with employee job ID and last name as the contents of the elements. It also orders the employee XML elements within the department by their last name.
SELECT XMLELEMENT("Department", XMLAGG( XMLELEMENT("Employee", e.job_id||' '||e.last_name) ORDER BY last_name)) as "Dept_list" FROM employees e WHERE e.department_id = 30; Dept_list ------------------------------------------------------------- <Department> <Employee>PU_CLERK Baida</Employee> <Employee>PU_CLERK Colmenares</Employee> <Employee>PU_CLERK Himuro</Employee> <Employee>PU_CLERK Khoo</Employee> <Employee>PU_MAN Raphaely</Employee> <Employee>PU_CLERK Tobias</Employee> </Department>
The result is a single row, because XMLAgg() aggregates the rows. You can use the GROUP BY clause to group the returned set of rows into multiple groups:
SELECT XMLELEMENT("Department", XMLAttributes(department_id AS deptno), XMLAGG(XMLELEMENT("Employee", e.job_id||' '||e.last_name))) AS "Dept_list" FROM employees e GROUP BY e.department_id; Dept_list --------------------------------------------------------- <Department deptno="1001"> <Employee>AD_ASST Whalen</Employee> </Department> <Department deptno="2002"> <Employee>MK_MAN Hartstein</Employee> <Employee>MK_REP Fay</Employee> </Department> <Department deptno="3003"> <Employee>PU_MAN Raphaely</Employee> <Employee>PU_CLERK Khoo</Employee> <Employee>PU_CLERK Tobias</Employee> <Employee>PU_CLERK Baida</Employee> <Employee>PU_CLERK Colmenares</Employee> <Employee>PU_CLERK Himuro</Employee> </Department>
You can order the employees within each department by using the ORDER BY clause inside the XMLAgg() expression.
|
Note: Within the |
XMLAgg() can be used to reflect the hierarchical nature of some relationships that exist in tables. The following example generates a department element for each department. Within this it creates elements for all employees of the department. Within each employee, it lists their dependents:
SELECT XMLELEMENT( "Department", XMLATTRIBUTES ( d.dname AS "name" ), (SELECT XMLAGG(XMLELEMENT ("emp", XMLATTRIBUTES (e.ename AS name), ( SELECT XMLAGG(XMLELEMENT( "dependent", XMLATTRIBUTES(de.name AS "name"))) FROM dependents de WHERE de.empno = e.empno ) )) FROM emp e WHERE e.deptno = d.deptno) ) AS "dept_list" FROM dept d ;
The query might produce a row containing the XMLType instance for each department:
<Department name="Accounting"> <emp name="Smith"> <dependent name="Sara Smith"/d> <dependent name="Joyce Smith"/> </emp> <emp name="Yates"/> </Department> <Department name="Shipping"> <emp name="Martin"> <dependent name="Alan Martin"/> </emp> <emp name="Oppenheimer"> <dependent name="Ellen Oppenheimer"/> </emp> </Department>
XMLColAttVal() is an Oracle SQLX extension function.
XMLColAttVal() function generates a forest of XML column elements containing the value of the arguments passed in. Figure 10-8 shows the XMLColAttVal() syntax.
The name of the arguments are put in the name attribute of the column element. Unlike the XMLForest() function, the name of the element is not escaped in any way and hence this function can be used to transport SQL columns and values without escaped names.
This example generates an Emp element for each employee, with a name attribute and elements with the employee's start date and department as the content.
SELECT XMLELEMENT("Emp",XMLATTRIBUTES(e.fname ||' '||e.lname AS "name" ), XMLCOLATTVAL ( e.hire, e.dept AS "department")) AS "result" FROM employees e;
This query might produce the following XML result:
<Emp name="John Smith"> <column name="HIRE">2000-05-24</column> <column name="department">Accounting</column> </Emp> <Emp name="Mary Martin"> <column name="HIRE">1996-02-01</column> <column name="department">Shipping</column> </Emp> <Emp name="Samantha Stevens"> <column name="HIRE">1992-11-15</column> <column name="department">Standards</column> </Emp>
Because the name associated with each XMLColAttVal() argument is used to populate an attribute value, neither the fully escaped mapping nor the partially escaped mapping is used.
DBMS_XMLGEN creates XML documents from any SQL query by mapping the database query results into XML. It gets the XML document as a CLOB or XMLType. It provides a "fetch" interface whereby you can specify the maximum rows and rows to skip. This is useful for pagination requirements in Web applications. DBMS_XMLGEN also provides options for changing tag names for ROW, ROWSET, and so on.
The parameters of the package can restrict the number of rows retrieved, the enclosing tag names. To summarize, DBMS_XMLGEN PL/SQL package allows you:
XMLType.fetch interface with maximum rows and rows to skip. For example, the first fetch could retrieve a maximum of 10 rows, skipping the first four. This is useful for pagination in Web-based applications.ROW, ROWSET, and so on.
The following shows a sample result from executing a "select * from scott.emp" query on a database:
<?xml version="1.0"?> <ROWSET> <ROW> <EMPNO>30</EMPNO> <ENAME>Scott</ENAME> <SALARY>20000</SALARY> </ROW> <ROW> <EMPNO>30</EMPNO> <ENAME>Mary</ENAME> <AGE>40</AGE> </ROW> </ROWSET>
The result of the getXML() using DBMS_XMLGen package is a CLOB. The default mapping is as follows:
ROW.ROWSET element. These names are both configurable, using the setRowTagName() and setRowSetTagName() procedures in DBMS_XMLGEN.ROW element.When the document is in a CLOB, it has the same encoding as the database character set. If the database character set is SHIFTJIS, then the XML document is SHIFTJIS.
Figure 10-9 summarizes the DBMS_XMLGEN calling sequence.
Here is DBMS_XMLGEN's calling sequence:
newContext() call.ROW element's name, use setRowTag(ctx), where ctx is the context got from the previous newContext() call.getXML() or getXMLType(). By setting the maximum rows to be retrieved for each fetch using the setMaxRows() call, you can call this function repeatedly, getting the maximum number of row set for each call. The function returns null if there are no rows left in the query.
getXML() and getXMLType() always return an XML document, even if there were no rows to retrieve. If you want to know if there were any rows retrieved, use the function getNumRowsProcessed().
closeContext() to free up any resource allocated inside.Table 10-1 summarizes DBMS_XMLGEN functions and procedures.
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>
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 DOM 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 -- now get the result result := dbms_xmlgen.getXML(qryCtx); -- if there were no rows processed, then quit..! exit when dbms_xmlgen.getNumRowsProcessed(qryCtx) = 0; -- do some processing with the lob data..! -- Here, we are inserting the results -- into a table. You can print the lob out, output it to a stream, -- put it in a queure -- 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.
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) ); / 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 ); / 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'); DBMS_XMLGEN.setRowTag(qryCtx, NULL); -- Here is the resulting XML: -- <ROWSET> -- <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>Mary</LAST_NAME> -- </EMP_T> -- </EMPLIST> -- </DEPTXML> -- <DEPTXML DEPARTMENT_ID="20"> -- ... -- </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 the previous 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:
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 FROM dept d'); -- 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; -- do whatever 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, 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> -- <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.
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 attributes 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 no */ Custno NUMBER constraint po_cust_fk references Customer_tab, /* Foreign KEY referencing customer */ 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 po in XML format create table po_xml_tab( poid number, poDoc XMLTYPE /* purchase order 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 PO in XML format -- and store XMLTYPE in po_xml table ------------------------------------------------------- declare qryCtx dbms_xmlgen.ctxHandle; pxml XMLTYPE; cxml clob; begin -- get the query context; qryCtx := dbms_xmlgen.newContext(' select pono,deref(cust_ref) customer,p.OrderDate,p.shipdate, lineitems_ntab lineitems,shiptoaddr from po p' ); -- set the 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 -- now get the po 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>
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; /
In addition to the SQL standard functions, Oracle9i provides the SYS_XMLGEN and SYS_XMLAGG functions to aid in generating XML.
This Oracle specific SQL function is similar to the XMLElement() except that it takes a single argument and converts the result to XML. Unlike the other XML generation functions, SYS_XMLGEN() always returns a well-formed XML document. Unlike DBMS_XMLGEN which operates at a query level, SYS_XMLGEN() operates at the row level returning a XML document for each row.
SYS_XMLGEN() creates and queries XML instances in SQL queries, as follows:
SELECT SYS_XMLGEN(employee_id) FROM employees WHERE last_name LIKE 'Scott%';
The resulting XML document is:
<?xml version=''1.0''?> <employee_id>60</employee_id>
SYS_XMLGEN() takes in a scalar value, object type, or XMLType instance to be converted to an XML document. It also takes an optional XMLFormat (the old name was XMLGenFormatType) object that you can use to specify formatting options for the resulting XML document. See Figure 10-10.
SYS_XMLGEN() takes an expression that evaluates to a particular row and column of the database, and returns an instance of type XMLType containing an XML document. The expr can be a scalar value, a user-defined type, or a XMLType instance.
XMLType instance, then the function encloses the document in an XML element whose default tag name is ROW.By default the elements of the XML document match the elements of expr. For example, if expr resolves to a column name, the enclosing XML element will have the same name as the column. If you want to format the XML document differently, specify fmt, which is an instance of the XMLFormat object.
In this release, the formatting argument for SYS_XMLGEN() accepts the schema and element name, and generates the XML document conforming to that registered schema.
SELECT sys_xmlgen( dept_t(d.deptno, d.dname, d.loc, cast(multiset( SELECT emp_t(e.empno, e.ename, e.job, e.mgr, e.hiredate,e.sal, e.comm) FROM emp e WHERE e.deptno = d.deptno) AS emplist_t), xmlformat.createformat('Department', 'http://www.oracle.com/dept.xsd')) FROM dept d;
The following example retrieves the employee email ID from the sample table oe.employees where the employee_id value is 205, and generates an instance of a XMLType containing an XML document with an EMAIL element.
SELECT SYS_XMLGEN(email).getStringVal() FROM employees WHERE employee_id = 205; SYS_XMLGEN(EMAIL).GETSTRINGVAL() ------------------------------------------------------------------ <EMAIL>SHIGGENS</EMAIL>
SYS_XMLGEN() is powerful for the following reasons:
SYS_XMLGEN() creates an XML document from either of the following:
and returns an XMLType instance contained in the document.
SYS_XMLGEN() also optionally inputs a XMLFormat object type through which you can customize the SQL results. A NULL format object implies that the default mapping behavior is to be used.
You can use XMLFormat to specify formatting arguments for SYS_XMLGEN() and SYS_XMLAGG() functions.
SYS_XMLGEN() returns an instance of type XMLType containing an XML document. Oracle9i provides the XMLFormat object, which lets you format the output of the SYS_XMLGEN function.
Table 10-2 lists the XMLFormat attributes. of the XMLFormat object. The function that implements this type follows the table.
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
When you input 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:
<?xml version="1.0"?> <EMPNO>30</EMPNO>
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.
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:
<?xml version="1.0"?> <ROW>60</ROW>
since 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.
For example, in the last case, if you wanted the result to have EMPNO as the tag name, you can supply a formatting argument to the function, as follows:
SELECT sys_xmlgen(empno *2, xmlformat.createformat('EMPNO')).getClobVal() FROM emp;
This results in the following XML:
<?xml version="1.0"?> <EMPNO>60</EMPNO>
When you input 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's 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 (Example 10-18, "DBMS_XMLGEN: Generating Complex XML"), you can generate a hierarchical XML for the employee, department example as follows:
SELECT SYS_XMLGEN( dept_t(deptno,dname, CAST(MULTISET( select empno, ename from emp e where e.deptno = d.deptno) AS emplist_t))).getClobVal() AS deptxml FROM 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, cast's 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:
<?xml version="1.0"?> <ROW DEPTNO="100"> <DNAME>Sports</DNAME> <EMPLIST> <EMP_T EMPNO="200"> <ENAME>John</ENAME> <EMP_T> <EMP_T> <ENAME>Jack</ENAME> </EMP_T> </EMPLIST> </ROW>
for each row of the department. The default name ROW is present because the function cannot deduce the name of the input operand directly.
If you pass an XML document into SYS_XMLGEN(), 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;
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>
|
Note: If the input was a column, then the column name would have been used as default. You can override the enclosing element name using the formatting object that can be passed in as an additional argument to the function. See "Using XMLFormat Object Type". |
-- 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>
SYS_XMLAGG() function aggregates all XML documents or fragments represented by expr and produces a single XML document. It adds a new enclosing element with a default name, ROWSET. To format the XML document differently then specify fmt, the instance of XMLFORMAT object
Oracle9i introduced XMLType for use with storing and querying XML-based database content. You can use these database XML features to produce XML for inclusion in your XSQL pages by using the <xsql:include-xml> action element.
The SELECT statement that appears inside the <xsql:include-xml> element should return a single row containing a single column. The column can either be a CLOB or a VARCHAR2 value containing a well-formed XML document. The XML document will be parsed and included in your XSQL page.
The following example uses nested xmlagg() functions to aggregate the results of a dynamically-constructed XML document containing departments and nested employees into a single XML "result" document, wrapped in a <DepartmentList> element:
<xsql:include-xml connection="orcl92" xmlns:xsql="urn:oracle-xsql"> select XmlElement("DepartmentList", XmlAgg( XmlElement("Department", XmlAttributes(deptno as "Id"), XmlForest(dname as "Name"), (select XmlElement("Employees", XmlAgg( XmlElement("Employee", XmlAttributes(empno as "Id"), XmlForest(ename as "Name", sal as "Salary", job as "Job") ) ) ) from emp e where e.deptno = d.deptno ) ) ) ).getClobVal() from dept d order by dname </xsql:include-xml>
Since it is more efficient for the database to aggregate XML fragments into a single result document, the <xsql:include-xml> element encourages this approach by only retrieving the first row from the query you provide.
For example, if you have a number of <Movie> XML documents stored in a table of XmlType called MOVIES, each document might look something like this:
<Movie Title="The Talented Mr.Ripley" RunningTime="139" Rating="R"> <Director> <First>Anthony</First> <Last>Minghella</Last> </Director> <Cast> <Actor Role="Tom Ripley"> <First>Matt</First> <Last>Damon</Last> </Actor> <Actress Role="Marge Sherwood"> <First>Gwenyth</First> <Last>Paltrow</Last> </Actress> <Actor Role="Dickie Greenleaf"> <First>Jude</First> <Last>Law</Last> <Award From="BAFTA" Category="Best Supporting Actor"/> </Actor> </Cast> </Movie>
You can use the built-in Oracle9i XPath query features to extract an aggregate list of all cast members who have received Oscar awards from any movie in the database using a query like this:
SELECT xmlelement("AwardedActors", xmlagg(extract(value(m), '/Movie/Cast/*[Award[@From="Oscar"]]'))) FROM movies m; -- To include this query result of XMLType into your XSQL page, -- simply paste the query inside an <xsql:include-xml> element, and add -- a getClobVal() method call to the query expression so that the result will -- be returned as a CLOB instead of as an XMLType to the client: <xsql:include-xml connection="orcl92" xmlns:xsql="urn:oracle-xsql"> select xmlelement("AwardedActors", xmlagg(extract(value(m), '/Movie/Cast/*[Award[@From="Oscar"]]'))).getClobVal() from movies m </xsql:include-xml>
|
Note: Again we use the combination of |
Failing to do this results in an attempt by the XSQL page processor to parse a CLOB that looks like:
<Actor>...</Actor> <Actress>...</Actress>
Which is not well-formed XML because it does not have a single document element as required by the XML 1.0 specification. The combination of xmlelement() and xmlagg() work together to produce a well-formed result like this:
<AwardedActors> <Actor>...</Actor> <Actress>...</Actress> </AwardedActors>
This well-formed XML is then parsed and included in your XSQL page.
| See Also:
Oracle9i XML Developer's Kits Guide - XDK, the chapter in "XDK for Java" on XSQL Page Publishing Framework. |
The Oracle XML SQL Utility (XSU) can still be used with Oracle9i to generate XML. This might be useful if you want to generate XML on the middle-tier or the client. XSU now additionally supports generating XML on tables with XMLType columns.
For example, if you have table, parts:
CREATE TABLE parts ( PartNo number, PartName varchar2(20), PartDesc xmltype );
You can generate XML on this table using Java with the call:
java OracleXML getXML -user "scott/tiger" -rowTag "Part" "select * from parts"
This produces the result:
<Parts> <Part> <PartNo>1735</PartNo> <PartName>Gizmo</PartName> <PartDesc> <Description> <Title>Description of the Gizmo</Title> <Author>John Smith</Author> <Body> The <b>Gizmo</b> is <i>grand</i>. </Body> </Description> </PartDesc> </Part> ... </Parts>
| See Also :
Oracle9i XML Developer's Kits Guide - XDK for more information on XSU |
|
 Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|