XML în Oracle

Funcţia XMLTable

XMLTable

Această funcţie face parte din standardul SQL.

Sintaxa funcţiei este:

XMLTABLE(sir_XQuery [optiuni])  
XMLTABLE(spatiu_nume, sir_XQuery [optiuni])

Clauza PASSING cu semnificaţia de la XMLQuery

PASSING expr [AS alias] [, expr [AS alias]]...]

Clauza COLUMNS:

COLUMNS nume tip PATH 'expresie_Path' [, nume tip PATH 'expresie_Path'] ...

care precizează denumirea şi tipul coloanelor (prin clauza "nume tip") din tabelul relaţional obţinut ca rezultat şi o expresie Path care se evaluează pentru a determina valoarea coloanei. Fără această clauză se obţine numai o singură coloană, care este chiar documentul XML.

Exemple:

Selectarea unor coloane dintr-un document XML obţinut printr-o expresie XQuery.

SELECT *
 FROM XMLTable(
 'for $i in ora:view("sectii") 
    return <sectia>{$i//COD}{$i//DENUMIRE}</sectia>
 '
 COLUMNS cod VARCHAR(10) PATH 'COD',denumire varchar(100) path 'DENUMIRE');

In clauza FROM se poate utiliza o "sursă dinamică" de date. Plecând de la o linie curentă dintr-un tabel (sau view) curent se poate determina o astfel de sursă de înregistrări cu funcţia XMLTable. Cu linia curentă din sursa principală de date se stabileşte un context curent, în care se pot afla şi coloane de tipul XMLType. Aceste coloane (de tipul XMLType) pot fi folosite ca surse pentru declaraţiile XQuery din funcţia XMLTable.
Pentru o înregistrare din sursa principală se pot trece la expresiile XMLTable, prin clauza PASSING, o mulţime de valori, care pot fi documente XML. Prin expresiile XQuery se pot parcurge aceste documente şi se poate obţine o succesiune de taguri, care sunt considerate ca înregistrări furnizate de XMLTable. Intre înregistrarea curentă din sursa principală şi înregistrările astfel determinate se face un INNER JOIN.
Intre înregistrarea curentă şi mulţimea de înregistrări determinate se poate utiliza şi alt operator de join, de exemplu LEFT OUTER JOIN.
Exemplu:

SELECT t.*
FROM xml1 x,
  XMLTABLE (
    'for $r in $facultate//SECTIE
     return $r'
  PASSING x.xml AS "facultate"
  COLUMNS cod integer path 'COD', denumire varchar(100) PATH 'DENUMIRE') t
where c='2'

Explicaţie.

Cu "FROM xml1 x ... where c='2'" se selectează o înregistrare din sursa de date xml1, care are şi coloana XML de tip XMLType
Pentru această înregistrare se precizează (evaluează) o sursă dinamică obţinută cu XMLTable
La expresia XQuery din XMLTable se trece coloana xml prin PASSING x.xml AS "facultate", deci variabila $facultate va putea fi folosită în această expresie (valoarea ei va fi un document XML)
Prin declaraţiile (clauzele) 'for $r in $facultate//SECTIE return $r' se furnizează o succesiune de taguri de forma următoare, care sunt considerate ca înregistrări:
```
<SECTIE num="1"> <COD>1</COD> <DENUMIRE>Matematica</DENUMIRE> </SECTIE>
```
Dacă expresia XPath este deja o succesiune de astfel de taguri (ca în exemplul de mai sus), atunci nu mai este necesară declaraţia (expresia) "for ...", deci interogarea de mai sus are acelaşi rezultat ca următoarea:
```
SELECT t.*
FROM xml1 x,
  XMLTABLE (
    '$facultate//SECTIE'
  PASSING x.xml AS "facultate"
  COLUMNS cod integer path 'COD', denumire varchar(100) PATH 'DENUMIRE') t
where c='2'
```
Din tagurile de mai sus se pot selecta coloane în rezultatul instrucţiunii SELECT, conform clauzei COLUMNS
Dacă pentru o coloană precizată în COLUMNS nu se găseşte valoare pentru expresia din clauza PATH, atunci valoarea coloanei va fi null

Valorile obtinuţe pot să fie de tipul XMLType (pentru afişare în exemplul următor s-a făcut o conversie la un şir de caractere):

SELECT t.xml.getStringVal() v
FROM xml1 x,
  XMLTABLE (
    'for $r in $facultate
     return $r//SECTIE'
  PASSING x.xml AS "facultate"
  COLUMNS xml XMLType PATH '.') t
where c='2';

In tabelul XML1 folosit mai sus, pentru c='4' se obţine, în coloana xml, documentul mondial.xml. Vom folosi acest document pentru a obţine diverse date. Câteva elemente din acest document sunt date în continuare.

<country car_code="RO" area="237500" capital="cty-cid-cia-Romania-2"
        memberships="org-ACCT org-BIS org-BSEC org-CEI org-CE org-CCC org-ECE org-EBRD org-EU org-FAO org-G-9 org-G-77 
                     org-IAEA org-IBRD org-ICAO org-ICFTU org-Interpol org-IFRCS org-IFC org-IFAD org-ILO org-IMO org-Inmarsat 
                     org-IMF org-IOC org-IOM org-ISO org-ICRM org-ITU org-Intelsat org-NAM org-ANC org-NSG org-OSCE org-OAS 
                     org-PFP org-PCA org-UN org-UNAVEM-III org-UNESCO org-UNIDO org-UNIKOM org-UPU org-WEU org-WCL org-WFTU 
                     org-WHO org-WIPO org-WMO org-WToO org-WTrO org-ZC">
  <name>Romania</name>
  <population>21657162</population>
  <population_growth>-1.21</population_growth>
  <infant_mortality>23.2</infant_mortality>
  <gdp_total>105700</gdp_total>
  <gdp_agri>19.6</gdp_agri>
  <gdp_ind>36.3</gdp_ind>
  <gdp_serv>44.1</gdp_serv>
  <inflation>25</inflation>
  <indep_date>1881-01-01</indep_date>
  <government>republic</government>
  <encompassed continent="europe" percentage="100"/>
  <ethnicgroups percentage="0.4">German</ethnicgroups>
  <ethnicgroups percentage="1.6">Roma</ethnicgroups>
  <ethnicgroups percentage="8.9">Hungarian</ethnicgroups>
  <ethnicgroups percentage="89.1">Romanian</ethnicgroups>
  <religions percentage="6">Roman Catholic</religions>
  <religions percentage="6">Protestant</religions>
  <religions percentage="70">Christian Orthodox</religions>
  <border country="SRB" length="476"/>
  <border country="H" length="443"/>
  <border country="UA" length="531"/>
  <border country="BG" length="608"/>
  <border country="MD" length="450"/>
  <province id="prov-cid-cia-Romania-2" capital="cty-cid-cia-Romania-Alba-Iulia"
            country="RO">
     <name>Alba</name>
     <area>6231</area>
     <population>428000</population>
     <city id="cty-cid-cia-Romania-Alba-Iulia" is_state_cap="yes" country="RO"
           province="prov-cid-cia-Romania-2">
        <name>Alba Iulia</name>
     </city>
  </province>
  .....
</country>

Se cere denumirea tagurilor corespunzătoare categoriilor de informaţii din document:

 select col,count(*) from (SELECT t.*
  FROM xml1 x,
  XMLTABLE 
  (
    'for $e in $xml/mondial/* return <a>{$e/name()}</a>'
     PASSING x.xml AS "xml" COLUMNS col varchar(20) PATH '.'
  ) t
  where c='4'
 ) group by col order by 1

Rezultatul care se obţine este:

COL        COUNT(*)     
---------------------------------------------------------------------------------------------------- ---------------------- 
continent         5  
country         238
desert           63 
island          276
lake            130
mountain        240
organization    153
river           218
sea              34

Pentru fiecare ţară se cere codul, denumirea, suprafaţa şi populaţia:

SELECT t.*
FROM xml1 x,
XMLTABLE (
  '$xml//country'
   PASSING x.xml AS "xml"
   COLUMNS 
      cod varchar(3) PATH '@car_code', 
      denumire varchar(50) PATH 'name', 
      suprafata integer PATH '@area',
      populatia integer PATH 'population'
) t
where c='4'
order by 2;

Pentru fiecare ţară se cere denumirea, capitala şi populaţia capitalei:

SELECT t.*
FROM xml1 x,
XMLTABLE (
  'for $c in $xml//country
     for $x in $c//city
       where $x/@is_country_cap="yes"
       return <tara><nume>{$c/name}</nume><capitala populatia="{$x/population/text()}">{$x/name}</capitala></tara>
  '
   PASSING x.xml AS "xml"
   COLUMNS 
      denumire varchar(50) PATH 'nume',
      capitala varchar(50) PATH 'capitala',
      populatia_cap integer PATH 'capitala/@populatia'
   ) t
where c='4' order by 1

SELECT t.*
FROM xml1 x,
XMLTABLE (
  'for $c in $xml//country
     let $x:=$c//city[@is_country_cap="yes"]
     return <tara><nume>{$c/name}</nume><capitala populatia="{$x/population/text()}">{$x/name}</capitala></tara>
  '
   PASSING x.xml AS "xml"
   COLUMNS 
      denumire varchar(50) PATH 'nume',
      capitala varchar(50) PATH 'capitala',
      populatia_cap integer PATH 'capitala/@populatia'
   ) t
where c='4' order by 1

Pentru fiecare ţară se cere un document XML cu informaţii despre vecini:

SELECT t.denumire,t.granita.getStringVal()
FROM xml1 x,
  XMLTABLE ('$xml//country'
  PASSING x.xml AS "xml"
  COLUMNS 
  denumire varchar(50) PATH 'name', 
  granita XMLType PATH 'border'
  ) t
where c='4'
order by 1;

Se cer toate vecinătăţile de ţări:

SELECT t.*
FROM xml1 x,
  XMLTABLE (
'for $t in $xml//country
  for $b in $t/border
    return <tara cod="{$t/@car_code}" vecin="{$b/@country}" lungime="{$b/@length}"/>
'
  PASSING x.xml AS "xml"
  COLUMNS 
  tara varchar(3) PATH '@cod', 
  vecin varchar(3) PATH '@vecin',
  lungime integer PATH '@lungime'
  ) t
where c='4'
order by 1,2;

Denumirea oraşelor din România, populaţia şi judeţul de care aparţine:

SELECT t.oras,t.judet, decode(t.populatia,null,0,t.populatia) as populatia
FROM xml1 x,
  XMLTABLE (
'for $t in $xml//country
  for $p in $t/province
    for $c in $p/city
      where $t/@car_code="RO"
      return <oras><nume>{$c/name/text()}</nume>
             <populatia>{$c/population/text()}</populatia>
             <judet>{$p/name/text()}</judet></oras>
'
  PASSING x.xml AS "xml"
  COLUMNS 
  oras varchar(50) PATH 'nume',
  populatia integer PATH 'populatia',
  judet varchar(50) PATH 'judet'
  ) t
where c='4'
order by 3 desc