XML aus relationalen Daten erzeugen

(Auszug aus "Oracle PL/SQL: Das umfassende Handbuch", Kapitel 16 "Arbeiten mit XML", von Jürgen Sieben)

Oracle bietet eine Vielzahl von Möglichkeiten, um aus relationalen (oder objektrelationalen) Daten XML zu erzeugen. Im Einzelnen sind dies:

• SQL/XML-Funktionen
• XQuery
• Migration von Objekttypen zu XML mittels dbms_xmlgen oder sys_xmlgen (deprecated ab Version 12c)
• händische Erzeugung über DOM-Baum-Programmierung
• händische Erzeugung über die Erzeugung einer XML-Zeichenkette und anschließendes Casting zu XMLType über einen Konstruktor

Ich werde Ihnen nicht alle Ansätze vorstellen, sondern mich auf die gängigsten Verfahren beschränken. Beachten Sie zudem den Kasten »Neuerungen in Version 12c«, falls Sie bereits jetzt eine Vorauswahl der für Sie besten Techniken treffen möchten.

Der SQL/XML-Standard

Eine der für viele erstaunlichsten Eigenschaften der Oracle-Datenbank in Bezug auf XML ist ihre Fähigkeit, mit einer einfachen XML-Abfrage dynamisch XML aus relationalen Daten zu erzeugen. Die dafür erforderlichen Zeilen- und Gruppenfunktionen stehen Oracle bereits seit Version 9 (und damit, das sollte man nicht vergessen, im Prinzip seit der Einführung von XML) zur Verfügung. Mittlerweile ist die Art, wie Oracle XML aus SQL erzeugt, in den ISO-SQL-Standard aufgenommen worden und wird so auch von IBM-DB2 und Microsoft SQL Server unterstützt. Sehen wir uns vielleicht ein erstes Beispiel an:

Eine einzelne Zeile sieht komplett (und etwas formatiert) so aus:

Code-Beispiel: Erzeugung von XML über den SQL/XML-Standard

Bevor wir uns dieses Beispiel genauer ansehen, sollten wir verstehen, dass die Funktionen aus dem SQL/XML-Standard ganz normale Zeilen- und Gruppenfunktionen sind. Sie entsprechen syntaktisch also einer Funktion lower oder sum und können ebenso verwendet werden, sogar in Kombination mit den »normalen« Zeilenfunktionen. Im Beispiel sehen wir, dass eine Funktion xmlelement offensichtlich für die Erzeugung der XML-Elemente erforderlich ist. Dieser Funktion können mehrere Parameter übergeben werden. Ich übergebe zwei Parameter: Als ersten Parameter übergebe ich das Ergebnis der Funktion xmlattributes und als zweiten das Ergebnis der Funktion xmlforest. Die erste Funktion erzeugt Attribute als direkte Kinder des übergeordneten Elements; xmlforest wiederum erzeugt eine Gruppe von Kindelementen unter dem Element, in dem sie aufgerufen wurde. Dann fällt uns auf, dass die Elementnamen durch die doppelten Anführungszeichen case-sensitive geworden sind, ganz so, wie wir das von Aliassen kennen. Interessant sind auch ein oder zwei Details: So ist es möglich (wie sonst in SQL ja auch), Zeilenfunktionen zu verschachteln. Ich habe das durchgeführt, als ich den Namen und den Beruf mit initcap und das Gehalt durch to_char formatieren ließ. Außerdem scheint es Oracle durchaus bekannt zu sein, auf welche Weise ein Datum XML-konform zu konvertieren ist, denn die Übergabe der Spalte hiredate erfolgt als date ohne Formatierungsmaske.

Exkurs: Datumsfunktionen in SQL/XML
Allerdings lauert hier ein Fallstrick: Die Funktionen aus dem Umfeld von SQL/XML stammen letztlich aus dem ISO-Standard. In diesem Standard enthält ein Datum keine Uhrzeit. Daher ist die einzige Konvertierung, die für eine Datumsspalte erlaubt ist, die XML-Formatmaske 'yyyy-mm-dd', eine eventuelle Uhrzeit entfällt. Beinhaltet Ihre Datumsspalte also die Uhrzeit, muss die Spalte entweder zunächst mit der Funktion to_timestamp in einen Zeitstempel umgewandelt oder mit to_char in exakt diese Formatmaske überführt werden: 'yyyy-mm-dd"T"hh24:mi:ss'. Überraschend ist vielleicht die Verwendung von "T": Der XML-Standard fordert, abweichend vom ISO-Standard, ein T zwischen Datum und Uhrzeit. Dieser Sondertext wird mit doppelten Anführungszeichen in die Formatmaske integriert.

Da wir diese Funktionen als Zeilenfunktionen für eine Tabelle aufrufen, ist es nicht verwunderlich, dass wir eine entsprechende Anzahl Zeilen zurückgeliefert bekommen. Jede enthält nun eine gültige Instanz des Typs XMLType und könnte somit jede der für diesen Typ definierten Typfunktionen aufrufen. Mit den Datenbankversionen 10 und 11 ist der Umfang der in SQL/XML enthaltenen Funktionen zur Erzeugung von XML deutlich angewachsen. Die Erweiterungen können Kommentare, Processing Instructions und Ähnliches erzeugen. Sie liegen etwas außerhalb des Fokus dieser Einführung und sollen lediglich erwähnt werden. Wichtiger sind allerdings die Gruppenfunktionen, mit deren Hilfe (wie bei normalen Gruppenfunktionen auch) aus mehreren Eingangszeilen wenige Ausgabezeilen erzeugt werden. Ich möchte zum Beispiel eine Mitarbeiterliste pro Abteilung in XML erzeugen. Dazu werden wir das Beispiel oben sukzessive so erweitern, dass am Ende das gewünschte Ergebnis erscheint.

Zunächst einmal erweitern wir unsere Ausgabe durch ein weiteres Element, das den Namen Mitarbeiterliste bekommen soll:

Diese Schachtelung hat noch keine Gruppierung zur Folge, es wird lediglich ein weiteres XML-Element um jede Zeile gelegt. Nun folgt der entscheidende Schritt:

Durch die Verwendung der Gruppenfunktion xmlagg werden die innerhalb dieser Funktion liegenden Ergebnisse zu einer Gruppe von XML-Elementen (einem XML-Fragment, da mehrere Elemente auf oberster Ebene vorhanden sind, was der XML-Syntax widerspricht) zusammengefasst und in das umgebende Element verschachtelt. Nun haben wir nur noch eine Zeile, nicht aber, wie eigentlich gefordert, eine Mitarbeiterliste pro Abteilung. Diese letzte Umwandlung hat jetzt aber nichts mehr mit SQL/XML, sondern mit einfachem SQL zu tun, denn die Gruppenfunktion wird über die group by-Klausel lediglich partitioniert. Um das Ergebnis zu verdeutlichen, werde ich zusätzlich noch die Abteilungsnummer als Attribut abteilung im äußeren XML-Element Mitarbeiterliste ausgeben:

Eine einzelne Zeile sieht formatiert nun wie folgt aus:

Code-Beispiel: Ein erweitertes Beispiel

Die Syntax ist zunächst etwas gewöhnungsbedürftig, doch stimmen Sie mir sicher zu, dass die Erzeugung von XML aus relationalen Daten mithilfe dieser Funktionen recht einfach zu bewerkstelligen ist. Immerhin benötigen wir keinerlei Programmierung, eine einfache select-Anweisung genügt, es werden auch keine objektrelationalen Typen mit allen Konsequenzen auf die Administration dieser Typen benötigt. Nach diesem Prinzip lassen sich zudem beliebig komplex geschachtelte XML-Instanzen erzeugen, indem die Erzeugung tiefer geschachtelter Elemente als (harmonisierte) Unterabfragen in die select-Anweisung integriert wird. Das Einzige, was Sie immer behalten sollten, ist der Überblick. Werden Ihre Abfragen umfangreicher, ist die Kontrolle über die Funktionsaufrufe, deren Attribute und Schachtelung und insbesondere über die ganzen Klammerebenen überlebenswichtig.

Im Skript zum Buch habe ich Ihnen eine select-Anweisung zum Test der Performance von SQL/XML beigefügt, die sehr große XML-Instanzen erzeugt. Meine Beobachtung in Version 10 war, dass diese Instanzen extrem langsam werden (die Beispielabfrage benötigte über fünf Minuten), doch in Version 11 und 12c ist die Performance stark gestiegen (die gleiche Abfrage benötigt nun etwa 20 Sekunden). Daher bietet sich dieser Weg bei zunehmend mehr Einsatzbereichen an.

Das Package »dbms_xmlgen« bzw. »sys_xmlgen«

Alternativ zum Ansatz über SQL/XML existiert das Oracle-Package dbms_xmlgen, mit dessen Hilfe ebenfalls XML-Instanzen erzeugt werden können. Dieses Package kann eine Tabelle generisch in XML umwandeln und liegt auch als weitgehend funktionsgleiche Funktion sys_xmlgen zur direkten Verwendung in select-Abfragen vor. In der einfachsten Variante bestünde die Möglichkeit, Daten einer Tabelle oder View mit diesem Package umformen zu lassen und das Ergebnis mit XSLT in die gewünschte Form zu bringen. Allerdings kann das Package auch mit Objekten umgehen und deren innere Struktur in strukturiertes XML überführen, was eventuell die Umformung mit XSLT überflüssig machen kann.

Erzeugung einfacher XML-Instanzen

Sehen wir uns ein einfaches Beispiel an. Dazu verwenden wir eine Abfrage wie etwa die folgende:

Die Ergebnisse dieser Abfrage sollen nun in XML überführt werden. Dazu benötigen wir einen PL/SQL-Block, der die Abfrage ausführt und das Ergebnis konvertiert:

Code-Beispiel: Verwendung des Packages »dbms_xmlgen«

Diese Standardverwendung zeigt noch nicht recht die gesamte Mächtigkeit des Packages, gibt aber einen Eindruck von der grundsätzlichen Arbeitsweise: Um das Abfrageergebnis wird ein Wurzelelement mit dem Namen ROWSET gelegt, und jede Zeile wird in ein Element ROW eingefasst. Um nun das Ergebnis »aufzuhübschen«, können zunächst die Elementnamen des Wurzelelements und des Zeilenelements eingestellt werden, und auch die Spaltenbezeichnungen, die momentan als Kindelemente des ROW-Elements vergeben werden, können über Spaltenaliasse benannt werden. Sehen wir uns eine solche optimierte Ausgabe an:

In dieser Variante sieht das Ganze für mich schon besser aus. Erweitern wir die Funktionalität durch die Möglichkeit, Spalten als Attribute zu erzeugen. Dies geschieht, indem dem Spaltenalias ein @-Zeichen vorangestellt wird:

Code-Beispiel: Ein verfeinertes Beispiel der Verwendung von »dbms_xmlgen«

Achten Sie darauf, dass dieses Package, im Gegensatz zu den entsprechenden SQL/XML-Funktionen, Datumsangaben nicht korrekt in XML übernimmt, sondern die Standardformatierung Ihrer Session verwendet, wenn Sie keine explizite Konvertierungsfunktion angeben.

Erzeugung von XML-Instanzen aus objektrelationalen Typen

Eine weitere Möglichkeit, um geschachteltes XML aus relationalen Daten zu erzeugen, besteht darin, die Daten hierarchisch durch objektrelationale Typen vorzubereiten. Sie können sich einen objektrelationalen Typ ja als ein »in die Tiefe« geschachteltes Objekt vorstellen, in dem ein Attribut eine Tabelle weiterer Attribute enthält etc. Wenn wir mit Hilfe von SQL einen solchen objektrelationalen Typ vorbereiten und an das Package dbms_xmlgen übergeben, wie oben gezeigt, erhalten wir ebenfalls eine geschachtelte XML-Instanz mit den geschilderten Möglichkeiten. Allerdings ist dies nicht ganz einfach und auch teilweise in seinen Funktionen begrenzt. Ich zeige Ihnen hier eine Variante. Sehen wir uns zunächst einmal die Deklarationen der objektorientierten Typen an:

Code-Beispiel: Ein Objekt zur Vorbereitung der Erstellung von XML

Der Typ "Objekt", den wir als Erstes erstellt haben, stellt die Ebene dar, die später als innerste Ebene in XML vorhanden ist. Die etwas unübliche Verwendung der doppelten Hochkommata sorgt dafür, dass die spätere XML-Instanz diese Bezeichner als Attribut oder Kindelement umwandelt, zudem ist sie dafür zuständig, die XML-Elemente case-sensitive zu bezeichnen. Sie soll ein Objekt des Data Dictionarys darstellen. Eine Liste der Objekte eines Benutzers wird als Typ "Objektliste" dargestellt. Schließlich nimmt ein Objekt des Typs "Eigentuemer" den Namen des Eigentümers und die Liste der Objekte auf. Wenn Sie sich die Deklarationen der Objekttypen näher anschauen, erkennen Sie, auf welche Weise ich die Aliasse einsetze, um Attribute und Elementnamen zu steuern.

Mit einer SQL-Abfrage können wir nun die relationalen Daten einer Tabelle in eine solche Objektstruktur überführen:

Code-Beispiel: Erstellung einer objektrelationalen View

Der Höhepunkt dieser Anweisung ist sicherlich die Kombination cast(multiset(...) as "Objektliste"). Lesen Sie diese Anweisung so: »Nimm eine Liste (multiset) von "Objekt"-Instanzen, und überführe sie (cast) in eine Instanz des Typs "Objektliste«. Beachten Sie, dass ich die View derzeit auf den aktuell angemeldeten Benutzer (also Scott) eingeschränkt habe, um zunächst einmal die Datenmengen zu kontrollieren. Nachdem wir diese Komplexität in einer Datensicht gekapselt haben, können wir diese nun einfach abfragen. Die Ausgabe ist im SQL Developer eher verhalten (orace.sql.STRUCT@1c1b57 in meinem Fall), doch in SQL*Plus (und mit etwas Formatierung) können wir grundsätzlich erkennen, worum es geht:

Code-Beispiel: Darstellung des Objekttyps in SQL*Plus

Wir sehen eine Liste von ineinandergeschachtelten Konstruktorfunktionen, die jeweils einen entsprechenden Datentyp zurückliefern. Nun können wir uns vorstellen, dass eine Ausgabe dieses Datentyps, nachdem dbms_xmlgen ihn in die Mangel genommen hat, auch in XML möglich ist:

Code-Beispiel: Erzeugung von XML aus dem Objekt

In diesem Fall habe ich mich entschlossen, das Ergebnis als XMLType erzeugen und in unsere XML-Test-Tabelle einfügen zu lassen. Ansonsten ist an dem PL/SQL-Block nichts, was Sie nicht schon kennen, sodass ich Ihnen direkt das Ergebnis zeigen kann:

Code-Beispiel: Das Ergebnis – XML aus einem Objekt

Das resultierende Ergebnis entspricht im Grundzug den Instanzen, die wir durch SQL/XML erzeugt haben, allerdings mussten wir Objekttypen erzeugen und PL/SQL bemühen. Das wäre kein wesentliches Problem, wenn die Performance so überragend ist, dass dieser Weg schon von daher erforderlich ist. Doch leider ist die Sache so einfach nicht: Meine Tests und die Erfahrungen, die ich in Projekten sammeln konnte, zeichnen für die Datenbankversion 11gR2 etwa folgendes Bild: Sind die XML-Instanzen groß (je nach Ausstattung der Datenbankmaschine, vielleicht ist etwa 1 MB als Grenze realistisch für erste Tests), ist die Erzeugung von XML durch dbms_xmlgen schneller und ressourcen-schonender als SQL/XML oder XQuery. Umgekehrt verhält es sich allerdings, wenn sehr viele kleine XML-Instanzen erzeugt werden müssen, wie das im Umfeld von nachrichtenbasierten Systemen häufig der Fall ist. Hier ist umgekehrt SQL/XML/XQuery schneller und ressourcen-schonender. Wie alle Hinweise auf die Performance ist dies von sehr vielen Parametern abhängig. Diese Empfehlung hat sich auch erst mit Datenbankversion 11.2 so dargestellt, vorher war SQL/XML fast immer langsamer als der objektorientierte Weg. Da Oracle für die neue Version 12c den Weg der XML-Erstellung über Objekte als deprecated einstuft (zumindest die Funktion sys_xmlgen), darf erwartet werden, dass die Performance von SQL/XML auch weiterhin verbessert werden wird.

Erzeugung von XML-Instanzen aus hierarchischen Abfragen

Eine besondere Fähigkeit dieses Packages ist es, mit hierarchischen Abfragen umgehen zu können. Diese Umwandlung ist mit SQL/XML meines Wissens nicht zu machen, nutzt aber SQL/XML, um das Ausgabeergebnis zu berechnen. Diese Funktion ist besonders interessant, wenn Sie zum Beispiel Baumstrukturen mit XML-Daten füllen müssen (denken Sie vielleicht an ein HTML TreeView Control):

Code-Beispiel: Erstellung von XML aus hierarchischen Abfragen

Zunächst sehen wir wiederum, wie bei der Verwendung des Packages dbms_xmlgen ein Kontext zur Identifikation der Abfrageumgebung genutzt wird. Bei der Verwendung der Funktion newContextFromHierarchy in Zeile 15 übergeben wir einerseits die Pseudospalte level der hierarchischen Abfrage und andererseits eine Instanz des XML-Elements, das eingeschachtelt werden soll. Sozusagen als Ausgleich ist dann aber keine Festlegung für rowSetTag und rowTag mehr erforderlich. Das Ergebnis ist ein korrekt geschachteltes XML, inklusive korrekt sortierter Geschwisterknoten etc.

Paginierung von XML-Abfragen

Eine wichtige weitere Eigenschaft des Packages dbms_xmlgen ist, dass es Ihnen ermöglicht, eine Abfrage zu paginieren und das Ergebnis in XML umzuformen. Die Problematik besteht darin, von einer bekannten Ergebnismenge nur die n-te bis m-te Zeile in XML zu konvertieren. Um diese Ausgabe zu erhalten, können die Prozeduren setMaxRows und setSkipRows verwendet werden. Das Schöne daran ist, dass die Ergebnismenge, die durch den Kontext repräsentiert ist, erhalten bleibt, sodass die Frage der Konsistenz der Daten beim iterativen Abfragen gelöst ist: Sie können neue Teilmengen als XML liefern, ohne die zugrunde liegende Abfrage erneut ausführen zu müssen. Ich denke, dass ich auf ein Beispiel dazu verzichten kann, denn die Vorgehensweise ist identisch mit den vorangegangenen Beispielen. Es werden lediglich vor dem Erzeugen der XML-Instanz mithilfe der Methoden die Anzahl und die erste Zeile definiert. Um zu erfragen, wie viele Zeilen beim letzten Aufruf tatsächlich verarbeitet wurden, können Sie die Funktion getNumRowsProcessed verwenden. Mit dieser Funktion können Sie also zum Beispiel prüfen, ob noch weitere Zeilen enthalten sein werden, denn wenn beim letzten Aufruf die Anzahl der verarbeiteten Zeilen kleiner ist als die Anzahl der angeforderten Zeilen, dann wissen Sie, dass keine weiteren Zeilen mehr in der Ergebnismenge existieren. Denken Sie in diesem Zusammenhang bitte auch an die neuen Funktionen der Version 12c zur Limitierung der Ergebnismenge mittels der neuen row limiting-Klausel (offset und fetch n rows).

»SYS_XMLGEN«

Lassen Sie mich diesen Abschnitt mit der Funktion sys_xmlgen beschließen. Sie stellt einen Zugriff auf das Package dbms_xmlgen aus SQL heraus dar und vereinfacht die Erstellung von XML aus Objekten, da Sie den Aufruf des Packages dbms_xmlgen aus dem Code-Beispiel "Das Ergebnis – XML aus einem Objekt" vermeiden und XML mit Hilfe dieser Funktion direkt in SQL erzeugen können:

Code-Beispiel: Erzeugung von XML über Objekte in SQL

Es besteht zudem noch die Möglichkeit, ein spezielles XMLFormat-Objekt zu übergeben, das im Groben die gleichen Einstellungen wie die Parameter des Packages enthält. Details dazu finden Sie in der Online-Dokumentation, hier möchte ich sie nicht mehr besprechen, da diese Funktion mit Version 12c als deprecated eingestuft ist.