Van SQL naar XML vanuit een Oracle database

Februari 2009 - Databestanden aanleveren vanuit de database kan op vele manieren. Veelal gebeurt dit nog op de conventionele manier, het 'platte ASCII bestand'. Het maken van een comma separated (CSV) bestand met behulp van de package UTL_FILE is hier een voorbeeld van. Een heel goed alternatief, zonder gebruik te hoeven maken van PL/SQL(!), is 'Oracle SQL/XML'.

Vanaf Oracle 9i beschikken we over de XML DB repository. Dit is de Oracle 'out-of-the-box' oplossing (een transparante interface) tussen de Oracle database en de daarin opgeslagen XML gegevens. XML documenten kunnen via SQL, of met behulp van internet de protocollen WebDaV en FTP, worden opgeslagen, gewijzigd of verwijderd. Verderop wordt besproken hoe je heel eenvoudig met behulp van het WebDav protocol een XML document kan openen.

Dit artikel laat een toepassing zien waarbij een aantal SQL/XML functies, XMLElement, XMLAttribute, XMLForest, XMLAgg worden gebruikt. Deze functies zijn de Oracle implementaties van de SQL/XML ISO/IEC 9075-14:2006 Standaard. We gaan zien ook hoe met behulp van een XML query een XMLtype view gemaakt wordt. Deze XMLtype view wordt gebruikt om XML bestanden beschikbaar te maken op het filesystem. Tevens laat ik zien hoe met behulp van het WebDav protocol het XML document vanuit de Oracle XML DB repository op het filesysteem beschikbaar is gekomen en kan worden gelezen.

In het voorbeeld wordt gebruik gemaakt van de standaard meegeleverde tabellen DEPARTMENTS en EMPLOYEES van het HR schema.

De eerste functie, XMLElement is tevens de meest gebruikte functie.

select xmlelement("achternaam",emp.last_name )
  from employees emp 
 where emp.last_name like 'A%'

Abel
Ande
Atkinson
Austin

Het eerst argument is een identifier, niet zomaar een string dus. Vandaar het gebruik van dubbele quotes om achternaam. Laat je die quotes achterwege, dan wordt de identifier (net zoals in kolomnamen van een tabel) naar hoofdletters omgezet. Het tweede argument is uiteraard een kolom van de tabel, in dit geval de last_name.

Je wilt natuurlijk meer dan 1 kolom in je XML file. Hiervoor is het mogelijk een de aanroep van XMLElement te nesten. Dus binnen de aanroep van XMLElement nogmaals, meerdere, aanroepen van XLMElement te plaatsen. Het volgende voorbeeld toont een aantal afdelingen met van die afdeling de naam en de bijhorende manager. Let ook op het gebruik van de functie XMLAttributes. Het department_id wordt in de XML file een attribute van het element . Het gebruik van de outerjoin verraad al dat niet iedere afdeling een manager heeft. Indien de waarde van een kolom NULL is, resulteert dit bij gebruik van XMLElement in een leeg element. In ons geval 2 maal een leeg element .

select XMLElement("afdeling", XMLAttributes(dep.department_id AS ID)
          ,XMLElement("naam",dep.department_name)
          ,XMLElement("manager",emp.last_name)
          )
from   departments dep
      ,employees emp
where  dep.manager_id = emp.employee_id(+)
  and  dep.department_name like 'IT%'

ITHunold
IT Support
IT Helpdesk

Indien een leeg element niet gewenst is kan dit op meerdere manieren worden ondervangen. Dit kan eenvouding door het CASE statement te gebruiken in je select.

select  XMLElement("afdeling", XMLAttributes(dep.department_id AS ID)
          ,XMLElement("naam",dep.department_name)
          ,CASE WHEN emp.last_name IS NULL THEN NULL
             ELSE  XMLElement("manager",emp.last_name) END
          )
from departments dep
    ,employees emp
where dep.manager_id = emp.employee_id(+)
  and dep.department_name like 'IT%'

ITHunold
IT Support
IT Helpdesk

Een andere optie is het gebruik van de functie XMLForest. XMLForest, de naam zegt het al, genereert een woud van elementen middels de aanroep van één functie. Bijkomend voordeel is dat lege elementen automatisch weggelaten worden. Dit resulteert in een zeer compacte notatie. Zeker als we, zoals in het volgende voorbeeld, meerdere kolommen willen tonen van EMPOYEES.

select  XMLElement("medewerker", XMLAttributes(emp.employee_id AS ID)
         ,XMLForest(emp.first_name AS "roepnaam"
                   ,emp.last_name AS "achternaam"
                   ,emp.email AS "emailadres"       
                   ,emp.phone_number AS "telefoon" 
                   )
                 )
    from employees emp   
    where emp.last_name like 'A%'

EllenAbel
EABEL011.44.1644.429267

SundarAnde
SANDE011.44.1346.629268

MozheAtkinson
MATKINSO650.124.6234

DavidAustin
DAUSTIN590.423.4569

Bovenstaand XML query levert per medewerker één XML document. We gaan nu per afdeling één XML document leveren met daarin verzamelt alle medewerkers bij deze afdelingen. We doen dit door het gebruik van de functie XMLAgg. XMLAgg is net als MIN, MAX, SUM en COUNT een verzamel functie. We moeten dus in onze XML query gebruik maken van een GROUP BY expressie.

select XMLElement
        ("afdeling",XMLAttributes(dep.department_name as "naam")
         ,XMLAgg(
                 XMLElement("medewerker"
                   ,XMLAttributes(emp.employee_id as "id")
                   ,XMLForest(emp.first_name AS "roepnaam"
                             ,emp.last_name AS "achternaam"
                             ,emp.email AS "emailadres"       
                             ,emp.phone_number AS "telefoon" 
                             )
                           )
                 )
       ) 
  from employees emp           
      ,departments dep
 where dep.department_id = emp.department_id
   and dep.department_name like 'A%' 
 group by dep.department_name


   
       Shelley
       Higgins
       SHIGGINS
       515.123.8080
   
   
      William
      Gietz
      WGIETZ
      515.123.8181
   


  
    Jennifer
    Whalen
    JWHALEN
    515.123.4444
    

En dan nu het leukste (vanaf Oracle9i Database Release 9.2.0.2 of hoger).

Je kunt een SQL/XML query gebruiken als basis voor een XMLtype view. Door deze XMLtype view te koppelen aan een resource in de XML DB repository wordt op het filesystem het bijhordende XML document gemaakt. Wanneer je dit XML document opent in een applicatie als b.v. Microsoft Excel of Internet Explorer, wordt de inhoud van dit document rechtstreeks vanuit de database aangemaakt. Je werkt dus altijd met de meest actuele gegevens!

Eerst de XMLtype view.

create or replace view afdelingen_xml of XMLTYPE
   with object id (extractValue(sys_nc_rowinfo$,'/afdeling/@naam')
                  ) from columns (dep.department_name) 
AS
select XMLElement
        ("afdeling",XMLAttributes(dep.department_name as "naam")
         ,XMLAgg(
                 XMLElement("medewerker"
                   ,XMLAttributes(emp.employee_id as "id")
                   ,XMLForest(emp.first_name AS "roepnaam"
                             ,emp.last_name AS "achternaam"
                             ,emp.email AS "emailadres"       
                             ,emp.phone_number AS "telefoon" 
                             )
                           )
                 )
       ) 
  from employees emp           
      ,departments dep
 where dep.department_id = emp.department_id
 group by dep.department_name

De inhoud van de view is uiteraard gelijk aan het resultaat de XML query waarbij voor het eerst XMLAgg gebruikt werd. De object id clause in het create view statement genereert een uniek object id voor iedere rij in de view. De view maakt per rij, dus voor iedere afdeling een XML document met daarin opgenomen alle medewerkers bij deze afdeling. Sys_nc_rowinfo$ is een verwijzing naar de huidige rij in de view. De XPath query '/afdeling/@naam' zorgt er voor dat waarde van het attribute 'naam' van het element gebruikt wordt om een unieke identificatie te genereren voor iedere rij in de view.

Nu we de XMLtype view AFDELINGEN_XML aangemaakt hebben kunnen we verder met het aanmaken van XML DB repository resources. Deze resources vormen uiteindelijk de XML documenten op het filesystem.

declare
  cursor c_afd is 
    select dep.department_name naam
      from employees emp           
          ,departments dep
     where dep.department_id = emp.department_id
     group by dep.department_name;

   xmlref REF XMLType;
   l_res boolean;
   	
begin
  for r_afd in c_afd loop
    select make_ref(afdelingen_xml,r_afd.naam)
      into xmlref
      from dual;
      l_res := DBMS_XDB.createResource('/public/'||r_afd.naam||'.xml'
                                      ,xmlref);
  end loop;
end;

Na uitvoeren van dit blok pl/sql code vindt je op je filesystem via het WebDav protocol in de map http://localhost:8080/public per afdeling het bijhorende XML bestand. Merk op dat voor het aanmaken van de XML bestanden de XMLtype view AFDELINGEN_XML niet gelezen wordt. De aanroep naar make_ref genereert een verwijzing naar een rij in de view. Deze verwijzing wordt gebruikt door de aanroep van DBMS_XDB.createResource. Doordat gebruikt wordt gemaakt van verwijzingen is het niet nodig de XML bestanden opnieuw aan te maken zodra er in de onderliggende tabellen gegevens wijzigen. Voor het raadplegen van de actuele stand in de database is opnieuw opvragen van de XML documenten voldoende.

Conclusie

De XML-db repository gecombineerd met de SQL/XML functies, XMLElement, XMLAttribute, XMLForest, XMLAgg zijn heel goed bruikbaar om op een relatief eenvoudige wijze gegevens uit de database beschikbaar te stellen op het filesystem. Bijkomend voordeel van SQL/XML boven het conventionele UTL_FILE is dat de XML bestanden altijd de meest actuele gegevens bevat.

Aan de slag dus!

Referenties

Eerdere relevante Whitebooks over XML in Oracle en XML DB:

• Oracle's XML Developers Toolkit - een introductie (maart 2003)
• XML ondersteuning in de Oracle9i database (april 2003)
• XML ondersteuning in PL/SQL (mei 2003)
• XML Schema in de Oracle XML DB (april 2005)
• XML DB Query (mei 2005)

Over de auteur
Jan Thuis is consultant bij Whitehorses en heeft ruim 19 jaar IT ervaring waarvan meer dan 15 jaar als Oracle specialist. In zijn vakgebied probeert hij het beste uit al zijn kennisgebieden te combineren om zo een juiste combinatie van flexibiliteit, kwaliteit en snelheid te realiseren.