Oracle XMLDB An introduction into Oracles XMLDB Architecture

1
Oracle XMLDBAn introduction into Oracles XMLDB
Architecture
2
Who Am I

• Started with Oracle 7 on Windows NT 3.1 (1994)
• Experienced with Oracle 7.x / 8.x / 9.x / 10.x
  and 11.1
• Started with Oracle XMLDB in 2004 (Oracle 9.2,
  10.1)
• Oracle 11g Beta tester on Oracle XMLDB (Storage)
• Active Oracle OTN XMLDB Forum Member
• Sometimes in direct contact with Mark Drake and
  his XMLDB Development team, which can be very
  helpful

3
Overview
4
The Oracle XML Infrastructure

5
Oracle XDK - XMLDB

• Oracle XML Developer's Kit (XDK)
• Is a versatile set of components that enables you
  to build and deploy C, C, and Java software
  programs that process XML
• The XDK has been there since Oracle 8i
• Inside and Outside the database
• Oracle XMLDB
• Oracle XML DB is the name for a set of Oracle
  Database technologies related to high-performance
  XML storage and retrieval
• Is officially supported since version 9.2.0.3.0

6
Oracle XML Milestones / Importance
11g
XDB
10gR2
10g
9iR2
XDK
9i
8i
XMLType XSQL
XQuery
Performance Robustness
Binary XML XMLIndex, NDWS
7
XML Data Handling

• XML data exchange
• SQL, C, C, Java, PL/SQL, .Net, etc
• Via Protocol Listener supported methods
• Via Oracle Advanced Queuing methods (Oracle
  11gR1)
• XML data storage
• CLOB, Object Relational, Binary XML (11gR1)
• XML data validation
• XMLSchema, Programmatically, via Storage
  definitions
• XML data creation
• XML Operators, Packages

8
Based on (XML) Standards

• World Wide Web Consortium (W3)
• XML, XML Schema, XSL, XSLT, XPath, XQuery,
• (11gR1) XLink, XInclude
• International Standard Organisation (ISO)
• SQL/XML
• Internet Engineering Task Force (IETF)
• FTP, HTTP(s), WebDAV

9
XMLDB Architecture
SQLNet Protocol Server Thin, Thick Clients
XMLDB Funtionality
10
Major Components XMLDB

• Storage based on the XMLType datatype
• XMLType Column, XMLType Table
• Retrieval of data via XML/SQL, XPath, XQuery
• Security based on ACL, Oracle roles
• The Protocol Server
• Native Database WebServices (NDWS)
• HTTP(s), FTP, WebDAV, NFS (11gR2?)
• The XMLDB Repository
• XMLSchema support, evolution

11
XMLType Storage
Logical Design Level
Physical Design Level
12
XMLType(Datatype, Query, Storage)
13
XMLType

• Type that can be used to store and query XML data
  in the database
• Has member functions that can be used to access,
  extract, and query the XML data
• XMLType values can be created by SQL functions
  and PL/SQL packages
• You can use it as an argument of a function or as
  the datatype of a table or view column
• Create tables and views of XMLType

14
Store and Query XML?
SQLgt create table XMLDATA 2 (store
xmltype) Table created. SQLgt describe
XMLDATA Name Null? Type
---------------- -------- -------------- STORE
PUBLIC.XMLTYPE
15
Store XML Data (01)
SQLgt describe OE.CUSTOMERS Name
Null? Type ----------------- --------
-------------------- CUSTOMER_ID
NOT NULL NUMBER(6) CUST_FIRST_NAME
NOT NULL VARCHAR2(20) CUST_LAST_NAME
NOT NULL VARCHAR2(20) ... SQLgt select
XMLTYPE('ltCUST_FIRST_NAMEgt' 2
CUST_FIRST_NAME 3
'lt/CUST_FIRST_NAMEgt') as XML Data" 4 from
OE.customers where rownum 1 XML
Data ------------------------------------------- lt
CUST_FIRST_NAMEgtIshwaryalt/CUST_FIRST_NAMEgt 1 row
selected.
A String
16
Store XML Data (02)
SQLgt INSERT into XMLDATA 2 select
xmltype('ltCUST_FIRST_NAMEgt' 3
CUST_FIRST_NAME 4
'lt/CUST_FIRST_NAMEgt') 5 from
OE.customers SQLgt select from XMLDATA 2
where rownum 1 STORE ------------------------
------------------------ ltCUST_FIRST_NAMEgtIshwarya
lt/CUST_FIRST_NAMEgt
XMLType data
17
Query XML Data
SQLgt SELECT from XMLDATA 2 WHERE
extractvalue(store,'/CUST_FIRST_NAME/text()')
like 'M' STORE ----------------------
-----------------------ltCUST_FIRST_NAMEgtMarkuslt/CU
ST_FIRST_NAMEgt ltCUST_FIRST_NAMEgtMary
Bethlt/CUST_FIRST_NAMEgt ltCUST_FIRST_NAMEgtMatthewlt/C
UST_FIRST_NAMEgt ltCUST_FIRST_NAMEgtMaureenlt/CUST_FIR
ST_NAMEgt ltCUST_FIRST_NAMEgtMaureenlt/CUST_FIRST_NAME
gt ltCUST_FIRST_NAMEgtMauricelt/CUST_FIRST_NAMEgt ltCUST
_FIRST_NAMEgtMauricelt/CUST_FIRST_NAMEgt ltCUST_FIRST_
NAMEgtManilt/CUST_FIRST_NAMEgt ...
XPATH Expression
18
Store and Query XML Data
SQLgt INSERT into XMLDATA 2 (store) 3 SELECT
xmlelement("CustFirstName",CUST_FIRST_NAME) 4
from OE.customers SQLgt select 2 from
xmldata 3 where extractvalue(store,'//text()')
'Kiefer' STORE --------------------------------
------------------ ltCUST_FIRST_NAMEgtKieferlt/CUST_F
IRST_NAMEgt ltCustFirstNamegtKieferlt/CustFirstNamegt
Value, Table Column
XML Element
19
XMLTypeOperators and Functions
20
Statement of Direction XML Packages?

• Mark Drake in response on a question about
  DBMS_XMLGEN (generation of XML via Packages)
• Re dbms_xmlgen gt tags in upper case
• Posted Oct 19, 2007 556 AM in response to
  user545923
• ------------------------------------------------
  --------------------------------------------------
  --------------------------------------------
• We have not focused on DBMS_XMLGEN performance
  or functionality since 9.2.x. All of our
  development efforts are now focused on the
  SQL/XML operators.
• http//forums.oracle.com/forums/thread.jspa?messag
  eID21453372145337

21
XML Operators Functions in 11gR1

• XMLAGG
• XMLCAST
• XMLCDATA
• XMLCOLATTVAL
• XMLCOMMENT
• XMLCONCAT
• XMLDIFF
• XMLELEMENT
• XMLEXISTS
• XMLFOREST

XMLPARSE XMLPATCH XMLPI XMLQUERY XMLROOT XMLSEQUEN
CE XMLSERIALIZE XMLTABLE XMLTRANSFORM
DELETEXML EXTRACTVALUE EXISTNODE EXTRACT INSERTCHI
LDXML INSERTXMLBEFORE SYS_XMLAGG SYS_XMLGEN UPDATE
XML
22
XMLTABLE
SQLgt select extract(A.store,'//string-len
gth(name()) lt 14 ') as "Element" 2 ,
B."Customer" 3 from xmldata A 4 ,
XMLTABLE('//string-length(name()) lt 14 ' 5
PASSING A.store 6
COLUMNS "Customer" path '//') B 8
Element
Customer ---------------------------------------
---------- ltCustFirstNamegtIshwaryalt/CustFirstNamegt
Ishwarya ltCustFirstNamegtGustavlt/CustFirstNamegt
Gustav ltCustFirstNamegtMarkuslt/CustFirstNamegt
Markus
length lt 14
23
XMLQUERY
SQLgt select warehouse_name, XMLQUERY('for
i in /Warehouse where i/Area gt
10000 return
ltDetailsgtltDocks num"i/Docks"/gt
ltRailgt if (i/RailAccess "Y") then
"true" else "false lt/Railgt
lt/Detailsgt' PASSING warehouse_spec
RETURNING content) BIG from
warehouses WAREHOUSE_NAME
BIG --------------------- ---------------------
Southlake, Texas ltDetailsgt
ltDocks num"2"/gt
ltRailgtfalselt/Railgt
lt/Detailsgt
24
XMLType Storage
25
XMLType Storage

• XMLType Column or XMLType Table
• Can be XML Schema controlled
• CLOB, Object-Relational, Binary XML (Physical
  Storage)
• XMLIndex
• Specially crafted Index for the XML domain
• Oracle 11g
• Oracle 11g storage options
• Securefile Encryption, Compressed, Deduplication
• New Validation Options
• Equi-Partitioning for XML data

26
XMLType COLUMN (Binary Storage)
SQLgt CREATE TABLE XMLBIN_COLUMN 2 (test
XMLTYPE) 3 XMLTYPE COLUMN "TEST" STORE AS
BINARY XML 4 TABLE created.
SQLgt CREATE TABLE XMLBIN_COLUMN_XSD 2 (test
XMLTYPE) 3 XMLTYPE COLUMN "TEST" STORE AS
BINARY XML 4 XMLSCHEMA "http//localhost/public
/root.xsd" 5 ELEMENT "ROOT" 6
TABLE created.
27
XMLType TABLE (Binary Storage)
SQLgt CREATE TABLE XMLBIN_TABLE of XMLType 2
XMLTYPE store AS BINARY XML TABLE created.
SQLgt CREATE TABLE XMLBIN_TABLE_XSD of XMLType 2
XMLTYPE store AS BINARY XML 3 XMLSCHEMA
"http//localhost/public/root.xsd" 4 ELEMENT
"ROOT" TABLE created.
XML Schemas in Binary Storage definitions much be
registered for binary use in the repository
28
Register XML Schema (for Binary Use)
SQLgt BEGIN DBMS_XMLSCHEMA.registerSchema
(SCHEMAURL gt 'http//localhost/public/xsd/my
app.xsd', SCHEMADOC gt xdbURIType('/public
/root.xsd').getClob(), LOCAL gt
FALSE, -- local GENTYPES gt FALSE, --
generate object types GENBEAN gt FALSE,
-- no java beans GENTABLES gt FALSE, --
generate object tables FORCE gt
FALSE, OPTIONS gt DBMS_XMLSCHEMA.REGISTE
R_BINARYXML, OWNER gt USER)
END /
29
The Protocol Server
30
The Protocol Server Architecture

• Build on Shared (Multi Threaded) Server
  Technology
• Inhouse build by the XMLDB Development Team
• Behaviour controlled by xdbconfig.xml

31
Connect Access Protocols
32
The Repository
33
New Possibilities via Protocol Server
WebDAV
FTP
HTTP(s)
34
Repository Features (General)

• Owned by the XDB schema
• XML, XML Schema Support
• Media Support (for example Exchangeable Image
  File (Exif))
• XML Schema Evolution
• Query, DDL, DML via SQL, PL/SQL, Java, and C
• Security via ACL and Oracle Roles / Privileges
• Link between the Relational and XMLDB Database
  World
• Views RESOURCE_VIEW, PATH_VIEW
• Tables xdbresource, xdbenum_t, xdbh_link

35
Demo Time
36
Questions?

• marco.gralike_at_amis.nl
• http//www.amis.nl