Hier zunächst die Datenbasis:
create table xml_test
(
company_id integer,
xml xmltype
);
insert into xml_test(company_id, xml)
values
(
1,
'<company>
<employees>
<emp empno="7839" ename="KING" sal="5000" deptno="10">
<emp empno="7566" ename="JONES" sal="2975" deptno="20">
<emp empno="7788" ename="SCOTT" sal="3000" deptno="20">
<emp empno="7876" ename="ADAMS" sal="1100" deptno="20"/>
</emp>
<emp empno="7902" ename="FORD" sal="3000" deptno="20">
<emp empno="7369" ename="SMITH" sal="800" deptno="20"/>
</emp>
</emp>
<emp empno="7782" ename="CLARK" sal="2450" deptno="10">
<emp empno="7934" ename="MILLER" sal="1300" deptno="10"/>
</emp>
<emp empno="7698" ename="BLAKE" sal="2850" deptno="30">
<emp empno="7499" ename="ALLEN" sal="1600" deptno="30"/>
<emp empno="7521" ename="WARD" sal="1050" deptno="30"/>
<emp empno="7654" ename="MARTIN" sal="1250" deptno="30"/>
<emp empno="7844" ename="TURNER" sal="1500" deptno="30"/>
<emp empno="7900" ename="JAMES" sal="950" deptno="30"/>
</emp>
</emp>
</employees>
<departments>
<dept deptno="10">
<dname>ACCOUNTING</dname>
<loc>NEW YORK</loc>
<budget>13000</budget>
</dept>
<dept deptno="20">
<dname>RESEARCH</dname>
<loc>DALLAS</loc>
<budget>15000</budget>
</dept>
<dept deptno="30">
<dname>SALES</dname>
<loc>CHICAGO</loc>
<budget>11000</budget>
</dept>
<dept deptno="40">
<dname>OPERATIONS</dname>
<loc>BOSTON</loc>
<budget>10000</budget>
</dept>
</departments>
</company>'
);
Dabei handelt es sich um eine Tabelle mit einer Spalte vom Typ XMLType. Diese Spalte enthält ein XML-Dokument mit der Mitarbeiter-Hierarchie samt Informationen über die Abteilungen. Nun betrachten wir drei Aufgabenstellungen:(1) Abteilungen relational speichern (ähnlich Tabelle DEPT)
(2) Mitarbeiter relational speichern (ähnlich Tabelle EMP)
(3) Mitarbeiter im XML-Dokument nicht in einer Hierarchie darstellen
Wir beginnen mit der ersten und zugleich einfachsten Aufgabe. Mithilfe von
XMLTable und einem XQuery-FLWOR-Ausdruck erhält man:select x.deptno, x.dname, x.loc, x.budget from xml_test, xmltable ( ' for $dept in //dept order by $dept/deptno return $dept ' passing xml columns "DEPTNO" integer path '/dept/@deptno', "DNAME" varchar2(15) path '/dept/dname', "LOC" varchar2(15) path '/dept/loc', "BUDGET" integer path '/dept/budget' ) x;
DEPTNO DNAME LOC BUDGET
---------- --------------- --------------- ----------
10 ACCOUNTING NEW YORK 13000
20 RESEARCH DALLAS 15000
30 SALES CHICAGO 11000
40 OPERATIONS BOSTON 10000
Dabei handelt es sich bei //dept um eine Pfadangabe mit XPath, welche alle entsprechenden Department-Tags im XML-Dokument findet. Diese Tags werden separat durchlaufen und sodann zurückgegeben.Die nächste Aufgabe gestaltet sich etwas schwieriger, da die Elemente der Mitarbeiter geschachtelt sind, um die Hierarchie der Mitarbeiter abzubilden. Nun geht es also darum dies rückgängig zu machen, um eine Struktur, wie aus der EMP-Tabelle bekannt, zu erhalten.
Der FLWOR-Ausdruck durchläuft im for-Teil zunächst alle Mitarbeiter-Tags; für jeden Mitarbeiter wird dann im let-Teil der Parent (sofern vorhanden) ermittelt; jetzt werden die Informationen in ein XML-Element mit den entsprechenden Attributen gepackt und zurückgegeben.
select
x.empno,
x.ename,
x.mgr,
x.sal,
x.deptno
from
xml_test,
xmltable
(
'
for $emp in //emp
let $mgr := $emp/../@empno
return
element emp
{
attribute empno {$emp/@empno},
attribute ename {$emp/@ename},
attribute mgr {data($mgr)},
attribute sal {$emp/@sal},
attribute deptno {$emp/@deptno}
}
'
passing xml
columns
"EMPNO" integer path '/emp/@empno',
"ENAME" varchar2(15) path '/emp/@ename',
"MGR" integer path '/emp/@mgr',
"SAL" integer path '/emp/@sal',
"DEPTNO" integer path '/emp/@deptno'
) x;
EMPNO ENAME MGR SAL DEPTNO
---------- --------------- ---------- ---------- ----------
7839 KING 5000 10
7566 JONES 7839 2975 20
7788 SCOTT 7566 3000 20
7876 ADAMS 7788 1100 20
7902 FORD 7566 3000 20
7369 SMITH 7902 800 20
7782 CLARK 7839 2450 10
7934 MILLER 7782 1300 10
7698 BLAKE 7839 2850 30
7499 ALLEN 7698 1600 30
7521 WARD 7698 1050 30
7654 MARTIN 7698 1250 30
7844 TURNER 7698 1500 30
7900 JAMES 7698 950 30
Man erhält damit die gewünschte Struktur.Die dritte Aufgabenstellungen zielt nun auf eine Transformation des XML-Dokuments; pro Mitarbeiter soll es nun ein XML-Element geben mit dem zusätzlichen Attribut für den Vorgesetzen.
Die Lösung erfolgt wieder mithilfe von XQuery und der Funktion
XMLQuery, die in der Select-Klausel zum Einsatz kommt. Der wesentliche Unterschied liegt darin, dass jetzt ein XML-Dokument zurückkommt.select xmlquery ( '' passing xml returning content ) x from xml_test; { for $emp in //emp let $mgr := $emp/parent::emp/@empno return } {data($emp/@ename)} {data($emp/@sal)} {data($emp/@deptno)} { for $dept in //dept return $dept }
X ---------------------------------- <company> <employees> <emp empno="7839" mgr=""> <ename>KING</ename> <sal>5000</sal> <deptno>10</deptno> </emp> <emp empno="7566" mgr="7839"> <ename>JONES</ename> <sal>2975</sal> <deptno>20</deptno> </emp> <emp empno="7788" mgr="7566"> <ename>SCOTT</ename> <sal>3000</sal> <deptno>20</deptno> </emp> <emp empno="7876" mgr="7788"> <ename>ADAMS</ename> <sal>1100</sal> <deptno>20</deptno> </emp> <emp empno="7902" mgr="7566"> <ename>FORD</ename> <sal>3000</sal> <deptno>20</deptno> </emp> <emp empno="7369" mgr="7902"> <ename>SMITH</ename> <sal>800</sal> <deptno>20</deptno> </emp> <emp empno="7782" mgr="7839"> <ename>CLARK</ename> <sal>2450</sal> <deptno>10</deptno> </emp> <emp empno="7934" mgr="7782"> <ename>MILLER</ename> <sal>1300</sal> <deptno>10</deptno> </emp> <emp empno="7698" mgr="7839"> <ename>BLAKE</ename> <sal>2850</sal> <deptno>30</deptno> </emp> <emp empno="7499" mgr="7698"> <ename>ALLEN</ename> <sal>1600</sal> <deptno>30</deptno> </emp> <emp empno="7521" mgr="7698"> <ename>WARD</ename> <sal>1050</sal> <deptno>30</deptno> </emp> <emp empno="7654" mgr="7698"> <ename>MARTIN</ename> <sal>1250</sal> <deptno>30</deptno> </emp> <emp empno="7844" mgr="7698"> <ename>TURNER</ename> <sal>1500</sal> <deptno>30</deptno> </emp> <emp empno="7900" mgr="7698"> <ename>JAMES</ename> <sal>950</sal> <deptno>30</deptno> </emp> </employees> <departments> <dept deptno="10"> <dname>ACCOUNTING</dname> <loc>NEW YORK</loc> <budget>13000</budget> </dept> <dept deptno="20"> <dname>RESEARCH</dname> <loc>DALLAS</loc> <budget>15000</budget> </dept> <dept deptno="30"> <dname>SALES</dname> <loc>CHICAGO</loc> <budget>11000</budget> </dept> <dept deptno="40"> <dname>OPERATIONS</dname> <loc>BOSTON</loc> <budget>10000</budget> </dept> </departments> </company>Natürlich gibt es zu XQuery noch viel mehr zu sagen und zu schreiben; in einem anderen Post. Dennoch sollen diese Beispiel die Einsatzmöglichkeiten und die grundsätzliche Verwendung innerhalb der Datenbank aufzeigen.
Hallo,
AntwortenLöschenein Fall, in dem ich die XML-Optionen für sehr interessant halte, ist der Zugriff auf LONG-Spalten des Data Dictionary, da dabei die diversen Einschränkungen des Datentyps dem SQL-Zugriff oft im Weg stehen - z.B. weil es nicht möglich ist, Gruppen-Funktionen auf LONG-Werte anzuwenden. Bei Adrian Billington (http://www.oracle-developer.net/display.php?id=430) findet man neben einigen anderen Varianten der Konvertierung auch die Möglichkeit, einen LONG-Wert über DBMS_XMLGEN zu verarbeiten (unter http://martinpreiss.blogspot.com/2011/08/highvalue-angaben-fur-partitionen.html habe ich daraus dann ein Script zur Ermittlung von HIGH_VALUE-Angaben aus USER_TAB_PARTITIONS gebastelt; wobei DBMS_XMLGEN vermutlich nicht mehr unbedingt das XML-Mittel der Wahl ist).
Viele Grüße
Martin