XMLQuery(sir_XQuery [PASSING expr [AS alias] [, expr [AS alias]]...] RETURNING CONTENT)
SELECT XMLQuery('1, 2 + 3, "a", 10 to 14, <C><A>a</A><B>b</B></C>' RETURNING CONTENT) AS v FROM DUAL;
1 5 a 10 11 12 13 14<C><A>a</A><B>b</B></C>
SELECT XMLQuery('for $x in ("Matematica;","Informatica;","Matematica informatica;") return $x' RETURNING CONTENT) AS v from dual
are valoarea:
Matematica; Informatica; Matematica informatica;
SELECT XMLQuery('for $i in (11 to 20) let $v := $i*$i return $v' RETURNING CONTENT) as "patrate" FROM dual
are valoarea:
121 144 169 196 225 256 289 324 361 400
SELECT XMLQuery( 'for $i in (1 to 20) for $j in ($i+1 to 20) for $k in ($j+1 to 20) where $i*$i+$j*$j=$k*$k return <v>{$i},{$j},{$k}</v>' RETURNING CONTENT) as "v" FROM dual;
<v>3,4,5</v><v>5,12,13</v><v>6,8,10</v><v>8,15,17</v><v>9,12,15</v><v>12,16,20</v>
SELECT XMLQuery(' <ul> { for $x in (1 to 10) order by $x return <li>{$x}</li> } { for $x in (11 to 15) order by $x return <li>{$x}</li> } </ul> ' RETURNING CONTENT) FROM dual;
<ul><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li></ul>
SELECT XMLQuery(' let $x := ("a","b","c","d","e") let $n := count($x) return <rez><nr>{$n}</nr>{for $i in $x return <elem>{$i}</elem>}</rez> ' RETURNING CONTENT) as "v" FROM dual
<rez><nr>5</nr><elem>a</elem><elem>b</elem><elem>c</elem><elem>d</elem><elem>e</elem></rez>
SELECT XMLQuery(' let $c := <cumparaturi> <tip denumire="mere"><kg>10</kg></tip> <tip denumire="pere"><kg>3</kg></tip> <tip denumire="banane"><kg>5</kg></tip> <tip denumire="portocale"><kg>5</kg></tip> </cumparaturi> let $x := $c//kg/text() let $s := sum($x) return string($s)' RETURNING CONTENT) as "kg" FROM dual;
CREATE DIRECTORY FISIERE AS 'C:\oracle\global\';
CREATE OR REPLACE PROCEDURE insertXML (cod in varchar2, fisier in varchar2) AS f BFILE; v CLOB; BEGIN f := BFILENAME('FISIERE', fisier); if (DBMS_LOB.FILEEXISTS(f)=1) then DBMS_LOB.createtemporary (v, TRUE); DBMS_LOB.fileopen(f, DBMS_LOB.FILE_READONLY); DBMS_LOB.loadfromfile(v, f, DBMS_LOB.GETLENGTH(f)); DBMS_LOB.fileclose(f); INSERT INTO xml1 (c, xml) VALUES (cod, XMLTYPE(v)); COMMIT; DBMS_LOB.freetemporary (v); else DBMS_OUTPUT.PUT_LINE(fisier || 'este eronat'); end if; END;
Cu blocul următor se includ fişierele amintite în tabel:
BEGIN insertXML('3','periodicTable.xml'); insertXML('4','mondial.xml'); END;
<PERIODIC_TABLE> <ATOM> <NAME>Actinium</NAME> <ATOMIC_WEIGHT>227</ATOMIC_WEIGHT> <ATOMIC_NUMBER>89</ATOMIC_NUMBER> <OXIDATION_STATES>3</OXIDATION_STATES> <BOILING_POINT UNITS="Kelvin">3470</BOILING_POINT> <SYMBOL>Ac</SYMBOL> <DENSITY UNITS="grams/cubic centimeter" TEMP="300k"> 10.07 </DENSITY> <ELECTRON_CONFIGURATION>[Rn] 6d1 7s2</ELECTRON_CONFIGURATION> <ELECTRONEGATIVITY>1.1</ELECTRONEGATIVITY> <ATOMIC_RADIUS UNITS="Angstroms">1.88</ATOMIC_RADIUS> <ATOMIC_VOLUME UNITS="cubic centimeters/mole"> 22.5 </ATOMIC_VOLUME> <SPECIFIC_HEAT_CAPACITY UNITS="Joules/gram/degree Kelvin"> 0.12 </SPECIFIC_HEAT_CAPACITY> <IONIZATION_POTENTIAL>5.17</IONIZATION_POTENTIAL> <THERMAL_CONDUCTIVITY UNITS="Watts/meter/degree Kelvin" TEMP="300k"> 12 </THERMAL_CONDUCTIVITY> </ATOM> <ATOM> <NAME>Aluminum</NAME> <ATOMIC_WEIGHT>26.98154</ATOMIC_WEIGHT> <ATOMIC_NUMBER>13</ATOMIC_NUMBER> <OXIDATION_STATES>3</OXIDATION_STATES> <BOILING_POINT UNITS="Kelvin">2740</BOILING_POINT> <MELTING_POINT UNITS="Kelvin">933.5</MELTING_POINT> <SYMBOL>Al</SYMBOL> <DENSITY UNITS="grams/cubic centimeter" TEMP="300k"> 2.7 </DENSITY> <ELECTRON_CONFIGURATION>[Ne] 3s2 p1</ELECTRON_CONFIGURATION> <COVALENT_RADIUS UNITS="Angstroms">1.18</COVALENT_RADIUS> <ELECTRONEGATIVITY>1.61</ELECTRONEGATIVITY> <ATOMIC_RADIUS UNITS="Angstroms">1.43</ATOMIC_RADIUS> <HEAT_OF_VAPORIZATION UNITS="kilojoules/mole"> 290.8 </HEAT_OF_VAPORIZATION> <ATOMIC_VOLUME UNITS="cubic centimeters/mole"> 10 </ATOMIC_VOLUME> <HEAT_OF_FUSION UNITS="kilojoules/mole"> 10.7 </HEAT_OF_FUSION> <IONIZATION_POTENTIAL>5.986</IONIZATION_POTENTIAL> <SPECIFIC_HEAT_CAPACITY UNITS="Joules/gram/degree Kelvin"> 0.9 </SPECIFIC_HEAT_CAPACITY> <THERMAL_CONDUCTIVITY UNITS="Watts/meter/degree Kelvin" TEMP="300k"> 237</THERMAL_CONDUCTIVITY> </ATOM> ..... </PERIODIC_TABLE>
Exemplu de interogare (se cere numele şi simbolul elementelor):
SELECT XMLQuery(' for $element in $X1//ATOM let $d:=$element/NAME let $c:=$element/SYMBOL return <element>{$c}{$d}</element>' PASSING XML AS X1 RETURNING CONTENT) as "v" FROM xml1 where c='3';
Explicaţie.
Exemplu. Pentru documentul amintit mai sus se cere numărul de elemente:
SELECT XMLQuery(' for $element in $X1 let $n := count($element//ATOM) return $n' PASSING XML AS X1 RETURNING CONTENT) as "v" FROM xml1 where c='3';
SELECT XMLQuery(' for $t in $X1//country let $n:= $t/name let $p:=$t/population order by $n return <tara>{$n}{$p}</tara>' PASSING XML AS X1 RETURNING CONTENT) as "v" FROM xml1 where c='4';
SELECT XMLQuery(' for $t in $X1//country let $n:= $t/name let $nr:=count($t//city) order by $n return <tara>{$n}<nrorase>{$nr}</nrorase></tara>' PASSING XML AS X1 RETURNING CONTENT) as "v" FROM xml1 where c='4';
SELECT XMLQuery(' for $t in $X1//country let $n:= $t/name order by $n return <country>{$n} {for $c in $t//city return <city>{$c/name}<population>{$c/population/text()}</population></city> } </country>' PASSING XML AS X1 RETURNING CONTENT) as "v" FROM xml1 where c='4'
SELECT XMLQuery(' for $t in $X1//country let $n:=$t/name/text() where $t/name/text()="Romania" return <lista>{ for $c in $t//city let $judet:=$c/../name/text() return <oras><nume>{$c/name/text()}</nume><judet>{$judet}</judet><populatia>{$c/population/text()}</populatia></oras> } </lista> ' PASSING XML AS X1 RETURNING CONTENT) as "v" FROM xml1 where c='4';
ora:view([schema,] {tabel | view})care caută în schema precizată (ca prim argument, sau schema curentă prin lipsă) un tabel sau view cu un anumit nume (prin evaluarea celui de-al doilea argument) şi furnizează o colecţie de documente xml. Un element din colecţie corespunde la o înregistrare şi este format din concatenarea de taguri pentru coloanele găsite. Tagul pentru o coloană este denumirea sau aliasul coloanei.
SELECT XMLQuery(' <facultate> {for $c in ora:view("INFSTUD") let $n := $c//NUME, $p := $c//PRENUME order by $n,$p return <student>{normalize-space($n)}{" "}{normalize-space($p)}</student> } </facultate>' RETURNING CONTENT) as "s" FROM dual;