XML Import in Oracle / XML nach Relational (Oracle 11.2.0.2)

Aktuell landete dieses Thema auf meinem Tisch:

Aus XML-Dateien sollen die Daten ausgelesen werden und in relationale Tabellen geschrieben werden.

Und um es etwas interessanter zu machen, waren die 16 XML-Dateien zusammen 28GB groß (das größte Einzelfile rd. 3,5GB).
Das Schema bestand aus 7 xsd-Dateien, die mit vielen choice Definitionen die objekt-relationale Umsetzung fast verhinderten.

Nach einigem Doku lesen, fleissiger Online-Suche und Forenanfragen entstand folgende Lösung:

1) Oracle 11.2.0.2
Das Patchset ist wichtig, da es hier nochmal Verbesserungen zur XML-Verarbeitung gab.

2) Tabelle vom Typ XMLType
Für ETL reicht securefile binary xml, da die Daten nicht geändert werden sollen.
Ein Schema muss nicht angegeben werden (vereinfacht die Verarbeitung komplexer Schemata).
Wichtig sind die virtual columns. Hierüber wird indiziert, was die späteren Abfragen drastisch beschleunigt.
Bsp:
CREATE TABLE blabla of XMLTYPE
xmltype store as securefile binary xml
virtual columns
(
bsnr as (xmlcast(xmlquery('
declare namespace ehd="urn:ehd/001";(::)
declare namespace vda="urn:ehd/vda/001";(::)
/ehd:ehd/ehd:body/vda:bsnr/@EX'
passing object_value returning content) as number (9)))
, z_id as (xmlcast(xmlquery('
declare namespace ehd="urn:ehd/001";(::)
declare namespace vda="urn:ehd/vda/001";(::)
/ehd:ehd/ehd:body/vda:bsnr/vda:fall/vda:z_id/@EX'
passing object_value returning content) as varchar2(100 char)))
);


Die virtual columns werden per XQuery beschrieben.
Über declare werden Namespaces eingebunden. Das (::) macht den Namespace auch für SQLPlus lesbar.
/ehd:ehd/.../@EX ist der absolute Pfad zum Element (relative Pfade für Performance besser vermeiden).
Über object_value wird bei einer table of xmltype der Tabelleninhalt angesprochen.
XMLCast wandelt den Wert in einen Oracledatentyp um.

3) SQL*Loader Control-File
Zum Einlesen z.B. den SQL*Loader verwenden - am besten als direct load:
sqlldr usr/pwd@db control.ctl direct=Y

Beispiel eines Controlfiles zum Import von Daten in eine XMLTYPE-Tabelle:
Load data
infile *
into table XML_EHD_FALL_IMPORT
truncate
xmltype(XMLDATA)
(
filename filler char(120),
XMLDATA lobfile(filename) terminated by eof
)
BEGINDATA
myXML-File.xml


4) Auslesen der Daten
Das Auslesen und Einfügen in eine relationale Tabelle erfolgt per XMLTable

INSERT INTO blabla2 ( seq, a, b, c)
SELECT b.seq, b.a, b.b, b.c
  FROM blabla t
     , XMLTABLE ('
declare namespace ehd="urn:ehd/001";(::)
declare namespace vda="urn:ehd/vda/001";(::)
for $f in /ehd:ehd/ehd:body/vda:fall
  for $l in $f/vda:patient/vda:lanr
return <b>
 <z>{data($f/vda:z_id/@EX)}</z>
 <l>{data($l/@EX)}</l>
 <hf>{data($l/vda:ha_fa_kennung/@V)}</hf>
</b>'
PASSING t.object_value
COLUMNS seq FOR ORDINALITY
  , a  VARCHAR2 (100 CHAR) PATH 'z'
  , b  NUMBER (7) PATH 'l'
  , c  NUMBER (1) PATH 'hf'
 ) b;


XMLTable übernimmt die meiste Arbeit. Das PASSING t.object_value übernimmt die Daten aus Tabelle blabla (zur Verdeutlichung hier mit t benannt), führt die XQuery-Abfrage aus und konvertiert die Werte in Oracle-Datentypen. Diese können über ein simples INSERT in jede relationale Tabelle eingefügt werden.
Neu ist seq FOR ORDINALITY - dies zählt die Ergebniszeilen - hilfreich für Prüfungen.

Die meiste Zeit hat mich der Versuch gekostet, das Schema zu registrieren. Hierfür gabs einige Unterstützung aus dem Forum (Link) - dafür vielen Dank!

Für die konkrete Umsetzung hat mir letztlich die Internetsuche
(insbesondere http://searchoracle.techtarget.com/tip/Oracle-XML-capabilities-in-Oracle-Database-11gR2)
in Verbindung mit der 11.2 Doku weitergeholfen.

Kommentare

  1. Vielen Dank für die ausführlichen Informationen! War mir eine große Hilfe.

    AntwortenLöschen
  2. Freut mich, wenn ich weiterhelfen konnte :-)

    AntwortenLöschen

Kommentar veröffentlichen

Beliebte Posts aus diesem Blog

PGA unter Oracle 11g

trunc(sysdate) - nette Spiele mit dem Datum

Datapump - Verzeichnis erstellen