More Oracle XML DB

More XPath and XMLTYPE API

SELECT extract(value(X),'/PurchaseOrder//Part')

FROM XMLTABLE X;

SELECT extract(value(X),'/PurchaseOrder//User')

FROM XMLTABLE X;

CREATE TABLE po_xml_tab(

poid number,

poDoc XMLTYPE);

set long 2000

SELECT e.poDoc.getClobval() AS poXML

FROM po_xml_tab e

WHERE e.poDoc.existsNode(’/PO[PNAME = "po_2"]’) = 1;

-- This SQL statement extracts the purchase order name “PNAME” from purchase order

-- element PO, from all XML documents containing a shipping address with a

-- purchase order number of 300, and a customer name “CUSTNAME” containing the

-- string “John”.

SELECT e.poDoc.extract('/PO/PNAME/text()').getStringVal() PNAME

FROM po_xml_tab e

WHERE e.poDoc.existsNode('/PO/SHIPADDR') = 1 AND

e.poDoc.extract('//PONO/text()').getNumberVal() = 300 AND

e.poDoc.extract('//@CUSTNAME').getStringVal() like '%John%';

Consider the following XML doc:

<EMP>

<EMPNAME>Joe</EMPNAME>

<SALARY>50000</SALARY>

</EMP>

<EMP>

</EMP>

<EMP>

</EMP>

</EMPLOYEES>

To generate a new document with Joe’s salary updated to 100,000, update the Name

of Jack to Jackson, and modify the Employee element for 217, to remove the salary

element. You can write a query such as:

SELECT UPDATEXML(emp_col,

'/EMPLOYEES/EMP[EMPNAME="Joe"]/SALARY/text()', 100000,'//EMP[EMPNAME="Jack"]/EMPNAME/text()','Jackson',

'//EMP[EMPNO=217]',XMLTYPE.CREATEXML('<EMP<EMPNO>217</EMPNO<EMPNAME>Jane</EMPNAME>'))

FROM emp_tab e;

This generates the following updated XML:

<EMP>

</EMP>

<EMP>

</EMP>

<EMP>

<EMPNAME>Jackson</EMPNAME>

</EMP>

</EMPLOYEES>

NULL Updates with updateXML()

Consider the XML document:

</shipAddr>

</PO

The clause:

updateXML(xmlcol,'/PO/shipAddr',null)

is equivalent to making it:

</PO

Creating Views Using updateXML()

A view such as:

CREATE VIEW new_emp_view

AS SELECT

UPDATEXML(emp_col, '/EMPLOYEES/EMP/SALARY/text()', 0) emp_view_col

FROM emp_tab e;

ensures that users selecting from view, new_emp_view, do not see the SALARYfield for any employee.

Creating a Function-Based Index on an extract() Function

For example, to speed up the search on the query,

SELECT * FROM po_xml_tab e

WHERE e.poDoc.extract(’//PONO/text()’).getNumberVal()= 100;

you can create a function-based index on the extract() function as follows:

CREATE INDEX po_index ON po_xml_tab

(poDoc.extract('//PONO/text()').getNumberVal());

The SQL query uses this function-based index, to evaluate the predicate instead of

parsing the XML document row by row, and evaluating the XPath expression.

Creating, Adding, and Dropping XMLType Columns

Creating XMLType: Creating XMLType Columns

CREATE TABLE warehouses(

warehouse_id NUMBER(3),

warehouse_spec XMLTYPE,

warehouse_name VARCHAR2(35),

location_id NUMBER(4));

CREATE TABLE po_xml_tab(

poid number,

poDoc XMLTYPE);

-- this creates a table of XMLType. The default

-- is CLOB based storage.

CREATE TABLE po_xtab of XMLType;

Adding XMLType Columns

ALTER TABLE po_xml_tab add (custDoc XMLType);

Dropping XMLType Columns

ALTER TABLE po_xml_tab drop (custDoc);

Inserting Values into an XMLType Column

Inserting into XMLTYpe Using the XMLType() Constructor

INSERT INTO warehouses VALUES

( 100, XMLType(

’<Warehouse whNo="100">

<Building>Owned</Building>

</Warehouse>’), ’Tower Records’, 1003);

Using XMLType in an SQL Statement

Using XMLType and in a SELECT Statement

SELECT

w.warehouse_spec.extract('/Warehouse/Building/text()').getStringVal()

"Building"

FROM warehouses w;

Updating an XMLType Column

Updating XMLType

UPDATE warehouses SET warehouse_spec = XMLType

(’<Warehouse whono="200">

<Building>Leased</Building>

</Warehouse>’);

Deleting a Row Containing an XMLType Column

Deleting an XMLType Column Row

DELETE FROM warehouses e

WHERE e.warehouse_spec.extract(’//Building/text()’).getStringVal()

= ’Leased’;

Generating XML from Oracle 9i Database

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.

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.

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:

-- ID result

------

-- 1001 <Emp>John Smith</Emp>

-- 1206 <Emp>Mary Martin</Emp>

XMLElement(): Generating Nested XML

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>

</Emp>

<Emp>

<name>Mary Martin</name>

</Emp>

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.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

------

XMLElement(): Generating an Element from a UDT

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.

<DEPT_T DEPTNO="100">

<DNAME>Sports</DNAME>

<EMP_T EMPNO="200">

<EMP_T>

</EMP_T>

</EMPLIST>

</DEPT_T>

</Department>

XMLForest() functionproduces a forest of XML elements from the given list of

arguments.

XMLForest(): Generating Elements for Each Employee with Name Attribute, Start Date, and Dept as Content

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:

<department>Accounting</department>

</Emp>

<department>Shipping</department>

</Emp>

XMLForest(): Generating an Element from an UDT

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.

<DNAME>Sports</DNAME>

<EMP_T EMPNO="200">

</EMP_T>

<EMP_T>

</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.

XMLSequence(): Generating One XML Document from Another

Suppose you had the following XML document containing employee information:

<EMP>

</EMP>

<EMP>

</EMP>

<EMP>

<EMPNO>412</EMPNO>7

</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:

<EMP>

</EMP>

<EMP>

</EMP>

</EMPLOYEES>

Notice how XMLExtract() was used to extract out all the employees:

1. XMLExtract() returns a fragment of EMP elements.

2. XMLSequence() creates a collection of these top level elements into XMLType

instances and returns that.

3. The TABLE function was then used to makes the collection into a table value

which can be used in the FROM clause of queries.

XMLConcat() function concatenates all the arguments passed in to create a XML

fragment.

XMLConcat(): Returning a Concatenation of XML Elements Used in

the Argument Sequence

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:

<PartName>Widget</PartName>

XMLConcat(): Returning XML Elements By Concatenating the

Elements in the Arguments

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:

<last>Martin</last>

<last>Smith</last>

XMLAgg() function is an aggregate function that produces a forest of XML elements

from a collection of XML elements.

XMLAgg(): Generating Department Elements with a List of Employee

Elements

This example produces department elements, with the list of employees

belonging to that department:

SELECT XMLELEMENT( "Department", XMLATTRIBUTES ( e.dept AS "name" ),

XMLAGG (XMLELEMENT ("emp", e.lname))) AS "dept_list"

FROM employees e

GROUP BY dept ;

This query might produce the following XML result:

<emp>Yates</emp>

<emp>Smith</emp>

</Department>

<emp>Oppenheimer</emp>

<emp>Martin</emp>

</Department>

XMLAgg(): Generating Department Elements, Employee Elements Per

Department, and Employee Dependents

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.

</emp>

</Department>

</emp>

</emp>

</Department>

XMLColAttVal() function generates a forest of XMLcolumn elements containing

the value of the arguments passed in.

XMLColAttVal(): Generating an Emp Element Per Employee with

Name Attribute and Elements with Start Date and Dept as Content

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:

<column name="department">Accounting</column>

</Emp>

<column name="department">Shipping</column>

</Emp>

<column name="department">Standards</column>

</Emp>

Generating XML from Oracle9i Database Using DBMS_XMLGEN

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.

Sample DBMS_XMLGEN Query Result

The following shows a sample result from executing a “select * from scott.emp”

query on a database:

<?xml version="1.0"?>

<ROW>

<ENAME>Scott</ENAME>

</ROW>

<ROW>

</ROW>

</ROWSET>

The result of the getXML() using DBMS_XMLGen package is a CLOB. The default mapping is as follows:

Every row of the query result maps to an XML element with the default tag name ROW.
The entire result is enclosed in a ROWSET element. These names are both configurable, using the setRowTagName() and setRowSetTagName()procedures in DBMS_XMLGEN.
Each column in the SQL query result, maps as a subelement of the ROW element.
Binary data is transformed to its hexadecimal representation.

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''?>

<ENAME>SMITH</ENAME>

<JOB>CLERK</JOB>

</EMPLOYEE>

<ENAME>ALLEN</ENAME>

<JOB>SALESMAN</JOB>

</EMPLOYEE>

...

</ROWSET>