-- Schema
create table membre(id integer primary key, nom varchar2(40), email varchar2(100));
create table salle(id integer primary key, moderateur integer references membre(id), titre varchar2(100));
create table message(id integer primary key, auteur integer references membre(id), salle integer references salle(id), parent integer, date_envoi timestamp, titre varchar2(100), corps varchar2(1000));
alter table message add constraint fk_parent foreign key (parent) references message(id);
-- Donnees
insert into membre values(1,'Toto','toto@blagues.com');
insert into membre values(2,'Titi','titi@canaries.com');
insert into membre values(3,'Rominet','sylvestre@eating-birds.com');
insert into membre values(4,'Bugs Bunny','bugs@whatsupdoc.com');
insert into salle values(1,1,'Bonne humeur');
insert into salle values(2,NULL,'Dessins animés');
insert into message values(1,1,1,NULL,sysdate,'Toto à l''école','C''est Toto à l''école. Sa prof lui demande: '||CHR(10)||' - Toto quelle est la 5 ème lettre de l''alphabet? '||CHR(10)||' - Euh ?');
insert into message values(2,3,2,NULL,sysdate,'Les plus beaux personnages','Ce sont les chats, évidement !');
insert into message values(3,2,2,2,sysdate,'Re: Les plus beaux personnages','Non, ce sont les canaris !');
insert into message values(4,3,2,3,sysdate,'Re: Les plus beaux personnages','Les chats !');
insert into message values(5,2,2,4,sysdate,'Re: Les plus beaux personnages','Grand mère, à l''aide !!!');
insert into message values(6,4,2,2,sysdate,'Re: Les plus beaux personnages','Moi, je dis que ce sont les lapins !');
commit;
===== Extensions XML de SQL =====
Oracle fournit des fonctions SQL qui permettent de gérer du XML en tant que type de donnée (''XMLType'').
==== XMLElement ====
La fonction ''XMLElement'' permet de créer un élément XML. L'expression suivante permet de créer un élément nommé ''prix'' contenant un noeud texte dont la valeur est donnée par l'attribut ''prix_vente''
XMLElement(name "prix", prix_vente)
On peut en particulier l'utiliser dans une requête:
SELECT XMLElement(name "prix", prix_vente)
FROM ventes
Il est possible de spécifier plusieurs enfants pour un élément en les séparant par des virgules. Il est également possible d'imbriquer les appels à XMLElement afin de construire un morceau de document XML plus complexe.
On se replace dans le contexte d'une base de données sur le forum de discussions. Ecrire une requête qui pour chaque message génère un //élément// XML "''message''" avec un //élément// "''contenu''" incluant le corps du message et un //élément// "''date''" incluant la date du message.
select xmlelement(name "message",
xmlelement(name "contenu", corps),
xmlelement(name "date", date_envoi))
from message;
==== XMLAttributes ====
La fonction ''XMLAttributes'' permet d'ajouter des attributs à un élément. Le nom par défaut utilisé pour chaque attribut XML est le nom de l'attribut SQL dont on a pris la valeur. Il est également possible d'utiliser la notation ''AS un_nom'' afin de changer le nom de l'attribut XML (de manière similaire au nom des colonnes dans un SELECT). L'exemple suivant crée des éléments "''article''" avec un attribut XML "''ident''" donné par l'attribut SQL ''ident'', un attribut XML "''prix''" donné par l'attribut SQL ''prix_vente'' et un contenu texte donné par l'attribut SQL ''description'':
XMLElement(name "article",
XMLAttributes(ident,prix_vente as "prix"),
description)
Ecrire une requête qui pour chaque message génère un //élément// "''message''" avec un //attribut// "''id_message''", un //attribut// "''date''", un //attribut// "''auteur''" ayant pour valeur l'identifiant de l'auteur et enfin le corps du message comme //contenu//.
select xmlelement(name "message",
xmlattributes(id as "id_message",
date_envoi as "date",
auteur),
corps)
from message;
Que peut-on remarquer sur la casse des nom d'attributs? Modifier la requête (si besoin) pour que tous les noms soient en minuscules.
select xmlelement(name "message",
xmlattributes(id as "id_message",
date_envoi as "date",
auteur as "auteur"),
corps)
from message;
==== XMLForest ====
La fonction ''XMLForest'' crée pour chacun de ses arguments un élément (dont on peut optionnellement préciser le nom avec un ''AS'') qui contient du texte correspondant à la valeur calculée pour cet argument. Par exemple:
XMLElement(name "article",
XMLAttributes(ident,prix_vente as "prix"),
XMLForest(nom_article as "nom",description)
Modifier la requête précédente en utilisant cette fonction pour mettre le corps du message dans un //élément// "''CORPS''" et l'email de l'auteur dans un //élément// "''mailAuteur''".
select xmlelement(name "message",
xmlattributes(message.id as "id_message",
date_envoi as "date",
auteur as "auteur"),
xmlforest(corps, email as "mailAuteur")) as resultat
from message, membre
where auteur=membre.id;
==== XMLAgg ====
La fonction ''XMLAgg'' est une fonction d'agrégation pour le type XML (donc typiquement à utiliser en conjonction avec un ''GROUP BY''). Son effet est de mettre les unes à la suite des autres les différentes valeurs de l'expression passée en argument. La requête suivante illustre son fonctionnement:
SELECT XMLElement(name "departement",
XMLAttributes(deptno),
XMLAgg(XMLElement(name "employe",ename))) as RESULTAT
FROM scott.emp
GROUP BY deptno;
Modifier la requête précédente pour ajouter à chaque élément message un //élément// "''reponse''" avec comme contenu l'identifiant de la réponse, et cela pour chacune des réponses à ce message (on ne traitera que les messages ayant des réponses).
select xmlelement(name "message",
xmlattributes(m1.id as "id_message",
m1.date_envoi as "date",
m1.auteur as "auteur"),
xmlforest(m1.corps, email as "mailAuteur"),
XMLAgg(XMLElement(name "reponse",
XMLAttributes(m2.id as "id_reponse")))
) as resultat
from message m1, message m2, membre
where m1.id = m2.parent and m1.auteur=membre.id
group by m1.id, m1.date_envoi, m1.auteur, m1.corps, email;
Remplacer la jointure sur les messages par un LEFT OUTER JOIN. Que peut-on remarquer pour les messages n'ayant pas de réponse?
select xmlelement(name "message",
xmlattributes(m1.id as "id_message",
m1.date_envoi as "date",
m1.auteur as "auteur"),
xmlforest(m1.corps, email as "mailAuteur"),
XMLAgg(XMLElement(name "reponse",
XMLAttributes(m2.id as "id_reponse")))
) as xml
from message m1 left outer join message m2 on m1.id = m2.parent, membre
where m1.auteur=membre.id
group by m1.id, m1.date_envoi, m1.auteur, m1.corps, email;
create or replace view message_id_xml as
select m1.id, xmlelement(name "message",
xmlattributes(m1.id as "id_message",
m1.date_envoi as "date",
m1.auteur as "auteur"),
xmlforest(m1.corps, email as "mailAuteur"),
XMLAgg(XMLElement(name "reponse",
XMLAttributes(m2.id as "id_reponse")))
) as xml
from message m1 left outer join message m2 on m1.id = m2.parent, membre
where m1.auteur=membre.id
group by m1.id, m1.date_envoi, m1.auteur, m1.corps, email;
Créer une vue qui crée un document XML contenant l'ensemble des messages sous la forme donnée précédemment.
create or replace view messages_xml as
select xmlelement(name "messages",
xmlagg(xml)) as messages
from message_id_xml;
select (messages)
from messages_xml
===== XQuery dans SQL =====
La fonction ''XMLQuery'' permet d'exécuter une requête XQuery afin de générer un morceau de document XML. Il est possible de passer à la requête XQuery certaines valeurs XML construites à partir de la requête SQL via l'utilisation du mot clé ''PASSING''. L'exemple suivant illustre l'utilisation de cette fonction:
select XMLQuery('{$id} '
PASSING XMLElement(name "some_id", id_obj) AS "id"
RETURNING CONTENT)
from mes_objets;
Mettre au point une requête XQuery permettant de transformer une représentation XML des messages en une représentation HTML ayant l'aspect suivant:
Message 25 de bob@aol.com:
Bonjour, c'est moi
Réponses: message 45 message 49
Message 25 de bob@aol.com:
Bonjour, c'est moi
Réponses: message 45 message 49
SELECT XMLQuery('
let $msg := $m/message,
$mail := $msg/mailAuteur/text()
return
Message {string($msg/@id_message)} de {$mail}
{$msg/CORPS/text()}
Réponses:
{
for $rep in $msg/reponse[@id_reponse]
return
concat("message ",$rep/@id_reponse)
}
'
PASSING xml AS "m"
RETURNING CONTENT)
FROM message_id_xml;
Faire une vue de la requête précédente, puis créer une autre vue qui va générer le document HTML entier.
create or replace view tous_messages_html as
select xmlelement(name "html",
xmlelement(name "body",
xmlelement(name "ul",
xmlagg(item)
)
)
) as html_code
from message_html;
select (html_code) from tous_messages_html