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