Start

Unternehmen

Business Solutions

Business Intelligence

Software-Technologien

Technologie-Beratung

Individual-Software

Comelio GmbH
Berlin
Fon: +49(0)30-3640339-80
Fax: +49(0)30-3640339-89
info@comelio.com

Comelio GmbH
Essen
Fon: +49(0)201-437517-0
Fax: +49(0)201-437517-10
info@comelio.com

Comelio GmbH
München
Fon: +49(0)89-38156860-0
Fax: +49(0)89-38156860-9
info@comelio.com

Comelio GmbH
Zürich
info@comelio.com

Comelio-Blog > Oracle > SysXMLGen

XML-Abfragen mit SysXMLGen

Sofern die als einfache Spaltenfunktionen vorhandenen SQLX-Funktionen nicht mehr ausreichen, um aus relationalen Daten XML-Strukturen zu erzeugen, existieren in Oracle weitere Funktionen. Sie erleichtern auch sehr tief verschachtelte Abfragen bei besserer Lesbarkeit als bei SQLX, erfordern allerdings individuelle Datentypen innerhalb der Datenbank, welche die Erstellung von ad-hoc-Abfragen im Gegensatz zu SQLX deutlich erschweren. Dieser Artikel stellt die SYS_XMLGEN-Funktionen vor und beschreibt, wie sich auch komplexe und weiterhin gut lesbare Abfragen realisieren lassen.

Kontakt Anrede* Herr Frau Vorname* Nachname* Firma E-Mail* Tel-Nr. Bereich* Comelio-Blog > Oracle > SysXMLGen Freitext

Oracle XDB: Oracle-spezifische SQL-Funktionen für XML

Neben den SQLX-Funktionen, die überaus schwergängig zu bedienen scheinen, wenn man andere Techniken kennt, besitzt Oracle weitere Funktionen, die allerdings spezifisch für dieses Datenbanksystem sind. Mit ihnen lassen sich ebenfalls Ergebnisse in XMLType-Form erzeugen. Die Aggregation lässt sich dann über Objekt- und Tabellentypen realisieren. Dies erleichtert die Lesbarkeit der Abfrage im Gegensatz zu den SQLX-Funktionen, erfordert allerdings mehr Planung auf der Datenbankseite, weil natürlich die entsprechenden Typen vorhanden sein müssen.

Im Gegensatz zu DBMS_XMLQUERY und DBMS_XMLGEN lassen sich diese Funktionen direkt aus SQL heraus aufrufen.

Erzeugung von einfachen Elementen

In einer ersten Variante erzeugt man mit SYS_XMLGEN() für jeden Ausdruck ein Element mit dem Inhalt des Ausdrucks. Dies kann ein Spaltenwert oder auch eine vorgegebene Zeichenkette sein, die zusätzlich auch mit Funktionen kombiniert werden können:

SELECT SYS_XMLGEN(K_Nr) AS Nr,
       SYS_XMLGEN(K_Titel) AS Titel,
       SYS_XMLGEN(K_Untertitel) AS Untertitel
  FROM kurs
 WHERE K_Titel IN ('Oracle', 'PHP');

Man erhält eine Liste mit den ausgewählten Kursen, wobei jede Reihe eine XML-Struktur beinhaltet. Sie besteht aus den Spaltennamen als Elementnamen und den Spaltenwerten als Textknoten. Bei einer möglichen Verarbeitung solcher Strukturen ist darauf zu achten, dass man stets einen Cursor verwendet, da mehrere Reihe zurückgeliefert werden und nicht nur ein einziges XML-Dokument.

NR TITEL UNTERTITEL
<K_NR>1015024</K_NR>
<K_TITEL>PHP</K_TITEL>
<K_UNTERTITEL>Syntax + Konzepte</K_UNTERTITEL>
…
10 Zeilen ausgewählt.

Einsatz von Objekt- und Tabellentypen

Neben der gerade gezeigten sehr einfachen Verwendung von SYS_XMLGEN() lässt sich als Ausdruck auch ein Objekttyp benutzen, der jeweils in der Datenbank gespeichert sein muss. Dies erhöht zwar die Lesbarkeit der SQL-Abfrage im Vergleich zu den SQLX-Funktionen, jedoch ist man bei dieser Technik der Verschachtelung ebenso wie beim DBMS_XMLGEN-Paket verpflichtet, zunächst die entsprechenden Typen bereitzustellen. Dies ist für eine umfangreiche Anwendungsentwicklung mit wiederholter Ausführung sicherlich kein Problem, doch für eine spontane und einmalige SQL-Abfrage ein recht großer Aufwand.

Der erste Objekttyp enthält Attribute, die aus Spalten der Kurstabelle bestehen und die Kursnummer, den Titel und den Untertitel beschreiben. Für eine Struktur mit mehreren Reihen und nicht nur mit mehreren Spalten erzeugt man hieraus einen Tabellentyp. Diesen ruft man schließlich in einem dritten Objekttyp für eine Dozentenliste auf.

-- Lösche mögliche vorhandene Typen
DROP TYPE Dozent_T;
DROP TYPE Kursliste_T;
DROP TYPE Thema_T;
/
-- Mehrspaltige Struktur
CREATE TYPE Thema_T AS OBJECT (
  "@Nr"      NUMBER(7,0),
  titel      VARCHAR2(30),
  untertitel VARCHAR2(40)
);
/
-- Reihenstruktur
CREATE TYPE Kursliste_T AS TABLE OF Thema_T;
/
-- Mehrspaltige, verschachtelte Reihenstruktur
CREATE TYPE Dozent_T AS OBJECT (
  "@Nr"    NUMBER(7,0),
  vorname  VARCHAR2(20),
  nachname VARCHAR2(30),
  kursliste Kursliste_T
);
/

Die Abbildung veranschaulicht noch einmal die unterschiedlichen Datenstrukturen, die für eine verschachtelte Abfrage bereitstehen müssen.

Die Abfrage besteht grundsätzlich aus einer wenig veränderten, einfachen SQL-Abfrage, wobei die Spalten vom Typnamen als Konstruktor umgehen sind und die gesamte Spalte der Parameter der SYS_XMLGEN()-Funktion ist. Diese Abfrage erzeugt eine Übertragung oder eine Abbildung der Spalten auf die Objekttypattribute.

SELECT SYS_XMLGEN(Thema_T(K_Nr,
                          K_Titel,
                          K_Untertitel)) AS XML
  FROM kurs
 WHERE K_Nr = '1025039';

Das Ergebnis ist eine einfache XML-Struktur als XMLType, die aus zwei Spalten und einem Attribut besteht. Das Attribut wird automatisch dem ROW-Element hinzugefügt, das die einzelnen Reihen beschreibt.

XML
<ROW Nr="1025039">
  <TITEL>SQL</TITEL>
  <UNTERTITEL>DDL, DML + DCL</UNTERTITEL>
</ROW>
1 Zeile wurde ausgewählt.

Für eine tiefere Verschachtelung lassen sich alle drei Typen verwenden, wobei für die Abbildung der Dozentenspalten auf die Attribute des Dozent_T-Typs dieser Typ als Konstruktor aufgerufen wird und wobei für die Abbildung der Kursspalten auf die Attribute des Thema_T-Typs eine Liste vom Typ Kursliste_T benötigt wird. Da hier eine Wiederholung benötigt wird, deren Daten über eine korrelierte Unterabfrage („für jeden Dozenten seine Kurse“) ermittelt werden, benötigt man zusätzlich noch die CAST()-Funktion, um die Daten in eine solche Kursliste zu übernehmen. Diese Funktion führt die Konversion durch, während die MULTISET()-Funktion gerade den Bereich angibt, der wiederholt auftreten wird.

SELECT SYS_XMLGEN(Dozent_T(dozent.D_Nr,
                           D_Vorname,
                           D_Nachname,
                           CAST(MULTISET(SELECT kurs.K_Nr,
                                                K_Titel,
                                                K_Untertitel
                                    FROM kurs INNER 
                                    JOIN themenverteilung t2
                                      ON kurs.K_Nr = t2.K_Nr
                                   WHERE t1.D_Nr = t2.D_Nr)
                           AS Kursliste_T))) AS XML
  FROM dozent INNER JOIN themenverteilung t1
    ON dozent.D_Nr = t1.D_Nr
 WHERE dozent.D_Nr BETWEEN 5 AND 7;

Die Abbildung veranschaulicht noch einmal die Zuordnung von Datenstrukturen in der Datenbank in Form von Objekt- und Tabellentypen zu den Bereichen in der SQL-Abfrage, denen sie entsprechen.

Man erhält ein verschachteltes XML-Dokument als XMLType, das eine Dozentenliste und für jeden Dozenten seine Kursliste enthält.

XML
<ROW Nr="6">
  <VORNAME>Ralf</VORNAME>
  <NACHNAME>Hostell</NACHNAME>
  <KURSLISTE>
    <THEMA_T Nr="1010004">
      <TITEL>Fireworks</TITEL>
      <UNTERTITEL>Webgrafik + Menüs</UNTERTITEL>
    </THEMA_T>
    <THEMA_T Nr="1020052">
      <TITEL>HTML</TITEL>
      <UNTERTITEL>Webdesign</UNTERTITEL>
    </THEMA_T>
…

Erzeugung von Aggregaten

Es existiert eine weitere Funktion, mit deren Hilfe Sie ein Eltern-Elemente angeben können. Eine Aggregation im Sinne von tatsächlich verschachtelten Ergebnissen lässt sich damit nicht einrichten. Wie Sie schon gesehen haben, liefert die SYS_XMLGEN()-Funktion immer mehrere Reihen. Dies lässt sich durch die SYS_XMLAGG()-Funktion verhindern, die ein Eltern-Element um sämtliche Reihen setzt:

SELECT SYS_XMLAGG(SYS_XMLGEN(K_Nr)) AS XML
  FROM kurs
 WHERE K_Titel = 'PHP';

Man erhält innerhalb des Eltern-Elements ROWSET alle weiteren gefundenen Kurse. Wichtig für die Verarbeitung ist hier, dass tatsächlich nur eine Reihe zurückgeliefert wird und daher bei einer entsprechenden Verarbeitung auch kein Cursor verwendet werden muss, um die gesamte Ergebnismenge zu durchlaufen.

XML
<ROWSET>
  <K_NR>1015024</K_NR>
..
  <K_NR>1015068</K_NR>
</ROWSET>
1 Zeile wurde ausgewählt.

XML-Dokumentvorgaben

Eine letzte Möglichkeit besteht darin, für beide Funktionen noch das XMLFormat-Objekt zu verwenden, um weitere Formatierungen für das XML-Dokument anzugeben. Hierunter fällt auch die Angabe von passenden Eltern-Elementen.

• enclTag in VARCHAR2(100): Namen des Eltern-Elements für die zugehörige Struktur, wobei der Standardwert normalerweise ROW ist. Sollte das Attribut schemaType den Wert USE_GIVEN_SCHEMA haben, dann liefert dieses Attribut auch den Namen des XML Schema-Elements.
• schemaType in VARCHAR2(100): Mit den Werten NO_SCHEMA als Standardwert und USE_GIVEN_SCHEMA erhält man eine Schema-Angabe.
• schemaName in VARCHAR2(4000): Name des Schemas, wenn in schemaType der Wert USE_GIVEN_SCHEMA verwendet wird.
• targetNameSpace in VARCHAR2(4000): Angabe des Zielnamensraums, wenn das Schema angegeben wurde.
• dburl in VARCHAR2(2000): Die URL, unter der die Datenbank zu finden ist, um die Option WITH_SCHEMA aufzulösen. Ansonsten wird der Pfad als relativ angenommen.
• processingIns in VARCHAR2(4000): Prozessoranweisungen für die Ausgabe.

Die folgende Abfrage verwendet zwei Formatvorgaben, um ebenfalls Eltern-Elemente mit einem passenden Namen zu versehen:

SELECT SYS_XMLAGG(SYS_XMLGEN(Dozent_T(dozent.D_Nr,
                           D_Vorname,
                           D_Nachname,
                           CAST(MULTISET(SELECT kurs.K_Nr,
                                                K_Titel,
                                                K_Untertitel
                    FROM kurs INNER JOIN themenverteilung t2
                      ON kurs.K_Nr = t2.K_Nr
                   WHERE t1.D_Nr = t2.D_Nr)
                           AS Kursliste_T)),
                          XMLFormat.createFormat('DOZENT')), XMLFormat.createFormat('DOZENTENLISTE')).getClobVal() AS XML
  FROM dozent INNER JOIN themenverteilung t1
    ON dozent.D_Nr = t1.D_Nr
 WHERE dozent.D_Nr BETWEEN 5 AND 7;

Man erhält folgende Ausgabe:

XML
<?xml version="1.0"?>
<DOZENTENLISTE>
<DOZENT Nr="6">
  <VORNAME>Ralf</VORNAME>
  <NACHNAME>Hostell</NACHNAME>
  <KURSLISTE>
…

Die Abbildung stellt die Struktur des Dokuments schematisch dar: Innerhalb einer Dozentenliste befinden sich mehrere Dozenten, die jeweils ihre Themen in einer Kursliste besitzen. Diese Kurse stellen eine Sammlung von mehreren Kursen dar.

In der Übersichtsdarstellung für das gesamte Beispiel (siehe Abbildung 6.12) befinden sich alle drei Bausteine für die Abfrage in einer Datei. Zum einen benötigt man entsprechende Objekt- und Tabellentypen, die in der Datenbank gespeichert sind. Zum anderen bezieht sich die SQL-Abfrage genau auf diese Datenstrukturen, aus denen über die Funktion SYS_XMLGEN() die passende XML-Struktur erzeugt wird. Als dritte Komponente erhält man dann das eigentliche Dokument.

Auch für diese Funktionen gilt wieder, dass sie sich als XMLType verarbeiten oder in der Datenbank für eine spätere Verarbeitung speichern lassen.