====== TP Génération de XML via SQL ======

Ce TP sera réalisé sous Oracle SQLDevelopper (disponible en salle TP Windows). Chaque étudiant se verra attribuer un compte oracle via tomuss (cf [[enseignement:aide:oracle|aide Oracle]]).

Ce TP n'est pas à rendre.
Des questions pourront cependant être posées en examen sur les techniques abordées dans ce TP.
===== Introduction =====

L'objectif de ce TP est de pratiquer la création de (fragments de) documents XML directement à partir de données relationnelles en SQL. Le TP consiste dans un premier temps à lire et comprendre le TP "[[enseignement:tp:bd:tp-xml-sql|Générer du XML dans Oracle]]". Dans un deuxième temps, on créera des vues à interroger. Enfin on répondra aux questions dans le fichier de réponses en s'inspirant du TP cité ci-dessus.

  * [[http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions002.htm#SQLRF51185|Doc Oracle sur les fonctions XML]]
===== Vues relationnelles =====

Les données relationnelles seront accessibles en créant dans votre compte les vues suivantes:
<code sql vues_forets.sql>
create view arbres as 
select * from ECOQUERY.arbres_foret_2011;

create view documentation as 
select * from ECOQUERY.documentation_2011;
</code>

Le {{:enseignement:tp:bd:tp-xml-sql:2009-2011_ifn_db_arbres_pf.pdf|document de description arbres}} décrit les attributs de la vue ''arbres''.

La vue ''documentation'' décrit, pour un certain nombre d'attributs (''DONNEE'') de la table arbre, une correspondance entre la valeur (''CODE'') dans la table et une description textuelle (''LIBELLE''). Par exemple la liste des couples valeur/description pour l'attribut ''ESPAR'' de la vue ''arbres'' peut être obtenue par la requête suivante:
<code sql>
SELECT code, libelle
FROM documentation
WHERE donnee = 'ESPAR';
</code>

<note tip>Il est également possible de réaliser ce tp sous postgresql. Il convient alors d'insérer les données dans la base via le script suivant: {{:enseignement:tp:bd:tp-xml-sql:forets-postgres.sql.gz|}}</note>
===== Génération de XML =====

Il s'agit d'écrire des requêtes SQL afin de générer du XML en réponse aux requêtes en français ci-dessous. Ce XML se situera toujours dans une colonne ''vxml'' dans le résultat de la requête. Il sera toujours conforme à la DTD indiquée dans la question.
----
**Exemple**

"Donner le titre de chaque film, un film étant identifié par son ''id''."\\
DTD:<code xml><!DOCTYPE titre [
<!ELEMENT titre (#PCDATA)>
]></code>Réponse:<code sql>SELECT XMLElement(name "titre", titre) as vxml, id FROM film</code>
----


==== Questions ====

  - Donner, pour chaque arbre de la parcelle 613376 (''IDP''), sa hauteur totale (''HTOT''). <code xml><!DOCTYPE arbre [
<!ELEMENT arbre(hauteur)>
<!ELEMENT hauteur (#PCDATA)>
]></code>27 ou 8 réponses selon que l'on met des hauteurs vides ou non.
  - Reprendre la question précédente et ajouter un attribut ''id'' (valeur obtenue via ''A'') dans l'élément ''arbre''.<code xml><!DOCTYPE arbre [
<!ELEMENT arbre(hauteur)>
<!ATTLIST arbre id CDATA #REQUIRED>
<!ELEMENT hauteur (#PCDATA)>
]></code>27 ou 8 réponses selon que l'on met des hauteurs vides ou non.
  - Ajouter, lorsqu'elle est disponible, le nom de l'espèce (code: ''ESPAR'', nom obtenu via la vue ''documentation''((Attention, les codes sont réemployés ''select * from documentation where code = '02';'' renvoie 9 résultats))), toujours pour la parcelle 613376. Remarque: il y a deux arbres dont le code espece n'est pas dans documentation. On doit avoir le même nombre de résultats qu'à la question précédente (utiliser e.g. un [[http://www.geekphilip.com/2012/04/01/visual-explanation-of-sql-joins/|XXX OUTER JOIN]]). <code xml><!DOCTYPE arbre [
<!ELEMENT arbre(hauteur,espece?)>
<!ATTLIST arbre id CDATA #REQUIRED>
<!ELEMENT hauteur (#PCDATA)>
<!ELEMENT espece (#PCDATA)>
]></code>
  - Donner pour chaque espèce (valeur distincte ''ESPAR'') son nom (si disponible) et le nombre d'arbres de cette espece.<code xml><!DOCTYPE espece [
<!ELEMENT espece (nom?,quantite)>
<!ATTLIST espece code CDATA #REQUIRED>
<!ELEMENT nom (#PCDATA)>
<!ELEMENT quantite (#PCDATA)>
]> </code>115 réponses((:!: SQLDeveloper n'affiche que les 50 première si on ne fait pas défiler les résultats))
  - Pour chaque parcelle dont l'identifiant (''IDP'') est inférieur ou égal à 600200, donner l'identifiant de la parcelle et la liste //sans doublons// des espèces présentes dans cette parcelle (son code ''ESPAR'' sous forme d'attribut XML et pour celles dont on le connait, leur nom sous forme de texte).<code xml><!DOCTYPE parcelle [
<!ELEMENT parcelle (espece*)>
<!ATTLIST parcelle id CDATA #REQUIRED>
<!ELEMENT espece (#PCDATA)>
<!ATTLIST espece code CDATA #REQUIRED>
]></code>13 réponses
  - Pour chaque parcelle dont l'identifiant (''IDP'') est inférieur ou égal à 600200, donner l'identifiant de la parcelle, le nombre d'arbres de cette parcelle et la liste //sans doublons// des espèces présentes dans cette parcelle (son code ''ESPAR'' sous forme d'attribut XML et pour celles dont on le connait, leur nom sous forme de texte).<code xml><!DOCTYPE parcelle [
<!ELEMENT parcelle (nb,espece*)>
<!ATTLIST parcelle id CDATA #REQUIRED>
<!ELEMENT nb (#PCDATA)>
<!ELEMENT espece (#PCDATA)>
<!ATTLIST espece code CDATA #REQUIRED>
]></code>13 réponses
  - Pour les espèces présentes dans entre 2 et 5 parcelles, donner leur code, leur nom et la liste //sans doublons// des parcelles (''IDP'') contenant des arbres de cette espèce.<code xml><!DOCTYPE espece [
<!ELEMENT espece (nom,parcelle+)>
<!ATTLIST espece code CDATA #REQUIRED>
<!ELEMENT nom (#PCDATA)>
<!ELEMENT parcelle EMPTY>
<!ATTLIST parcelle id CDATA #REQUIRED>
]></code>15 réponses
  - Reprendre la question précédente en ajoutant le nombre d'arbres de l'espèce concernée dans la parcelle concernée sous forme de texte dans l'élément ''parcelle''.<code xml><!DOCTYPE espece [
<!ELEMENT espece (nom,parcelle+)>
<!ATTLIST espece code CDATA #REQUIRED>
<!ELEMENT nom (#PCDATA)>
<!ELEMENT parcelle (#PCDATA)>
<!ATTLIST parcelle id CDATA #REQUIRED>
]></code>15 réponses


<note tip>Pour les requêtes complexes, mettez d'abord au point une requête SQL //classique// dont vous modifierez ensuite la clause ''SELECT'' pour produire du XML </note>