11. Januar 2011

XML-Dokumente und SQL: EXTRACTVALUE oder XMLTABLE?

Working with XML documents: EXTRACTVALUE or 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:
In the past I already had some Blog postings about working with XML documents and SQL. Since Oracle9i it is very easy to extract information out of XML documents using the SQL functions EXTRACT and EXTRACTVALUE. But the SQL standard moved forward during the last years and it turned out that the SQL standard contains other functions for that purpose. With the database version 11.2 Oracle marked some of the old and well-known SQL functions for XML as deprecated. They still work and they will work in the future as well - but since future optimizations and enhancements will concentrate on the new, standard-compliant functions I'd recommend to start using the new functions XMLQUERY and XMLTABLE. This blog posting is about XMLTABLE. We start with an example:
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.
Now we'll do some queries to extract information from the XML documents. In the first example we need the attribute cnt of the XML tag emps in each table row. The "old" and well known syntax is as follows.
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.
This works well - and if you use the object relational or the Binary XML storage options the query is being optimized for fast execution. But the standard-compliant functions look different - so you might formulate the same query in future projects as follows.
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.
The query returns the same result and should use the same execution plan (depending on the chosen XML storage option). But there is one advantage: The COLUMNS clause allows to set the datatype of the returned column - so you don't need TO_NUMBER or TO_DATE calls any more. This is particularly useful when the XML is stored with the CLOB (textbased) approach - any extracted information is a VARCHAR2(4000) for the database (it has no information about the datatype). So when you use the "old" functions EXTRACT or EXTRACTVALUE the results are always returned as VARCHAR2(4000). The COLUMNS clause of the XMLTABLE function allows to set the desired datatype in a very elegant manner.
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.
As in reality: The XML documents in this example contain a one-to-many relationship; the XML tag emps contains multiple emp tags. That is no problem: Since Oracle9i we can use the syntax construct TABLE(XMLSEQUENCE(EXTRACT(...))) to extract each and every piece of information.
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.
The standardized function XMLTABLE is also better suited for this - and (to be honest) it has a much better readability.
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.
In the past I got very familar with EXTRACT and EXTRACTVALUE and I really have to "force" myself to work with the new functions. But the COLUMNS clause alone is worth the efforts. The for $i in ... return $i syntax inside the XMLTABLE function is XQuery; we could now move on and do another 10 blog postings on that topic ... but not today.

Keine Kommentare:

Beliebte Postings