bewährt und vielfach im einsatz: oracle xml db
TRANSCRIPT
<Insert Picture Here>
Bewährt und vielfach im Einsatz: Oracle XML DB
Carsten Czarski
Oracle Deutschland B.V. & Co KG
Oracle XML DB Kurzprofil
• XML und SQL in einer Datenbank
• Standardkonform (W3C, SQL:2003...) • XML/SQL
• XQuery
• XML Schema, DOM
• Verfügbar ab Oracle 9i Release 2
• Alle Datenbankeditionen
• Keine zusätzliche Installation erforderlich
Oracle XML DB
Transformationen
Recherchen Relationale Sichten
XML- Sichten
SQL
FTP
HTTP / WebDAV
SQL*Net
XPath
XQuery
Oracle XML DB
XML DB Installation
• Überprüfen ob Funktionalität installiert
• Falls Funktionalität nicht installiert
1. XML DB Repository installieren mit $ORACLE_HOME/rdbms/admin/catqm.sql
2. Bei Bedarf: HTTP- und FTP-Listener aktivieren DBMS_XDB.SETFTPPORT DBMS_XDB.SETHTTPPORT
Ab Oracle12c ist
die XML DB immer
installiert.
SQL> select comp_id, version from dba_registry where comp_id='XDB';
COMP_ID VERSION
------------------------------ ------------------------------
XDB 12.1.0.1.0
Oracle XML DB Entwicklung der Funktionalität
Oracle8i • Datentyp XMLTYPE
• CLOB und O/R Speicherung
• SQL/XML-Funktionen
• Protokollserver FTP, HTTP
• XQuery
• Protokollserver: HTTPS
• SQL:2003
1998 2001 2003
Oracle9i
XML DB
• XML Developers Kit
2008
Oracle10g
XML DB
• Binary XML und XMLIndex
• DB Native Webservices
• PL/SQL Embedded Gateway
• Repository Events
Oracle11g
XML DB
2013
Oracle12c
XML DB
• XQuery Update und FullText
• Replication Support
• Parallel DML for XMLTYPE
• JSON
XML DB: Zugriffe
ftp> open 192.168.2.140 2100
Connected to 192.168.2.140.
220- cczarski-linux-140
Unauthorised use of this FTP server is prohibited and may be subject to
civil and criminal prosecution.
220 cczarski-linux-140 FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user xmldemo xmldemo
331 pass required for XMLDEMO
230 XMLDEMO logged in
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
drw-r--r-- 2 SYS oracle 0 AUG 09 15:48 home
drw-r--r-- 2 SYS oracle 0 AUG 10 11:14 i
drw-r--r-- 2 SYS oracle 0 AUG 30 17:57 public
drw-r--r-- 2 SYS oracle 0 AUG 09 15:48 sys
226 ASCII Transfer Complete
ftp: 238 bytes received in 0,03Seconds 7,93Kbytes/sec.
ftp>
SQL> select extract(object_value, '/nachricht/aktie').getstringval()
2 from "nachricht429_TAB" e;
XML_TITEL
-----------------------------------------------------------------------
<aktie xmlns="http://www.oracle.com/aktie/nachrichten.xsd" wkn="871460„
name="Oracle" reuters="ORCL" branche="Software"/>
<aktie xmlns="http://www.oracle.com/aktie/nachrichten.xsd" wkn="871460„
name="Oracle" reuters="ORCL" branche="Software"/>
<aktie xmlns="http://www.oracle.com/aktie/nachrichten.xsd" wkn="840400„
name="Allianz" reuters="ALV" branche="Versicherungen"/>
:
SQL>
XML DB Protokollserver Kontrolle
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 14-FEB-2014 09:18:18 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production Start Date 08-NOV-2013 14:02:54 Uptime 97 days 19 hr. 15 min. 24 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/12.1.0/db/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/sccloud017/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud017.de.oracle.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud017.de.oracle.com)(PORT=8080))(Presentation=HTTP)(Session=RAW)) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sccloud017.de.oracle.com)(PORT=2100))(Presentation=FTP)(Session=RAW)) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "pdb01" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "sample" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully
XML DB in der Oracle DB Dateien und Ordner im XML DB Repository
• File/Folder-Sicht auf eine Repository-Tabelle
• PL/SQL-Zugriff via DBMS_XDB
• Basis für FTP und WebDAV
• Zugriffskontrolle mit ACL
• Versionierung
• Links
• Metadatenverwaltung
XML DB Repository im SQL Developer
Datentyp XMLTYPE
• Verwendbar wie jeder andere Datentyp
• als Tabellenspalte,
• in PL/SQL Logik
• Entwickler-Schnittstelle (SQL-Funktionen)
• XMLQUERY, XMLTABLE
• UPDATEXML
• APPENDCHILDXML
• SCHEMAVALIDATE
• EXTRACT, EXTRACTVALUE
• ...
• Tabellen anlegen
CREATE TABLE order_tab( id number(10), order_doc xmltype ) xmltype column order_doc store as object relational xmlschema "http://xmldb.oracle.com/purchaseOrder" element "purchaseOrder"
• Beispiel: Objektrelationale Speicherung
create table xml_tab (doc xmltype) xmltype column doc store as [clob | object relational | binary xml]
Datentyp XMLTYPE
Beispiel-XML Tabelle PURCHASEORDER_TAB
Zugriff mit SQL Standardfunktion XMLTABLE
• SQL/XML Standard als Teil von SQL:2003
select
x.reference, x.username, x.costcenter
from Purchaseorder_tab p, xmltable(
'/PurchaseOrder'
passing xml_document
columns
reference varchar2(30) path '/PurchaseOrder/Reference',
username varchar2(30) path '/PurchaseOrder/User',
costcenter varchar2(10) path '/PurchaseOrder/CostCenter'
) x
Zugriff mit SQL Hierarchie vs. flache Tabellen
REFERENCE USERNAME COSTCENTER
------------------------------ ---------- ----------
ADAMS-20011127121051212PST ADAMS R20
ADAMS-20011127121044463PST ADAMS R20
ADAMS-20011127121044793PST ADAMS R20
ADAMS-20011127121045424PST ADAMS R20
ADAMS-20011127121045484PST ADAMS R20
ADAMS-20011127121046265PST ADAMS R20
ADAMS-2001112712104796PST ADAMS R20
: : :
• SQL-Funktion XMLTABLE: Ergebnis
Zugriff mit SQL Hierarchie vs. flache Tabellen
select x.reference, x.username, count(it.item_number) anz_items from Purchaseorder_tab p, xmltable( '/PurchaseOrder[CostCenter="A10"]' passing xml_document columns reference varchar2(30) path '/PurchaseOrder/Reference', username varchar2(30) path '/PurchaseOrder/User', lineitems xmltype path '/PurchaseOrder/LineItems/LineItem' ) x, xmltable( '/LineItem' passing x.lineitems columns item_number number path '/LineItem/@ItemNumber' ) it group by x.reference, x.username
• XMLTABLE "hintereinander" schalten
• Ein XMLTABLE pro Hierarchieebene
Zugriff mit SQL Hierarchie vs. flache Tabellen
REFERENCE USERNAME ANZ_ITEMS
------------------------------ ---------- ----------
CLARK-2001112712104736PST CLARK 7
CLARK-2001112712105385PST CLARK 21
CLARK-2001112712105263PST CLARK 17
CLARK-2001112712104168PST CLARK 40
CLARK-2001112712105223PST CLARK 5
CLARK-20011127121039575PST CLARK 8
CLARK-2001112712104919PST CLARK 26
• SQL-Funktion XMLTABLE: Ergebnis
Zugriff mit SQL-Funktionen Bearbeiten von XML-Dokumenten
• DML-Kommandos für XML
• updateXML()
• insertChildXML()
• appendChildXML()
• insertXMLbefore()
• deleteXML()
• Piecewise Updates = Performance!
• Bei objektrelationaler Speicherung
XMLTYPE Update Ab Oracle12c: XQuery Update
UPDATE purchaseorder_tab po
SET xml_document = XMLQuery(
'copy $i := $p1 modify (
for $j in $i/PurchaseOrder/Actions/Action[1]/User
return replace value of node $j with $p2
)
return $i'
PASSING xml_document AS "p1", 'SKING' AS "p2"
RETURNING CONTENT
)
WHERE XMLExists(
'$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING xml_document AS "p"
)
XMLTYPE Speicherungsformen Storage Clause einer XMLTYPE-Spalte
Oracle XML DB: Textbasiert Ein Blick hinter die Kulisse
SQL> select object_value from xml_text where rownum=1;
OBJECT_VALUE
------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-i
nstance" xsi:noNamespaceSchemaLocation="http:/localhost:9021
1 Zeile wurde ausgewählt.
SQL> select e.xmldata from xml_text e where rownum=1;
XMLDATA
------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-i
nstance" xsi:noNamespaceSchemaLocation="http:/localhost:9021
Oracle XML DB: Objektrelational Ein Blick hinter die Kulisse
SQL> select object_value from xml_or where rownum=1;
OBJECT_VALUE
------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-i
nstance" xsi:noNamespaceSchemaLocation="http:/localhost:9021
1 Zeile wurde ausgewählt.
SQL> select e.xmldata from xml_or e where rownum=1;
XMLDATA(SYS_XDBPD$, Reference, ACTIONS(SYS_XDBPD$, ACTION(SY
------------------------------------------------------------
XDBPO_TYPE(XDB$RAW_LIST_T('23FF01020084000088010035687474703
A2F6C6F63616C686F73743A393032312F7075626C69632F584D4C44454D4
F2F70757263686173654F726465722E787364000102030405060708'), '
ADAMS-20011127121044463PST', XDBPO_ACTIONS_TYPE(XDB$RAW_LIST
_T('1301000000'), ...
Objektrelationale Speicherung: XML Schema
XML Schema in der Oracle-Datenbank
• "Registrierung"
• PL/SQL Paket (DBMS_XMLSCHEMA)
• Oracle Enterprise Manager
• Nachbildung der XML-Strukturen
• Oracle Objekttypen
• XML-Speicherung als "Objekt"
Oracle XML DB: Binary XML Ein Blick hinter die Kulisse
SQL> select object_value from xml_binary where rownum=1;
OBJECT_VALUE
------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-i
nstance" xsi:noNamespaceSchemaLocation="http:/localhost:9021
1 Zeile wurde ausgewählt.
SQL> select e.xmldata from xml_binary e where rownum=1;
XMLDATA
------------------------------------------------------------
9F01039E000000B203000000030001787369C85DA1DD0001C03400156874
74703A2F6C6F63616C686F73743A393032312F7075626C69632F584D4C44
454D4F2F70757263686173654F726465722E787364C01956E14144414D53
2D3230303131313237313231303434343633505354C83A7CC81AAAC00403
3F53434F....
Speicherungsformen Entscheidungsbaum -I-
1. Zugriffe
Nur auf ganze Dokumente
2. XML Schema ...
Auf einzelne Dokumentknoten
<document id="2"> <element date="2007-0 <action> <member name="Ora :
Textbasierte
Speicherung
Speicherungsformen Entscheidungsbaum -II-
2. XML Schema
Nicht vorhanden
3. Flexibilität ...
vorhanden
110100100100111000100 100101010111010001110 111001000111100001111 100010011110100001000 111100011100011111001
Binary XML
Speicherungsformen Entscheidungsbaum -III-
3. Flexibilität
XML Schema wird sich häufig
ändern XML Schema
wird stabil sein
110100100100111000100 100101010111010001110 111001000111100001111 100010011110100001000 111100011100011111001
Binary XML Objektrelational
Speicherungsformen Zusammenfassung:
• Drei Fragen führen zur richtigen Speicherungsform
• Zugriffe auf ganze Dokumente oder auf Dokumentteile?
• Liegt ein XML Schema vor oder nicht?
• Wird sich das XML Schema ändern oder stabil bleiben?
• Richtige Speicherungsform ist entscheidend!
• Performance-Unterschied: Faktoren
• Durch Tuning (Index, I/O) nicht einzuholen
• Binary XML in den meisten Fällen richtig
• Sehr flexibel und performant
• Default für XMLTYPE ab 11.2.0.2
XML Indizieren
• Index für XML: XDB.XMLINDEX
• Für Storage: Textbasiert und Binary XML
• Nicht aber für objektrelationale Speicherung
hier wie schon immer: B-Tree Indizes
create index xml_index on purchaseorder_tab(object_value) indextype is xdb.xmlindex parameters ( 'PATHS (INCLUDE (/PurchaseOrder/User) (/PurchaseOrder/LineItems)' )
XML und Indizes Weitere Möglichkeiten
• B-Tree Index (Nur objektrelationale Speicherung)
• Indizierung einzelner Knoten oder Attribute
• Verhalten wie "normale" B-Tree Indizes; sehr performant
• Function Based Index (Alle Speicherungsformen)
• Indizierung von 1:n-Beziehungen nicht möglich
• Oracle TEXT-Index (Alle Speicherungsformen)
• Volltextrecherche, aber keine Bereichssuche
• Für "einfaches" Indizieren eines Knotens zu aufwändig
XQuery Full Text
• Oracle11g: Oracle TEXT zur Suche in XML-Dokumenten
• Section-Suche: Suche innerhalb gegebener XML-Tags
• SQL-Funktion CONTAINS (Oracle TEXT)
• XML_SECTION_GROUP, PATH_SECTION_GROUP
• Ab Oracle12c: XQuery Fulltext
• XML Datenmodell wird komplett unterstützt
• SQL Funktion XMLEXISTS (SQL:2003)
• Abfragesprache nun XQuery Fulltext und damit standardisiert!
XQuery Fulltext: Setup
• Oracle TEXT Index nötig
• Erstellen mit PATH_SECTION_GROUP und
XML_ENABLE=TRUE
begin ctx_ddl.create_section_group('my_sg_xquery', 'PATH_SECTION_GROUP'); ctx_ddl.set_sec_grp_attr('my_sg_xquery', 'xml_enable', 'true'); end; / sho err create index ft_tabxml on tab_xml (docs) indextype is ctxsys.context parameters ('section group my_sg_xquery') /
XQuery Fulltext: Abfragen
• Nutzung von XMLExists und XQuery-Syntax
• SQL/XML-Funktion XMLExists verwenden
• CONTAINS-Funktion (Oracle TEXT) nicht verwenden
• Kombination mit XQuery oder SQL/XML möglich
SELECT id FROM tab_xml WHERE XMLExists('declare namespace ns="http://mynamespaces.com/ns2"; //ns:tag[. contains text "text"]' PASSING docs)
XML erzeugen: In der Datenbank
XML-Schnittstellen SQL/XML Standard (SQL:2003)
• XMLElement()
• XMLForest()
• XMLAgg()
• XMLComment()
• XMLCDATA()
• XMLPI()
• XMLRoot()
• XMLSerialize()
XML-Dokumente erzeugen SQL/XML am Beispiel
create or replace view WERTPAPIER_XML_VIEW as select XMLElement("wertpapier", XMLAttributes( wp.isin as "isin", dp.kontonummer as "depotnummer" ), XMLElement("bezeichnung", wp.wp_bezeichnung), XMLElement("stueck_nominale", wp.amount) ) from wertpapier wp join depot dp using (depot_id)
• View auf Ebene der Wertpapiere
XML-Dokumente erzeugen SQL/XML am Beispiel
• Ergebnis ...
XML-Dokumente erzeugen SQL/XML am Beispiel
select XMLElement("kunde", XMLAttributes(kd.kunde_id as "kundennummer"), XMLForest( kd.kunde_name as "name", kd.kunde_vorname as "vorname", kd.kunde_ort as "ort" ), XMLComment('Generiert am '||to_char(sysdate)), ( select XMLAgg( XMLElement("depot", XMLAttributes(dp.kontonummer as "kontonummer"), ( select XMLAgg( :
• Komplexeres XML mit Hierarchie erzuegen
XML-Dokumente erzeugen SQL/XML am Beispiel
• Ergebnis ...
XML Views und das XML DB Repository Abrufen der Inhalte per HTTP oder FTP
• Virtuelle Dateien: Inhalte kommen aus View
XMLDIFF und XMPATCH "Original und Fälschung" ... für XML
• 1. Dokument ...
• 2. Dokument ...
<vortrag xmlns="oracle-sig" titel="Oracle XML DB: Immer noch unschlagbar"/>
<s:vortrag xmlns:s="oracle-sig" titel="Oracle XML DB: Immer noch unschlagbar"></s:vortrag>
Welche Unterschiede gibt es zwischen diesen Dokumenten ...?
Deltas in XML-Dokumenten erkennen Die Herausforderung ...
• XML: Das Gleiche unterschiedlich aussagen ...
• Namespace-Präfix
• XML-Tags ohne Inhalt
• Whitespace
• Entity-Referenzen
• Einfacher Textvergleich greift zu kurz!
<vortrag xmlns="oracle-sig"/>
<s:vortrag xmlns:s="oracle-sig"/> <vortrag></vortrag>
<vortrag/> <tag1/> <tag2/>
<vortrag> </vortrag> <vortrag>"</vortrag>
<vortrag>"</vortrag>
• SQL-Funktionen XMLDiff und XMLPatch
• Deltas erkennen (XMLDiff) ...
... und auf andere Dokumente anwenden (XMLPatch)
• Deltas zwischen Systemen übertragen ...
... mit Rücksicht auf XML-Besonderheiten!
XML-Deltas in erkennen Die Lösung: ... Mit SQL!
<xd:xdiff xsi:schemaLocation="http://.... <?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?> <!-- Leere Menge: Dokumente identisch--> </xd:xdiff>
XMLDiff: Ein anderes Beispiel Unterschiedliche XML-Dokumente (XML Schemas)
<xd:xdiff xsi:schemaLocation="http://.... <?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?> <xd:append-node xd:node-type="element" xd:parent-xpath= "/xs:schema[1]/xs:simpleType[6]/xs:restriction[1]" > <xd:content> <xs:enumeration value="E999" /> </xd:content> </xd:append-node> </xd:xdiff>
Weitere Informationen
• Oracle XML DB im OTN http://www.oracle.com/technetwork/database/features/xmldb/index.html
• Oracle Dokumentation
http://docs.oracle.com/
• Handbücher zur Oracle Database
• XML DB Developers Guide
• Blog: SQL und PL/SQL in Oracle http://sql-plsql-de.blogspot.com
[email protected] http://tinyurl.com/apexcommunity http://sql-plsql-de.blogspot.com http://oracle-text-de.blogspot.com http://oracle-spatial.blogspot.com http://plsqlexecoscomm.sourceforge.net http://plsqlmailclient.sourceforge.net Twitter: @cczarski @oraclebudb