XML-Dokumente und SQL: EXTRACTVALUE oder XMLTABLE?
In der Vergangenheit hatte ich ja schon einige Blog-Postings zum Umgang mit XML-Dokumenten
geschrieben. Es ist ja in der Oracle-Datenbank schon seit der Version 9i sehr einfach,
Informationen mit den Funktionen EXTRACT und EXTRACTVALUE aus XML-Dokumenten
zu extrahieren. Allerdings hat sich die SQL-Standardisierung in den letzten Jahren fortentwickelt,
so dass diese und andere bekannte ab der Datenbankversion 11.2 als deprecated, also "veraltet"
gekennzeichnet sind. Sie funktionieren immer noch, werden wohl auch noch eine sehr lange Zeit
weiter funktionieren; man sollte allerdings (vor allem in neuen Projekten) allmählich
auf die neue Syntax mit den Funktionen
XMLQUERY und XMLTABLE umsteigen. Dazu ein Beispiel:
create table xmltest (
id number(10),
xml xmltype
)
/
insert into xmltest values (
1,
'<emps cnt="2">
<emp ename="FORD" sal="3000"/>
<emp ename="MILLER" sal="5000"/>
</emps>'
);
insert into xmltest values (
2,
'<emps cnt="3">
<emp ename="KING" sal="10000"/>
<emp ename="CLARK" sal="4000"/>
<emp ename="MEYER" sal="7500"/>
</emps>'
);
commit
/
Nun ein paar Beispiele - wenn es darum geht, aus jeder Zeile das XML-Attribut cnt des Tags emps
auszulesen, geht das schon seit Oracle9i wie folgt.
select
id,
extractvalue(xml, '/emps/@cnt') emp_cnt
from xmltest
/
ID EMP_CNT
---------- ----------
1 2
2 3
Das funktioniert, verwendet man die objektrelationale Speicherung oder ab Oracle11g die neue
Varinate Binary XML, so wird auch optimiert ausgeführt. Dennoch entspricht es nicht dem
SQL-Standard - und da man (wie immer) davon ausgehen kann, dass künftige Optimierungen sich
auf die standardisierte Syntax beziehen werden, ist es sinnvoller, die folgende Syntax zu verwenden.
select
t.id,
x.emp_cnt
from
xmltest t,
xmltable(
'for $i in /emps return $i'
passing xml
columns "EMP_CNT" number path '/emps/@cnt'
) x
/
ID EMP_CNT
---------- ----------
1 2
2 3
Ergebnis und auch die Ausführungsweise sind gleich. Allerdings hat die neue Syntax einen Vorteil;
denn es ist möglich, den Datentypen der neuen Spalte vorzugeben. Da wir in diesem Beispiel die
textbasierte Speicherung der XML-Dokumente nutzen (weder objektrelational noch Binary XML),
werden grundsätzlich alle extrahierten Informationen als VARCHAR2(4000) zurückgegeben - Ihr könnt
das prüfen, indem Ihr mit dem SELECT eine View erzeugt. Die neue Syntax erlaubt in der COLUMNS-Klausel
jedoch, die Datentypen genau vorzugeben - früher wären hierfür diverse TO_NUMBER-Aufrufe nötig
gewesen.
Die XML-Dokumente in diesem Beispiel enthalten eine 1:n-Beziehung: das XML-Tag emp kommt
mehrfach vor. Auch das war in der Vergangenheit kein Problem - mit dem Funktionskonstrukt
TABLE(XMLSEQUENCE(EXTRACT(...))) kann man Informationen problemlos auslesen.
select
id,
extractvalue(xml, '/emps/@cnt') emp_cnt,
extractvalue(value(e), '/emp/@ename') ename,
extractvalue(value(e), '/emp/@sal') sal
from xmltest,
table(xmlsequence(extract(xml, '/emps/emp'))) e
/
ID EMP_CNT ENAME SAL
---------- ---------- -------------------- -----------
1 2 FORD 3000
1 2 MILLER 5000
2 3 KING 10000
2 3 CLARK 4000
2 3 MEYER 7500
Auch hier ist die standardisierte Funktion XMLTABLE anwendbar - und (betrachtet man es genau)
auch besser lesbar.
select
t.id,
x.emp_cnt,
e.ename,
e.sal
from
xmltest t,
xmltable(
'for $i in /emps
return $i'
passing xml
columns "EMP_CNT" number path '/emps/@cnt'
) x,
xmltable(
'for $i in /emps/emp return $i'
passing xml
columns "ENAME" varchar2(20) path '/emp/@ename',
"SAL" number path '/emp/@sal'
) e
/
Ich persönlich hatte mich so gut mit der "alten" Syntax angefreundet, dass ich mich
wirklich "zwingen" muss, auf die neue umzusteigen. Allerdings finde ich gerade die
COLUMNS-Klausel mit der Möglichkeit, die Datentypen vorzugeben, sehr elegant.
Das for $i in ... return $i ist XQuery - und man könnte nun noch seitenweise darüber
schreiben - aber für dieses Mal möchte ich es damit belassen.
Keine Kommentare:
Kommentar veröffentlichen