Managing Oracle XML DB
------------------------


Storing XML in Oracle XML DB:

SQL> CREATE TABLE sales_catalog_table
 (sales_num number(18),
 sales_order xmltype);

Here are some of the important SQL/XML operators:
• Extract() extracts a subset of the nodes contained in the XMLType.
• ExistsNode() checks whether a certain node exists in the XMLType.
• Validating() validates the XMLType contents against an XML schema.
• Transform() performs an XSL transformation.
• ExtractValue() returns a node corresponding to an XPath expression.


Inserting an XML Document into an Oracle Table:
SQL> INSERT INTO sales_catalog_table
2 VALUES (123456,
3 XMLTYPE(
4 '
5 Alapati - 200302201428CDT
6
7
8 Nina U. Alapati
9 ALAPATI
10 Dallas
11
12 Bicycle Courier
13
14
15 Expert Oracle DB Administration
16
17
18
19* '));
1 row created.



Viewing XML Data Stored in an Oracle Table:
SQL> SELECT sales_order FROM
2 sales_catalog_table;

Alapati - 200302201428CDT


Sam R. Alapati
ALAPATI
Dallas

Bicycle Courier


Expert Oracle DB Administration






SQL> SELECT extractValue(s.sales_order,'/SalesOrder/Requestor')
 FROM sales_catalog_table s
 WHERE existsNode(s.SALES_ORDER,
'/SalesOrder[SalesLocation="Dallas"]') = 1;


Creating a Relational View from an XML Document
--------------------------------------------------
Even if a developer doesn’t know much XML, he or she can use the XML documents stored in the
Oracle database by creating relational views based on the XML documents. The following example
maps nodes in an XML document to columns in a relational view called salesorder_view:

SQL> CREATE OR REPLACE VIEW salesorder_view
2 (requestor,description,sales_location)
3 AS SELECT
4 extractValue(s.sales_order,'/SalesOrder/Requestor'),
5 extractValue(s.sales_order,'/SalesOrder/Sales_Location')
6* FROM sales_Catalog_Table s ;
View created.
SQL>
You can query salesorder_view like you would any other view in an Oracle database, as shown
here:
SQL> SELECT requestor,sales_location FROM salesorder_view;



Query XML in SQL (Example 2)
------------------------------
create table acc_comm_log
(
acc_no number,
comm_details xmltype
);


Now, insert some records into it.
--------------------------------
insert into acc_comm_log
values
(
1,
xmltype(
'
EMAIL
3/11/2005
Dear Mr Smith')
)
/

insert into acc_comm_log
values
(
2,
xmltype(
'
LETTER
3/12/2005
Dear Mr Jackson')
)
/

insert into acc_comm_log
values
(
3,
xmltype(
'
PHONE
3/10/2005
Dear Ms Potter')
)
/


Now you can see what records are in the table:
-----------------------------------------------
select * from acc_comm_log;


SQL>
select acc_no,
XMLQuery(
'for $i in /CommRecord
where $i/CommType != "EMAIL"
order by $i/CommType
return $i/CommDate'
passing by value COMM_DETAILS
returning content) XDetails
from acc_comm_log
/

ACC_NO XDETAILS
---------- ------------------------------
1
2 3/12/2005
3 3/10/2005


Above example using XMLTable
------------------------------
The other function, XMLTable, has a similar purpose but returns the columns as if it were a regular SQL query. Here it is in action.
1 select t.column_value
2 from acc_comm_log a,
3 xmltable (
4 'for $root in $date
5 where $root/CommRecord/CommType!="EMAIL"
6 return $root/CommRecord/CommDate/text()'
7 passing a.comm_details as "date"
8* ) t
SQL> /

COLUMN_VALUE
---------------------
3/12/2005
3/10/2005


XQuery versus XMLTable
-----------------------
The first method, XQuery, allows you to to get the data in an XMLType, which can be manipulated as XML in any program or application that
supports it. In the example you saw, the resultant output of account data is in XML format and you can use any tool, not necessarily relational, to
manipulate and display that data. The second method, XMLTable, combines the functionality of regular SQL and XML. The resultant output of the
account data is not XML, but relational.


References:

http://oracle-base.com/articles/misc/sqlxml-sqlx-generating-xml-content-using-sql.php
http://oracle-base.com/articles/9i/xmltype-datatype.php
http://oracle-base.com/articles/8i/parse-xml-documents-8i.php