Extrahieren von Daten aus XML-Instanzen mit SQL/XML
(Auszug aus "Oracle SQL: Das umfassende Handbuch", Kapitel 17 "XML-Abfragen", von Jürgen Sieben)
Den umgekehrten Weg wie bei der Erzeugung von XML gehen wir, wenn wir aus einer XML-Instanz wieder relationale Daten gewinnen möchten. In diesem Abschnitt bespreche ich die Funktionen rund um extract, ein alternativer Ansatz besteht mit der Funktion xmltable, die ich allerdings im Zusammenhang mit XQuery besprechen möchte, da sie diese Technologie verwendet.
Das Problem beim Extrahieren der Daten aus XML besteht darin, dass wir eine Gruppenfunktion entschachteln müssen. Sie erinnern sich, dass wir die Mitarbeiterliste durch die Funktion xmlagg erzeugt haben, eine Gruppenfunktion, die mehrere XML-Instanzen in ein übergeordnetes Element einschachteln kann. Die Gruppierung nach Abteilung hat dann ein einfaches group by erledigt. Nun müssen wir diese Gruppierung rückgängig machen. Dazu benötigen wir eine Funktion, die das kann, und diese Funktion heißt xmlsequence. Die Funktion xmlsequence erwartet ein XML-Fragment, dessen Wurzelelemente als eigenständige XML-Instanzen betrachtet werden sollen. Die Funktion liefert dann für jedes Kindelement eine XML-Instanz zurück. Das XML-Fragment wiederum liefert uns die Funktion extract, die aus ein einer XML-Instanz ein XML-Fragment extrahiert, das durch einen XPath bezeichnet ist. Sie kennen die Funktion extract bereits aus dem Zusammenhang mit Datumsangaben (extract (year from sysdate)), doch hier ist die Syntax anders, eher an den anderen XML-Funktionen orientiert. Wir haben wieder die XML-Instanz, den XPath-Ausdruck und den optionalen Namensraum als Parameter der Funktion.
Dann kommt etwas SQL-Magie, die wir allerdings schon gesehen hatten, denn wir verwenden die Funktion table, um diese einzelnen Zeilen der Funktion xmlsequence als Zeilen einer Tabelle zu interpretieren. Ich muss das im Zusammenhang erklären, weil dies nicht in Einzelschritten in SQL durchgeführt werden kann, sondern nur auf einmal.
Entschachteln wir also die XML-Instanz:
select v.*
from dept_emp_xml x,
table(
xmlsequence(
extract(x.liste, '//Mitarbeiter'))) v
where deptno = 10;
COLUMN_VALUE
-----------------------------------------------------
<Mitarbeiter einstellDatum="1981-06-09" id="7782"><Na...
<Mitarbeiter einstellDatum="1982-01-23" id="7934"><Na...
<Mitarbeiter einstellDatum="1981-11-17" id="7839"><Na...
3 Zeilen ausgewählt
Code-Beispiel: Entschachtelung der XML-Instanz
Sie sehen, dass wir wieder drei Zeilen mit jeweils einem Mitarbeiter erhalten. Das ist so, weil ich die Abfrage auf die Abteilung 10 limitiert habe. Lassen wir dies weg, haben wir wieder 14 Zeilen, denn alle Mitarbeiter aus den drei Mitarbeiterlisten sind damit entschachtelt worden. Wie schon bekannt, liefert uns die Funktion table eine Spalte mit dem Namen column_value zurück, die wir folgerichtig nun auch wieder sehen. Die entschachtelte Tabelle nenne ich v, so dass wir nun mit diesen Zeilen weiterarbeiten können.
Der nächste Schritt besteht darin, dass wir nun aus der Spalte v.column_value mit der Funktion extract oder extractValue die Informationen herauslesen, die uns interessieren. XML ist eine formatierte Textdatei, mehr nicht, daher wird uns lediglich der Datentyp varchar2 entgegenkommen. Wenn wir also unsere ursprünglichen Datentypen wiederhaben möchten, müssen wir diese durch Konvertierung herstellen. Ich zeige das Vorgehen einmal an einem einfachen Beispiel, dann folgt die gesamte Auswertung. Zunächst also extrahieren wir nun den Namen des Mitarbeiters aus den Zeilen:
select extractValue(v.column_value, '//Name') ename
from dept_emp_xml x,
table(
xmlsequence(
extract(x.liste, '//Mitarbeiter'))) v;
ENAME
---------
Clark
Miller
King
Smith
Ford
Adams
Scott
Jones
Allen
James
Turner
Blake
Martin
Ward
14 Zeilen ausgewählt.
Code-Beispiel: Extraktion des Mitarbeiternamens
Sie sehen, dass wir bei der Extraktion mit der Funktion extractValue (die uns den Elementwert und nicht das gesamte Element zurückliefert) die Spalte COLUMN_VALUE übergeben müssen. Der XPath bezieht sich auf dessen Wurzelelement Mitarbeiter und hieße korrekt eigentlich /Mitarbeiter/Name. Natürlich können wir den Ausdruck durch upper umschließen und so die Namen wieder in Versalien erzeugen. Hier nun also die komplette Abfrage zum Extrahieren der Daten, die in der XML-Instanz enthalten sind:
select upper(
extractValue(
v.column_value, '//Name')) ename,
upper(
extractValue(
v.column_value, '//Beruf')) job,
to_date(
extractValue(
v.column_value,
'/Mitarbeiter/@einstellDatum'),
'yyyy-mm-dd') hiredate,
to_number(
extractValue(
v.column_value, '//Gehalt'),
'999G990D00L') sal,
x.deptno
from dept_emp_xml x,
table(
xmlsequence(
extract(x.liste, '//Mitarbeiter'))) v;
ENAME JOB HIREDATE SAL DEPTNO
---------- ---------- --------- --------- ----------
CLARK MANAGER 09.06.81 2450 10
MILLER CLERK 23.01.82 1300 10
KING PRÄSIDENT 17.11.81 5000 10
SMITH CLERK 17.12.80 800 20
FORD ANALYST 03.12.81 3000 20
ADAMS CLERK 23.05.87 1100 20
SCOTT ANALYST 19.04.87 3000 20
JONES MANAGER 02.04.81 2975 20
ALLEN SALESMAN 20.02.81 1600 30
JAMES CLERK 03.12.81 950 30
TURNER SALESMAN 08.09.81 1500 30
BLAKE MANAGER 01.05.81 2850 30
MARTIN SALESMAN 28.09.81 1250 30
WARD SALESMAN 22.02.81 1250 30
14 Zeilen ausgewählt.
Code-Beispiel: Entschachtelung und Umformung der Daten aus den XML-Instanzen
Schön oder nicht schön, das ist hier eher nicht die Frage. Allerdings wird die Arbeit dadurch etwas erleichtert, dass viel mit Copy & Paste gearbeitet werden kann. Trotzdem existiert ein etwas hübscherer Weg über die Funktion xmltable, die im Zusammenhang mit XQuery besprochen werden wird. Habe ich schon erwähnt, dass man solche Abfragen gern auch in Views in der Datenbank hinterlegen kann? Würde in diesem Fall in eine Importtabelle eine XML-Instanz eingelesen, könnte eine einfache Anweisung, wie etwa
insert into target
select *
from my_new_view;
die Daten bereits extrahiert in relationale Tabellen übernehmen. Unser Beispiel ist natürlich aus didaktischen Gründen (oder höre ich da jemanden »aus Faulheit« murmeln?) einfach gewählt. Was passiert, wenn die XML-Instanz deutlich tiefer geschachtelt ist? Dann müssen weitere table-Funktionen auf die bereits teilweise entschachtelten XML-Fragmente ausgeführt werden. Dann wird das Ganze komplex. Dann habe ich keine Lust mehr, Ihnen das in einem SQL-Buch zu zeigen ... Immerhin bleibt das Prinzip immer gleich.
Ich sollte aber noch erwähnen, dass für die Konvertierung der Daten in andere Datentypen noch ein weiterer Weg zur Verfügung steht. Dieser Weg nutzt SQL/XML-Funktionen, genauer die Funktion xmlcast, um ein XML-Element in einen SQL-Typ umzuwandeln. Das geht, allerdings zum Teil mehr schlecht als recht. Sie können in der Abfrage oben gern den Aufruf der Funktion to_date ersetzen durch folgende Formulierung:
xmlcast(
extract(v.column_value, '/Mitarbeiter/@einstellDatum')
as date)
Doch ist das bereits alles: Die anderen Spalten benötigen upper immer noch, und das Gehalt ist so formatiert, dass die entsprechende Funktion es nicht erkennt. Da auch keine Möglichkeit besteht, der Funktion zu erklären, was sie dort sieht, macht die Funktion nur dann Sinn, wenn absolut saubere Standardformate für die Daten verwendet werden. Dann jedoch kann die Funktion xmlcast auch date, timestamp, number etc. erzeugen. Möchten Sie explizit clob oder blob aus einer XML-Instanz extrahieren, empfiehlt Oracle nicht mehr die hierfür existenten Funktion XMLType.GetClob(), sondern die SQL/XML-Funktion xmlserialize, die wir ja bereits kennen.
Eine Anmerkung zu kulturspezifischem XML
Hier, wie auch in anderem Zusammenhang, müssen wir beim Konvertieren von Zahlen aus XML in den Typ number darauf achten, dass XML bei Gleitkommazahlen einen Punkt als Dezimaltrennzeichen vorschreibt. Wird nun diese Angabe in einen number-Typ überführt, ohne eine Formatmaske anzugeben, und ist für die Session zum Beispiel Deutsch als Sprache angegeben, wird eine Fehlermeldung die Folge sein, denn nun hofft die Datenbank auf ein Komma als Dezimaltrennzeichen, was die XML-Instanz nicht liefert. Hier der Nachweis:
select extract(
xmltype(
'<Foo>3.15</Foo>'),
'/Foo/text()').getNumberVal() zahl
from dual;
'/Foo/text()').getNumberVal() zahl
*
FEHLER in Zeile 4:
ORA-01722: Ungültige Zahl
ORA-06512:
in "SYS.XMLTYPE", Zeile 181
Das Problem: Eine Zahl wird in XML nun einmal immer mit einem Punkt getrennt, ansonsten ist dies keine valide XML-Zahl und würde demzufolge vom Parser als nicht valide ausgesondert. Daher wäre es nett, die lokalen Trennzeichen würden für die Konvertierung nicht berücksichtigt. Aus meiner Sicht ist das ein Bug.
Ach, und wenn ich schon einmal beim Meckern bin: Warum hat der XMLType eigentlich keine getDateVal()-Funktion? Auch hierfür existiert ja ein definierter XML-Schema-Standard, daher sollte eine Implementierung möglich sein. Na ja, vielleicht im nächsten Release ...
Ich habe mir einige Suchanfragen auch etwas leicht gemacht, indem ich eine separate Spalte DEPTNO in der Tabelle vorgehalten habe. Alternativ können Sie XML-Instanzen auch dadurch auswählen, dass Sie einen Knotentest durchführen. Dieser Knotentest kann über die Funktion extractValue durchgeführt werden, obwohl das etwas hausbacken wirkt und mittlerweile durch andere Funktionen und Operatoren (insbesondere xmlexists) besser bewerkstelligt werden kann:
select liste
from dept_emp_xml
where extractValue(
liste,
'//Mitarbeiter[Name="Scott"]/Name')
= 'Scott';
LISTE
----------------------------------------------------
<Mitarbeiterliste abteilung="20">
<Mitarbeiter einstellDatum="1980-12-17" id="7369">
<Name>Smith</Name>
<Beruf>Clerk</Beruf>
<Gehalt>800,00€</Gehalt>
</Mitarbeiter>
<Mitarbeiter einstellDatum="1981-12-03" id="7902">
<Name>Ford</Name>
<Beruf>Analyst</Beruf>
<Gehalt>3.000,00€</Gehalt>
</Mitarbeiter>
<Mitarbeiter einstellDatum="1987-05-23" id="7876">
<Name>Adams</Name>
<Beruf>Clerk</Beruf>
<Gehalt>1.100,00€</Gehalt>
</Mitarbeiter>
<Mitarbeiter einstellDatum="1987-04-19" id="7788">
<Name>Scott</Name>
<Beruf>Analyst</Beruf>
<Gehalt>3.000,00€</Gehalt>
</Mitarbeiter>
<Mitarbeiter einstellDatum="1981-04-02" id="7566">
<Name>Jones</Name>
<Beruf>Manager</Beruf>
<Gehalt>2.975,00€</Gehalt>
</Mitarbeiter>
</Mitarbeiterliste>
Code-Beispiel: Filterung der XML-Instanz mit extractValue
Ich zeige das eher als Beispiel, denn xmlexists ist besser für diese Aufgaben geeignet. Allerdings atmet diese Funktion den Geist (und die Syntax) von XQuery und soll daher dort besprochen werden. Dann noch eine Information für alle, die sich dunkel an die Funktion existsNode erinnern oder diese in einer Abfrage gesehen haben: Sie ist seit Version 11g R2 als deprecated eingestuft und sollte ebenfalls durch xmlexists ersetzt werden. Den gleichen Status haben seit dieser Datenbankversion im Übrigen auch die Funktionen extract und extractValue, die ich in diesem Abschnitt benutzt habe. Diese Funktionen sollten nach Möglichkeit durch die neuen SQL/XML-Funktionen xmlquery und xmlcast ersetzt werden, die wir im nächsten Abschnitt besprechen. Ich habe mich dennoch entschlossen, diese Funktionen zu verwenden, einfach, weil sie weit in Verwendung sind und in Datenbanken vor Oracle 11g erforderlich sind (xmlcast existiert erst ab Version 11).
<< zurück | vor >> |
Tipp der data2type-Redaktion: Zum Thema Oracle & XML bieten wir auch folgende Schulungen zur Vertiefung und professionellen Fortbildung an: |
Copyright © Rheinwerk Verlag, Bonn 2013
Für Ihren privaten Gebrauch dürfen Sie die Online-Version ausdrucken.
Ansonsten unterliegt dieses Kapitel aus dem Buch "Oracle SQL: Das umfassende Handbuch" denselben Bestimmungen, wie die gebundene Ausgabe: Das Werk einschließlich aller seiner Teile ist urheberrechtlich geschützt. Alle Rechte vorbehalten einschließlich der Vervielfältigung, Übersetzung, Mikroverfilmung sowie Einspeicherung und Verarbeitung in elektronischen Systemen.
Rheinwerk Verlag GmbH, Rheinwerkallee 4, 53227 Bonn, www.rheinwerk-verlag.de, service(at)rheinwerk-verlag.de