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 10000Dabei 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 30Man 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