Umwandlung von relationalen Daten in XML-Dateien

Um den Inhalt von Tabellen in XML-Files zu überführen, gibt es eine ganze Reihe von Möglichkeiten, u.a.

• die Verwendung der SQL-XML(SQLX)-Funktionen wie XMLElement, XMLAgg, XMLForest etc.
• das Package DBMS_XMLGen
• die Erzeugung von XML-Daten über das sog. DBUriServlet

In diesem Monatstipp sollen die beiden letztgenannten Methoden kurz vorgestellt werden, und zwar anhand einer Beispieltabelle, die im Gegensatz zu den üblichen Beispieltabellen aus den Oracle-Demo-Schemata einige Stolpersteine für die XML-Verarbeitung in Oracle enthält.

Die SQLX-Funktionen und die Möglichkeiten der Kombination von XML-Erzeugung und gleichzeitiger Transformation folgen  in einem späteren Beitrag.

conn scott/tiger
set define off
DROP TABLE verliehen;
CREATE TABLE verliehen(
id           NUMBER(4),
kundenname   VARCHAR2(40),
geräte_nr    NUMBER(6),
geräte_art   VARCHAR2(40),
zahl         NUMBER(4),   
verliehen_am DATE);
INSERT INTO verliehen VALUES(1, 'Müller & Söhne', 201, 'Häcksler', 1, sysdate - 20);
INSERT INTO verliehen VALUES(2, 'Susi Süß', 2290, 'Küchenmaschine', 1, sysdate - 12);
INSERT INTO verliehen VALUES(3, 'Hannes&Moritz', 15, 'Schürzen', 20, sysdate - 10);
INSERT INTO verliehen VALUES(4, 'Werner Bauer', 201, 'Mörser', 1, sysdate - 20);
INSERT INTO verliehen VALUES(5, 'Max "Morle" Mustermann', 121, 'Sofa', 1, sysdate - 8);
INSERT INTO verliehen VALUES(6, 'Lieschen Müller', 122, 'Vom Winde verweht', 1,  sysdate - 5);

Erzeugung von XML-Daten über das DBUriServlet

Die auf den ersten Blick unkomplizierteste Methode benutzt das sog. DBUriServlet, zu dem Carsten Czarski eine schöne Einführung geschrieben hat.
Statt ein XML-File aus den relationalen Daten zu generieren, kann man den Tabelleninhalt einfach über den Browser als XML abrufen. Voraussetzung ist, dass der sog. der HTTP-Listener aktiviert ist, den auch APEX nutzt (Defaultport 8080).
Wenn das der Fall ist, kann man die Tabellen über folgende Eingabe in der Adressleiste des Browsers aufrufen (User- und Tabellenname müssen groß geschrieben werden), nachdem man sich über Username und Passwort authentifiziert hat.

 "http://<hostname>:<http-portnummer>/oradb/<USERNAME>/<TABELLENNAME>"
 z.B.
 "http://localhost:8080/oradb/SCOTT/EMP"

Ausgabe im Browser:

<EMP>
  <ROW>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>17-DEC-80</HIREDATE>
    <SAL>800</SAL>
    <DEPTNO>10</DEPTNO>
  </ROW>
....
</EMP>

Leider hat diese nette Funktionalität einen Haken. Sie ist per default auf utf-8 encoding eingestellt, was zu Problemen bei der Zeichensatz-Konvertierung führt, wenn der Characterset der Datenbank z.B. Windows-1252 ist.

Weil in der Tabelle "verliehen" Spaltennamen und Spalteninhalte mit deutschen Umlauten vorkommen, erhält man bei der Eingabe von "http://localhost:8080/oradb/SCOTT/VERLIEHEN" die Rückmeldung

XML-Verarbeitungsfehler: nicht wohlgeformt
Adresse: "http://localhost:8080/oradb/SCOTT/VERLIEHEN"
Zeile Nr. 5, Spalte 16: 
  <KUNDENNAME>M?ller &amp; S?hne</KUNDENNAME>
---------------^

Ein einfacher Workaround ist die Darstellung des XML-Inhalts als Text. Das erreicht man mit dem Zusatz ?contenttype=text/plain.

 "http://localhost:8080/oradb/SCOTT/VERLIEHEN?contenttype=text/plain"
=>
<?xml version="1.0"?>
<VERLIEHEN>
 <ROW>
  <ID>1</ID>
  <KUNDENNAME>Müller &amp; Söhne</KUNDENNAME>
  <GERÄTE_NR>201</GERÄTE_NR>
  <GERÄTE_ART>Häcksler</GERÄTE_ART>
  <ZAHL>1</ZAHL>
  <VERLIEHEN_AM>17-NOV-10</VERLIEHEN_AM>
 </ROW>
 ...
</VERLIEHEN>

Die XML-Daten kann man direkt aus dem Browser als XML-Datei unter dem gewünschten Namen in einem beliebigen Ordner speichern.

Das so erzeugte Dokument ist zunächst nicht wohlgeformt, weil man die Kodierung auf diesem einfachen Weg nicht direkt mitgeben kann. Eine einfache Nachbearbeitung der XML-Datei durch den Zusatz encoding="WINDOWS-1252" zum XML-Prolog ändert dies jedoch schnell.

Eine elegantere Methode ist die ebenfalls über das DB URI Servlet mögliche Transformation des XML-Files mit Hilfe eines Stylesheets. Dazu müsste man aber zunächst ein passendes Stylesheet erstellen und innerhalb der in Oracle integrierten XML-DB zur Verfügung stellen.

Verwendung des DBMS_XMLGen-Packages

DBMS_XMLGen ist der Nachfolger des veralteten Packages DBMS_XMLQuery. In der Oracle Version 10g ist es noch performanter als die SQLX-Funktionen, in der Version 11gR2 haben letztere die Nase vorn.
Als Beispiel dient hier eine kleine Prozedur, an die man den Namen der als XML-File zu exportierenden Tabelle, das Directory für den Export, die gewünschten Tag-Namen für das Wurzelelement und die Unterelemente und optional den encoding-Zusatz als Parameter übergibt. 

Erklärungen zu den verwendeten Funktionen und Prozeduren des Packages

• Die Funktion newContext übernimmt einen Select und gibt den sogenannten ctxHandle (eine Nummer zur "Weiterverfolgung") zurück
• Die Funktion getXML generiert ein XML-Dokument aus der Abfrage, die durch den übergebenen ctxHandle spezifiziert wurde. Der Rückgabewert ist ein CLOB.
• Über die Prozedur setRowTag kann man den Tag-Namen für eine einzelne Zeile der Abfrage setzen (Default wäre row) und
• über die Prozedur setRowSetTag den Namen des Root-Elements des XML-Dokuments (Default wäre rowset)
• Die Prozedur closeContext schließt den "Kontext" und gibt alle Ressourcen frei.

CREATE OR REPLACE PROCEDURE export_xml(
    p_tabname   VARCHAR2
  , p_directory VARCHAR2
  , p_rowtag    VARCHAR2
  , p_rowsettag VARCHAR2
  , p_encoding  VARCHAR2 DEFAULT 'UTF-8')
AS
   l_string   VARCHAR2(100);
   l_ctx      DBMS_XMLGen.ctxHandle;
   l_clob     CLOB;
BEGIN
   l_string := 'SELECT * FROM '||p_tabname;
-- Erzeugung des neuen Kontexts
   l_ctx := DBMS_XMLGen.newContext(l_string);
-- Namen für Unterelemente und Wurzelelement werden übergeben
   DBMS_XMLGen.setRowTag(l_ctx, p_rowtag);
   DBMS_XMLGen.setRowSetTag(l_ctx, p_rowsettag);
-- Das über getXML erzeugt XML-Dokument wird in einen CLOB zurückgeschrieben
   l_clob := DBMS_XMLGen.getXML(l_ctx);
-- und die Ressourcen wieder freigegeben
   DBMS_XMLGen.closeContext(l_ctx);
-- Die Grösse des CLOBs wird zur Kontrolle ausgegeben
   DBMS_OUTPUT.PUT_LINE('Größe des CLOBs: '||DBMS_LOB.getlength (l_clob));
-- Anpassung der Kodierung
   l_clob := REPLACE(l_clob, '<?xml version="1.0"?>', q'[<?xml version='1.0' encoding=']'||p_encoding||q'['?>]');
-- Export des CLOBs
   DBMS_XSLPROCESSOR.CLOB2FILE(l_clob, UPPER(p_directory), p_tabname||'.xml');
END;

Die Verwendung der ab 10g zur Verfügung stehenden Prozedur DBMS_XSLPROCESSOR.CLOB2FILE hat gegenüber dem üblichen Export des CLOBs über DBMS_LOB.READ und UTL_FILE in einer Schleife den Vorteil, dass keine Zeilenumbrüche innerhalb von Tag-Namen vorkommen können. In diesem Fall ist nämlich einiges an Nacharbeit nötig, um die XML-Datei in einen wohlgeformten Zustand zu versetzen.
Das optionale "Einschmuggeln" der Kodierung über die REPLACE-Funktion ist nicht die eleganteste Lösung, aber für kleinere XML-Dateien durchaus praktikabel. Sie hält auch beim Export größerer Dateien - wie im Beispiel hier all_objects (28 MB) - den Vorgang nur unwesentlich auf.

Beispiele:

conn sys/<passwort> as sysdba
CREATE OR REPLACE DIRECTORY xmldir AS 'c:\temp';
GRANT READ, WRITE ON DIRECTORY xmldir TO scott;

conn scott/tiger
set timing on
BEGIN
  export_xml(
    p_tabname => 'all_objects',
    p_directory => 'xmldir',
    p_rowtag => 'details',
    p_rowsettag =>'alle_objekte');
END;
/

Größe des CLOBs: 28961278

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:32.31 (auf 11gR2, ohne die Übergabe des Kodierungsparameters und Ersetzung des Prologs)
Abgelaufen: 00:00:32.92 (auf 11gR2, mit Übergabe des Kodierungsparameters und Ersetzung des Prologs)

BEGIN
   export_xml(
     p_tabname => 'verliehen',
     p_directory => 'xmldir',
     p_rowtag => 'Details',
     p_rowsettag =>'Verleih-Daten',
     p_encoding => 'Windows-1252');
 END;
/

Größe des CLOBs: 1283

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.06

Wenn Sie noch mehr über die Möglichkeiten der XML-Bearbeitung erfahren wollen, besuchen Sie doch einfach unseren XML- oder PL/SQL II-Kurs.