TP BDAV : Générer du XML dans Oracle

À rendre pour le 17/10/2010 23h30 via spiral, envoyer un fichier SQL commenté

Connexion au serveur

Accéder à oracle comme indiqué dans l'aide.

Mise en place

Utiliser le code suivant pour mettre en place le schéma de base de données utilisé pour ce TP.

-- 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 XMLmessage” avec un élémentcontenu” incluant le corps du message et un élémentdate” incluant la date du message.

Correction:

      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 XMLident” donné par l'attribut SQL ident, un attribut XMLprix” 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émentmessage” avec un attributid_message”, un attributdate”, un attributauteur” ayant pour valeur l'identifiant de l'auteur et enfin le corps du message comme contenu.

Correction:

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.

Correction:

      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émentCORPS” et l'email de l'auteur dans un élémentmailAuteur”.

Correction:

      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émentreponse” 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).

Correction:

      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?

Correction:

      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;

Les messages sans réponse possèdent un élément <reponse> vide.

Quelques vues

Créer une vue qui à chaque identifiant de message associe sa représentation XML telle que décrite ci-dessus.

Correction:

     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.

Correction:

      CREATE OR REPLACE VIEW messages_xml AS 
      SELECT xmlelement(name "messages",
             xmlagg(xml)) AS messages
      FROM message_id_xml;

On peut tester avec:

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('<objet>{$id}</objet>' 
                        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

Code HTML correspondant:

       <li>
 
         <p>Message 25 de <a href="mailto:bob@aol.com">bob@aol.com</a>:</p>
         <p>Bonjour, c'est moi</p>
         <p>Réponses: message 45 message 49</p>
       </li>

Correction:

SELECT XMLQuery('
let $msg := $m/message,
    $mail := $msg/mailAuteur/text()
return
<li>
  <p>Message {string($msg/@id_message)} de <a href="mailto:{$mail}">{$mail}</a></p>
  <p>{$msg/CORPS/text()}</p>
  <p>Réponses: 
  {
    for $rep in $msg/reponse[@id_reponse]
    return
    concat("message ",$rep/@id_reponse)
  }
  </p>
</li>
'
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.

Correction: Pour créer la première vue, il suffit d'ajouter create or replace view message_html as au début de la requête et d'ajouter AS ITEM après RETURNING CONTENT). Le code pour la deuxième vue est:

        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;

Pour tester:

        SELECT (html_code) FROM tous_messages_html