21. Oktober 2007

XML-Dokumente relational aufbereiten: mit SQL!

XML wurde ja bereits schon einige Male in diesem Blog behandelt - heute gehen wir nochmal quasi "zurück zu den Grundlagen". Bereits seit Oracle 9.2 gebt es sehr umfassende XML-Funktionen in der Datenbank. Man kann nicht nur XML aus relationalen Tabellen erzeugen, sondern auch als solches mit dem Datentyp XMLTYPE speichern. Ein richtig schönes Feature ist die Möglichkeit, MXL-Dokumente als relationale Tabelle aufzubereiten. Und das ist einfacher als man denkt - ein Beispiel ...
<blog>
  <name>SQL und PL/SQL</name>
  <autor>Carsten Czarski</autor>
  <themen>
    <thema>XML</thema>
    <thema>PL/SQL</thema>
  </themen>
</blog>
Speichert man das XML in eine Tabelle mit einer Spalte vom Typ XMLTYPE ...
create table xmltest (
  dokument xmltype
)
/

insert into xmltest values (xmltype(
'<blog>
  <name>SQL und PL/SQL</name>
  <autor>Carsten Czarski</autor>
  <themen>
    <thema>XML</thema>
    <thema>PL/SQL</thema>
  </themen>
</blog>'
))
/
... so bereitet dieses SQL-Kommando hier ...
select
  extractvalue(dokument, '/blog/name') as blog_name,
  extractvalue(dokument, '/blog/autor') as blog_autor,
  extractvalue(value(thema), '/thema/text()') as thema
from xmltest,
  table(xmlsequence(extract(dokument, '/blog/themen/thema'))) thema
/
... die Ergebnisse relational auf.
BLOG_NAME            BLOG_AUTOR      THEMA
-------------------- --------------- --------------------
SQL und PL/SQL       Carsten Czarski XML
SQL und PL/SQL       Carsten Czarski PL/SQL
Wichtig sind die Funktionen EXTRACTVALUE und das Konstrukt TABLE(XMLSEQUENCE(EXTRACT))). EXTRACTVALUE extrahiert, wie der Name schon sagt, den Inhalt eines einzelnen XML-Tags oder -Attributs (XML-Namespaces werden übrigens auch unterstützt). Das TABLE(XMLSEQUENCE(EXTRACT()))-Konstrukt braucht man, um hierarchische XML-Dokumente relational aufzubereiten; schließlich gibt es in diesem Beispiel nur ein XML-Dokument (mit zwei Themen) - das Ergebnis der SQL-Abfrage enthält jedoch zwei Zeilen. Die Abfrage "klopft" das XML-Dokument sozusagen flach. Das geht übrigens auch mit mehrfach geschachtelten Hierarchien ...
Und wie sähe es bei einem Blog ohne Themen aus?
insert into xmltest values (xmltype(
'<blog>
  <name>Ein zweiter Blog</name>
  <autor>John Doe</autor>
  <themen/>
 </blog>'
))
/
Das bringt genau das gleiche Ergebnis ... da das XML-Dokument keine Themen enthält, erscheint das XML-Dokument erst gar nicht. Das liegt daran, dass das TABLE(XMLSEQUENCE(EXTRACT()))-Konstrukt den hierarchischen Teil wie eine zweite "virtuelle" Tabelle behandelt und einen Join mit der eigentlichen Tabelle durchführt. Damit gibt es auch schon eine einfache Lösung:
select
  extractvalue(dokument, '/blog/name') as blog_name,
  extractvalue(dokument, '/blog/autor') as blog_autor,
  extractvalue(value(thema), '/thema/text()') as thema
from xmltest,
  table(xmlsequence(extract(dokument, '/blog/themen/thema'))) (+) thema
/

BLOG_NAME            BLOG_AUTOR      THEMA
-------------------- --------------- --------------------
SQL und PL/SQL       Carsten Czarski XML
SQL und PL/SQL       Carsten Czarski PL/SQL
Ein zweiter Blog     John Doe
Mit einem einfachen CREATE VIEW hinterlegt man das Ganze im Dictionary - es sieht damit aus wie eine gewöhnliche Tabelle. Ein anderer User, der die View selektiert, muss gar nicht mehr wissen, wo die Daten eigentlich herkommen. Soviel zur Funktionalität ... Beim nächsten Post werde ich mich ein wenig mit dem Thema Performance beschäftigen. Gerade wenn man dies auf sehr vielen Tabellen durchführt, gibt es hier, je nachdem, wie man das XML in der Datenbank speichert, doch massive Unterschiede. Vorab nur soviel: In diesem Beispiel wurde das XML textbasiert gespeichert - und das ist für ein relationales Aufbereiten auch schon die langsamste und denkbar schlechteste Option. Wie es besser geht ...? Stay tuned!

Keine Kommentare:

Beliebte Postings