Funcţii SQL pentru date XML în Oracle

In continuare se vor descrie câteva funcţii SQL utile la gestiunea documentelor xml:
  1. Generare atribute xml: XMLATTRIBUTES
  2. Generare fragmente, documente, colecţie de documente XMLType: XMLELEMENT, XMLFOREST, XMLCOLATTVAL, SYS_XMLGEN, XMLSEQUENCE
  3. Test de existenţa unui element, extragere de fragmente xml: EXISTSNODE, EXTRACT, EXTRACTVALUE
  4. Concatenare fragmente xml: XMLCONCAT
  5. Funcţii de agregare pentru documente xml: XMLAGG, SYS_XMLAGG
  6. Modificare document xml (adaugare, eliminare, modificare taguri): APPENDCHILDXML, INSERTCHILDXML, INSERTXMLBEFORE, INSERTXMLAFTER, DELETEXML, UPDATEXML
Precizare. Unele dintre funcţile SQL amintite mai sus sunt metode ale tipul XMLType (în lista de parametri lipseşte obiectul xmltype): EXISTSNODE, EXTRACT, APPENDCHILDXML, INSERTXMLBEFORE, DELETEXML.

Funcţia Sintaxa de utilizare
Descriere, exemple
XMLATTRIBUTES
XMLELEMENT
XMLFOREST 
  XMLATTRIBUTES(expresie AS denumire [,expresie AS denumire]...)
  XMLELEMENT(denumire [,functie_XMLATTRIBUTES], lista de expresii si/sau functii_XMLELEMENT)
  XMLFOREST(expresie [AS denumire] [, expresie [AS denumire]]...)
Funcţia XMLATTRIBUTES generează o listă de atribute. Fiecare atribut va avea o denumire şi o valoare (evaluarea unei expresii).

Funcţia XMLELEMENT construieşte un fragment dintr-un document XML cu tagul dat de valoarea primului argument. Acest fragment (pentru tagul precizat) poate conţine o listă de atribute construite cu funcţia XMLATTRIBUTES, şi o valoare, care poate fi o listă valori concatenate sau de taguri incluse, care se pot construi în mod asemănător (atribute, valori) prin argumentele care urmează.

Funcţia XMLFOREST produce o succesiune de taguri XML obţinute prin evaluarea unor expresii (fiecare argument este convertit la un document xml).
Dacă expresia este un scalar (denumire de coloana în tabel), atunci poate sa nu apară "AS" (denumirea tagului este denumirea coloanei).
Dacă expresia este un obiect, atunci este necesară denumirea tagului generat. Se obţine un tag cu numele precizat şi care conţine taguri pentru valoarea fiecărei proprietăţi.
Dacă expresia este o colecţie, atunci este necesară denumirea tagului generat. Se obţine un tag cu numele precizat şi care conţine taguri pentru valoarea fiecărei componente din colecţie.

Exemple (deoarece aceste funcţii sunt funcţii SQL, se va folosi instrucţiunea SELECT-SQL pentru exemple):

select XMLELEMENT("sectia", XMLATTRIBUTES(cod as "cod"), denumire) from sectii;
select XMLELEMENT("sectia", XMLATTRIBUTES(cod as "cod"), XMLELEMENT("denumire",denumire)) from sectii;

care poate avea ca rezultat: 

<sectia cod="1">Matematica </sectia>
<sectia cod="2">Informatica </sectia>

sau

<sectia cod="1"><denumire>Matematica </denumire></sectia>
<sectia cod="2"><denumire>Informatica </denumire></sectia>

iar instrucţiunea:

SELECT XMLELEMENT("sectia", XMLFOREST(cod, denumire))  FROM sectii WHERE cod=43;

este identică cu:

SELECT XMLELEMENT("sectia", XMLELEMENT("cod",cod), XMLELEMENT("denumire",denumire)) FROM sectii WHERE cod=43;

şi are ca rezultat:

<sectia><COD>43</COD><DENUMIRE>Baze de date</DENUMIRE></sectia>

Cu interogarea:

select XMLFOREST(cod, denumire) from sectii where cod=43;

se obţine:

<COD>43</COD><DENUMIRE>Baze de date</DENUMIRE>

Considerăm tipul TColStrazi şi colecţia TColStrazi: 

create or replace type TStrada as object(
  TipStrada number(2),
  Denumire varchar2(65),
  codpostal varchar2(6),
  sector number(1)
);

create or replace type TColStrazi as table of TStrada;

Cu următorul bloc PL/SQL:

declare
  s TStrada := TStrada(1,'a','123456',1);
  r XMLTYPE;
  a varchar2(2000);
begin
  dbms_output.enable;
  select XMLFOREST(s as strada) into r from dual;
  a:=r.getStringVal();
  dbms_output.put_line(a);
end;

se extrage rezultatul:

<STRADA>
   <TIPSTRADA>1</TIPSTRADA>
   <DENUMIRE>a</DENUMIRE>
   <CODPOSTAL>123456</CODPOSTAL>
   <SECTOR>1</SECTOR>
</STRADA>

Rezultatul instrucţiunii:

select XMLFOREST(strazi as listastrazi) from localitati where denumire='Cluj-Napoca';

va fi un document xml cu rădăcina <LISTASTRAZI>, iar pentru fiecare stradă se va include un document de tipul următor: 

<TSTRADA><TIPSTRADA>1</TIPSTRADA><DENUMIRE>Moldoveanu</DENUMIRE><CODPOSTAL>400682</CODPOSTAL></TSTRADA>
XMLCOLATTVAL 
XMLCOLATTVAL(expresie [AS alias] [, expresie [AS alias]]...)
Pentru fiecare argument de tipul "expresie [AS alias]" se construieşte un element XML cu:
  • denumirea column şi atributul name (acest atribut are ca valoare aliasul expresiei, sau denumirea coloanei - dacă denumirea este un nume de coloană)
  • valoarea elementului XML construit este valoarea expresie
Funcţia XMLCOLATTVAL crează un fragment XML obţinut prin concatenarea acestor elemente.

Exemplu: 

SELECT XMLCOLATTVAL(cod, denumire) from sectii

are ca rezultat: 

<column name = "COD">1</column><column name = "DENUMIRE">Matematica </column>  
<column name = "COD">2</column><column name = "DENUMIRE">Informatica </column>

SYS_XMLGEN 
  SYS_XMLGEN(expresie)
  SYS_XMLGEN(expresie, format)
Funcţia SYS_XMLGEN furnizează un document XML precedat de declaraţia <?xml version="1.0"?>. Dacă argumentul este o expresie, atunci acesta se va include în tagul ROW, iar dacă expresia este un nume de coloană, atunci se generează un tag cu numele coloanei şi valoarea acesteia.
Exemple: 
SELECT SYS_XMLGEN(sysdate) FROM dual;
SELECT SYS_XMLGEN(denumire) FROM sectii

va furniza: 

<?xml version="1.0"?> <ROW>17-MAY-14</ROW>

<?xml version="1.0"?> <DENUMIRE>Matematica </DENUMIRE> 
<?xml version="1.0"?> <DENUMIRE>Informatica </DENUMIRE>

Instrucţiunea: 

SELECT SYS_XMLGEN(XMLELEMENT("sectia", XMLELEMENT("cod",cod), XMLELEMENT("denumire",denumire))) FROM sectii

furnizează valori de forma:

<?xml version="1.0"?> <ROW> <sectia> <cod>1</cod> <denumire>Matematica</denumire></sectia> </ROW> 
<?xml version="1.0"?> <ROW> <sectia> <cod>2</cod> <denumire>Informatica</denumire></sectia> </ROW>

Pentru configurarea rezultatului se poate folosi un format (al doilea argument al funcţiei), care este o instaţă a obiectului XMLFORMAT. Atributele şi metodele acestui tip obiect se pot vedea cu următoarea comandă în SQL*Plus:

describe XMLFORMAT

Descrierea acestui tip obiect se poate vedea în Attributes of the XMLFormat Object

XMLSEQUENCE 
  XMLSEQUENCE(valoare_XMLTYPE)
  XMLSEQUENCE(CURSOR (instrucţiune_SELECT))
Funcţia XMLSEQUENCE furnizează o listă de fragmente XML (instanţe XMLTypes), care formează o valoare de tip XMLSEQUENCE (sau VARRAY of XMLType) folosind valoarea din argument. Dacă argumentul este de forma 
CURSOR (instructiune_SELECT)
atunci pentru fiecare înregistrare se construieşte un fragment de document XML, cu tagul rădăcină ROW pentru înregistrare şi taguri incluse corespunzătoare valorilor selectate.

Exemple: 

select XMLSEQUENCE(XMLELEMENT("sectia", XMLELEMENT("cod",cod), XMLELEMENT("denumire",denumire))) 
     from sectii where cod in (43,44);

generează: 

SYS.XMLTYPE(<sectia><cod>43</cod><denumire>Baze de date</denumire></sectia>)
SYS.XMLTYPE(<sectia><cod>44</cod><denumire>Sisteme distribuite în Internet</denumire></sectia>)

iar: 

select XMLSEQUENCE(CURSOR (SELECT cod, denumire from sectii where cod in (43,44))) from dual;

furnizează: 

SYS.XMLTYPE(
<ROW>
  <COD>43</COD>
  <DENUMIRE>Baze de date</DENUMIRE>
</ROW>
, 
<ROW>
  <COD>44</COD>
  <DENUMIRE>Sisteme distribuite în Internet</DENUMIRE>
</ROW>
)

Colecţia furnizată de funcţia XMLSEQUENCE se poate folosi ca sursă de date într-o instrucţiune SELECT dacă se converteşte această colecţie la un tabel cu funcţia TABLE (elementele din colecţie vor deveni înregistrări cu o singură coloană).

Exemplu: 

select * from TABLE(XMLSEQUENCE(CURSOR (SELECT cod, denumire from sectii where cod in (43,44))));
EXISTSNODE 
EXISTSNODE(instanta_XMLTYPE, expresie_XPath)
EXISTSNODE(instanta_XMLTYPE, expresie_XPath, sir_spatiu_de_nume)
Funcţia EXISTSNODE determină dacă în valoarea primului argument există valori specificate de şirul din al doilea argument (interpretat ca o expresie XPath).
Expresiile XPath se folosesc pentru a localiza unul sau mai multe taguri într-un document XML. Pentru XPath se poate consulta Documentatie XPath.
Dacă funcţia găseşte noduri în documentul XML, atunci furnizează valoarea 1, altfel valoarea este 0. Expresia XPath se caută în spaţiul de nume precizat (de al treilea argument) sau în orice spaţiu de nume (dacă lipseşte al treilea argument).

Exemple: 

select * from xml1 where EXISTSNODE(xml,'//SECTIE')=1;
select * from xml1 where EXISTSNODE(xml,'//SECTIE/COD')=1;
select * from xml1 where EXISTSNODE(xml,'//SECTIE[COD=43]')=1;
EXTRACT 
EXTRACT(instanta_XMLTYPE, expresie_XPath)
EXTRACT(instanta_XMLTYPE, expresie_XPath, sir_spatiu_de_nume)
Această funcţie analizează valoarea primului argument, considerat ca document XML, şi extrage fragmentul (sau succesiunea de fragmente) din acest document corespunzător expresiei XPath furnizată de al doilea argument.

Exemple: 

select EXTRACT(xml,'//SECTIE/DENUMIRE').getStringVal() 
  from xml1 where EXISTSNODE(xml,'//SECTIE[COD=43]')=1;
select EXTRACT(xml,'//SECTIE[COD=43]').getStringVal() 
  from xml1 where EXISTSNODE(xml,'//SECTIE[COD=43]')=1;
select EXTRACT(xml,'//SECTIE[COD=43]/DENUMIRE').getStringVal() 
  from xml1 where EXISTSNODE(xml,'//SECTIE[COD=43]')=1;

Cu primul exemplu se obţine o succesiune de taguri DENUMIRE, al doilea exemplu furnizează un tag SECTIE cu mai multe taguri incluse, iar al treilea exemplu dă un singur tag DENUMIRE.

EXTRACTVALUE 
EXTRACTVALUE(instanta_XMLTYPE, expresie_XPath)
EXTRACTVALUE(instanta_XMLTYPE, expresie_XPath, sir_spatiu_de_nume)
Funcţia EXTRACTVALUE este similară cu funcţia EXTRACT dar trebuie să se obţină o singură valoare (prin expresia Path) dintr-un singur nod, care poate fi: un nod text, un atribut, un element.

Exemplu: 

select EXTRACTVALUE(xml,'//SECTIE[COD=43]/DENUMIRE') 
  from xml1 where EXISTSNODE(xml,'//SECTIE[COD=43]')=1;

XMLCONCAT 
XMLCONCAT(instanta_XMLTYPE [,instanta_XMLTYPE] ...)
Această funcţie concatenează diverse instanţe XMLTYPE trecute ca argumente.

Exemple: 

SELECT XMLConcat(xmltype('<an>2015</an>'), xmltype('<luna>4</luna>'), xmltype('<zi>28</zi>')) as data FROM dual;
SELECT XMLConcat(xmltype('<an>'||TO_CHAR(sysdate, 'YYYY')||'</an>'), 
      xmltype('<luna>'||TO_CHAR(sysdate, 'MM')||'</luna>'), 
      xmltype('<zi>'||TO_CHAR(sysdate, 'DD')||'</zi>'), 
      xmltype('<ora>'||TO_CHAR(sysdate, 'HH24')||'</ora>'), 
      xmltype('<minut>'||TO_CHAR(sysdate, 'MI')||'</minut>'), 
      xmltype('<secunda>'||TO_CHAR(sysdate, 'SS')||'</secunda>')) as data 
    FROM dual;
SELECT XMLConcat(XMLElement("cod", cod), XMLElement ("denumire", denumire)) FROM sectii;

XMLAGG 
XMLAGG(instanta_XMLTYPE)
XMLAGG(instanta_XMLTYPE clauza_order_by)
Funcţia XMLAGG este o funcţie de agregare, deci în instrucţiunea SELECT poate apare clauza GROUP BY (fără această clauză toată sursa formează un grup). Inregistrările din fiecare grup pot să fie sortate.
Pentru fiecare înregistrare din grup se evaluează primul argument al funcţiei, care trebuie să fie de tipul XMLTYPE, şi se face o concatenare a acestor fragmente XML obţinute pentru înregistrările din grup.

Exemple: 

SELECT XMLAGG(XMLELEMENT("Sectia", denumire)) FROM sectii;
SELECT XMLELEMENT("Facultate", XMLAGG(XMLELEMENT("Sectia", denumire))) FROM sectii;

SELECT XMLELEMENT("sectia", XMLATTRIBUTES(sectia as "cod"), XMLAGG(XMLELEMENT("Grupa", trim(cod)))) 
    FROM grupe group by sectia;

SELECT XMLELEMENT("sectia", XMLATTRIBUTES(sectia as "cod"), 
         XMLAGG(XMLELEMENT("student", 
                   XMLATTRIBUTES(trim(nrmatricol) as "nrmatricol"), 
                   trim(nume) ||' ' || trim(prenume)
               ) order by nume)) 
       AS sectia 
    FROM studenti group by sectia;

SYS_XMLAGG 
  SYS_XMLAGG(instanta_XMLTYPE)
  SYS_XMLAGG(instanta_XMLTYPE, format)
Funcţia SYS_XMLAgg este asemănătoare cu XMLAGG, dar:
  • se adaugă un element rădăcină cu numele ROWSET pentru întregul rezultat şi un prefix XML
  • permite formatarea rezultatului cu ajutorul unei instanţe a tipului obiect XMLFormat
APPENDCHILDXML 
APPENDCHILDXML(sursa_XMLTYPE, expresie_XPath, fragment_XMLTYPE)
APPENDCHILDXML(sursa_XMLTYPE, expresie_XPath, fragment_XMLTYPE, sir_spatiu_de_nume)
Funcţia APPENDCHILDXML adaugă un fragment_XMLTYPE la nodul precizat de expresia Path dintr-o sursa XML şi furnizează rezultatul obţinut. Adăugarea se face la sfârşitul listei de elemente incluse în elementul precizat de expresia_Path. Această adăugare se face pentru fiecare apariţie a expresie Path.

Exemplu: 

select appendchildxml(
            xmltype('<A><B><C>10</C><D>Baze de date</D></B></A>'),
			'//B',
			xmltype('<E>Informatica</E>')
		) as c from dual;

va avea rezultatul: 

<A> <B> <C>10</C> <D>Baze de date</D> <E>Informatica</E> </B> </A>

INSERTCHILDXML 
INSERTCHILDXML(sursa_XMLTYPE, expresie_XPath, expresie_fiu, fragment_XMLTYPE)
INSERTCHILDXML(sursa_XMLTYPE, expresie_XPath, expresie_fiu, fragment_XMLTYPE, sir_spatiu_de_nume)
Funcţia INSERTCHILDXML înserează un fragment XML, ca un fiu la un părinte precizat de o expresie XPath. Această adăugare se face pentru fiecare apariţie a expresie Path. Funcţia furnizează rezultatul operaţiei.
Dacă numele "fiului" începe cu "@", atunci se înserează un atribut.
Poziţia unde se face înserarea nu e precizată.

Exemplu: 

select insertchildxml(
           xmltype('<A><B><C>10</C><D>Baze de date</D></B></A>'),
		   '//B',
		   'C1',
		   xmltype('<C1>Informatica</C1>')) as c from dual

va avea rezultatul: 

<A> <B> <C>10</C> <D>Baze de date</D> <C1>Informatica</C1> </B></A>

INSERTXMLBEFORE 
INSERTXMLBEFORE(sursa_XMLTYPE, expresie_XPath, fragment_XMLTYPE)
INSERTXMLBEFORE(sursa_XMLTYPE, expresie_XPath, fragment_XMLTYPE, sir_spatiu_de_nume)
Funcţia INSERTXMLBEFORE înserează un fragment XML într-o sursă XML în faţa poziţiei precizate de o expresie Path. Funcţia furnizează rezultatul operaţiei.

Exemplu: 

select INSERTXMLBEFORE(
               xmltype('<A><B><C>10</C><C>11</C><D>Baze de date</D></B></A>'),
			   '//B/C[2]',
			   xmltype('<C>x</C>')) as c from dual

va avea rezultatul 

<A> <B> <C>10</C> <C>x</C> <C>11</C> <D>Baze de date</D> </B></A>

INSERTXMLAFTER 
INSERTXMLAFTER(sursa_XMLTYPE, expresie_XPath, fragment_XMLTYPE)
INSERTXMLAFTER(sursa_XMLTYPE, expresie_XPath, fragment_XMLTYPE, sir_spatiu_de_nume)
Funcţia INSERTXMLAFTER înserează un fragment XML într-o sursă XML după poziţia precizată de o expresie Path. Funcţia furnizează rezultatul operaţiei.

Exemplu: 

select INSERTXMLAFTER(
               xmltype('<A><B><C>10</C><C>11</C><D>Baze de date</D></B></A>'),
			   '//B/C[2]',
			   xmltype('<C>x</C>')) as c from dual

va avea rezultatul 

<A><B><C>10</C><C>11</C><C>x</C><D>Baze de date</D></B></A>

DELETEXML 
DELETEXML(sursa_XMLTYPE, expresie_XPath)
DELETEXML(sursa_XMLTYPE, expresie_XPath, sir_spatiu_de_nume)
Funcţia DELETEXML şterge dintr-un document sursă XML nodul (sau nodurile) precizate de o expresie Path. Funcţia furnizează rezultatul operaţiei.

Exemplu: 

select DELETEXML(xmltype('<A><B><C>10</C><C>11</C><D>Baze de date</D></B></A>'),'//B/C[2]') as c from dual

va avea rezultatul 

<A> <B> <C>10</C> <D>Baze de date</D> </B> </A>

iar: 

select DELETEXML(
            xmltype('<A><B><C>10</C><C>11</C><D>Baze de date</D></B></A>'),
			'//B/C') as c from dual

va avea rezultatul 

<A> <B> <D>Baze de date</D> </B> </A>

UPDATEXML 
  UPDATEXML(sursa_XMLTYPE, expresie_XPath, expresie [, expresie_XPath, expresie]...)
  UPDATEXML(sursa_XMLTYPE, expresie_XPath, expresie [, expresie_XPath, expresie]..., sir_spatiu_de_numeg)
Funcţia UPDATEXML foloseşte o sursă XML şi o listă de perechi: (expresie_XPath, expresie). Elementul (sau elementele) precizat de "expresie_XPath" se înlocuieşte cu valoarea expresiei (vechiul element şi noul element trebuie să fie de acelaşi tip).

Exemplu: 

select UPDATEXML(
               xmltype('<A><B><C>10</C><C>11</C><D>Baze de date</D></B></A>'),
			   '//B/C',
			   xmltype('<X>?</X>')) as c from dual

va avea rezultatul: 

<A> <B> <X>?</X> <X>?</X> <D>Baze de date</D> </B> </A>

Instrucţiunea: 

select UPDATEXML(
           xmltype('<A><B><C a="1">10</C><C>11</C><D>Baze de date</D></B></A>'),
		   '//*[@a]',
		   xmltype('<X>?</X>')) as c from dual

va avea rezultatul 

<A> <B> <X>?</X> <C>11</C> <D>Baze de date</D> </B> </A>

Instrucţiunea: 

select UPDATEXML(
           xmltype('<A><B><C>10</C><C>11</C><D>Baze de date</D></B></A>'),
		   '//C[2]/text()',
		   'x') as c from dual

va avea rezultatul 

<A> <B> <C>10</C> <C>x</C> <D>Baze de date</D> </B> </A>

Precizare. Pentru tipul XMLType există metodele următoare, cu semnificaţia funcţilor SQL cu acelaşi nume descrise mai sus:

EXISTSNODE(expresie_XPath)
EXTRACT(expresie_XPath)
APPENDCHILDXML(expresie_XPath, fragment_XMLTYPE)
INSERTXMLBEFORE(expresie_XPath, fragment_XMLTYPE)
DELETEXML(expresie_XPath)

La lista de metode ale tipului XMLType adăugăm şi metoda: 
getRootElement return varchar2
care furnizează numele elementului rădăcină.
Următorul exemplu dă o variantă de adăugare a unui atribut la element:
declare
  x xmltype:=xmltype('<a>a<b>b</b><c>c</c></a>');
begin
  select insertchildxml(x, '//b', '@x', '2') into x from dual;
  dbms_output.put_line(x.getClobVal());
end;

iar rezultatul este:
<a>a<b x="2">b</b><c>c</c></a>