XML in Oracle - SkillBuilders | Oracle Database, APEX Experts

66 Pages·2008·0.31 MB·English

1.1
1
XML in Oracle
A practical introduction to the primary
features of Oracle XDB.
Presented be Geoff Wiland.
Based on article from Oracle by Tim Quinlan
www.skillbuilders.com
V2.1
1.2
2
Agenda
(cid:190) Brief history (cid:190) XQuery, XPATH, &
FLOWR
(cid:190) Intro XML DB
(cid:190) XML DB Repository
(cid:190) SQL/XML Standard
(cid:190) Converting XML to
(cid:190) Structured vs
Relational Data
Unstructured XML
Storage (cid:190) Converting Relational to
XML Data
(cid:190) Namespaces
(cid:190) XML Performance
(cid:190) Schema Typed vs
Tuning
Untyped Data
(cid:190) 11g New Features
(cid:190) Schemas & Validation
www.skillbuilders.com
V2.1
1.3
3
A Bit of History
(cid:190)Starting a few years ago, lots of XML entering
databases
(cid:190)No formal vendor tools or solution
(cid:190)Had to store, query, and update XML data as
unstructured LOBs
(cid:190)Alternatively, “shred” data into relational
tables
(cid:190)Then put it back together
www.skillbuilders.com
When stored as LOBs, we had to use the DBMS_LOB package to process the XML data.
V2.1
1.4
4
Enter XML DB
(cid:190)1st delivered Oracle 9i Database Release 2
(cid:190)Built-in features for XML data
(cid:190)Storing as unstructured LOBs
(cid:190)Retrieving
(cid:190)Manipulating XML data in the database
(cid:190)Oracle 10g Release 2 XML DB greatly
extends features
www.skillbuilders.com
V2.1
1.5
5
Do I Have XML DB?
(cid:190)XML DB included with XE, SE and EE
(cid:190)Check if installed:
SQL> select comp_name, status, schema
SQL> select comp_name, status, schema
2 from dba_registry
2 from dba_registry
3 where comp_name like '%XML%'
3 where comp_name like '%XML%'
4 /
4 /
COMP_NAME STATUS SCHEMA
COMP_NAME STATUS SCHEMA
-------------------- ----------- ------
-------------------- ----------- ------
Oracle XML Database VALID XDB
Oracle XML Database VALID XDB
@xmldb
@xmldb
www.skillbuilders.com
@xmldb
set linesize 150
set pagesize 32000
column comp_name format a20
column schema format a6
select comp_name, status, schema
from dba_registry
where comp_name like '%XML%';
COMP_NAME STATUS Schema
-------------------- ----------- ------
Oracle XML Database VALID XDB
V2.1
1.6
6
SQL/XML Standard
(cid:190) SQL/XML ANSI ISO standard
(cid:190)See www.sqlx.org
(cid:190) SQL/XML defines a standardized mechanism for
using SQL and XML together
(cid:190) Store XML in RDBMS
(cid:190) Query with XQuery and XPath
(cid:190) Generate XML from SQL query / relational data
(cid:190) 10g R2 implements SQL/XML 2003
(cid:190)Plus some features…
www.skillbuilders.com
V2.1
1.7
7
XMLType
(cid:190)Use XMLType to create table, column ,view
(cid:190)Built-in methods to create, extract, index XML
data
(cid:190)PL/SQL, Java and .NET support
(cid:190)Store as
(cid:190)Unstructured – single LOB column
(cid:190)Structured – set of objects
SQL> create table test_xml of XMLtype
SQL> create table test_xml of XMLtype
2 XMLType store as CLOB;
2 XMLType store as CLOB;
@test_xml
@test_xml Table created.
Table created.
www.skillbuilders.com
@test_xml
drop table test_xml;
create table test_xml of XMLtype
XMLType store as CLOB;
desc test_xml
Name Null? Type
----------------------- -------- ----------------
TABLE of SYS.XMLTYPE
V2.1
1.8
8
Structured Storage…
(cid:190)Implemented as set of objects
(cid:190)Document is – in effect – stored as a “virtual”
document through the relational tables
(cid:190)Maintains Document Object Model (DOM)
fidelity
(cid:190)(But not a byte-for-byte representation of doc)
(cid:190)Creates an XMLType view over existing
relational data
www.skillbuilders.com
V2.1
1.9
9
…Structured Storage
(cid:190)Performance advantages
(cid:190)No tags, so reduced memory & storage
(cid:190)Granular retrieval
(cid:190)Better b-tree, function-based indexing
(cid:190)In-place, granular updates using XPath rewrite
(cid:190)Disadvantages
(cid:190)Increased insert, select overhead for whole doc
(cid:190)Not a byte-for-byte copy of original doc
(cid:190)Order of data in doc not maintained
www.skillbuilders.com
V2.1
1.10
10
Unstructured Storage
(cid:190)CLOB storage
(cid:190)Advantages
(cid:190)Match byte-for-byte original document
(cid:190)Can perform better when read or update entire doc
(cid:190)Disadvantages
(cid:190)Extra overhead to update small piece of doc
(cid:190)Constraints cannot be implemented
(cid:190)Less efficient memory management
www.skillbuilders.com
V2.1