drop table archive_commande_XML;
CREATE TABLE archive_commande_XML(comm XMLType);

INSERT INTO archive_commande_XML values(XMLType('<commande id_commande="4" date_achat="07/12/2006" prix_total="1200">
<client id_client="2" nom="Dupuis"/>
<commercial id_commercial="2" nom="Latour"/>
<ligne_commande num="1">
<produit id_produit="2" libelle="Table"
prix_unitaire="60" quantite="10" prix_total="600"/>
</ligne_commande>
<ligne_commande num="2">
<produit id_produit="1" libelle="Chaise"
prix_unitaire="15" quantite="40" prix_total="600"/>
</ligne_commande>
</commande>'));

INSERT INTO archive_commande_XML values(XMLType('<commande id_commande="1" date_achat="03/12/2005" prix_total="1000">
<client id_client="1" nom="Dupont"/>
<commercial id_commercial="2" nom="Latour"/>
<ligne_commande num="1">
<produit id_produit="2" libelle="Table"
prix_unitaire="60" quantite="5" prix_total="300"/>
</ligne_commande>
<ligne_commande num="2">
<produit id_produit="1" libelle="Chaise"
prix_unitaire="15" quantite="40" prix_total="600"/>
</ligne_commande>
<ligne_commande num="3">
<produit id_produit="1" libelle="Lampe"
prix_unitaire="5" quantite="20" prix_total="100"/>
</ligne_commande>
</commande>'));

INSERT INTO archive_commande_XML values(XMLType('<commande id_commande="2" date_achat="04/12/2005" prix_total="300">
<client id_client="1" nom="Durand"/>
<commercial id_commercial="2" nom="Latour"/>
<ligne_commande num="1">
<produit id_produit="2" libelle="Table"
prix_unitaire="60" quantite="5" prix_total="300"/>
</ligne_commande>
</commande>'));

-- On affiche la date et le montant de chaque commande 
select substr(extract(comm, '/commande/@date_achat').getStringVal(),1,10) date_achat,
	extract(comm, '/commande/@prix_total').getStringVal() prix_total from archive_commande_XML;

-- Supprimer toutes les commandes des clients de nom 'Dupont'.	
delete archive_commande_xml where
extract(comm,'/commande/client/@nom').getStringVal() = 'Dupont';

-- Supprimer toutes les commandes dont le prix total est inférieur à 500
delete archive_commande_xml where
extract(comm,'/commande/@prix_total').getNumberVal() < 500;

-- Changer le libellé du produit 2 en 'table de camping' dans toutes les commandes concernées par ce produit
update archive_commande_xml set comm = updateXML(comm,'/commande/ligne_commande/produit[@id_produit = 2]/@libelle','Table de camping');

-- Supprimer la 3ème ligne de commande de la commande 4
update archive_commande_xml set comm = deleteXML(comm,'/commande/ligne_commande[position() = 3]')
where extract(comm, '/commande/@id_commande').getNumberVal() = 4;

-- Supprimer de la commande de la commande 4, la ligne de commande dont le numéro est 3
update archive_commande_xml set comm = deleteXML(comm,'/commande/ligne_commande[@num = 3]')
where extract(comm, '/commande/@id_commande').getNumberVal() = 4;

-- Rajouter une ligne de commande dans la commande 4 sans mettre à jour le montant total de la commande
update archive_commande_xml set comm = insertChildXML(comm, '/commande', 'ligne_commande', xmltype('<ligne_commande num="5">
<produit id_produit="8" libelle="Rideau"
prix_unitaire="7" quantite="5" prix_total="35"/>
</ligne_commande>'))
where extract(comm,'/commande/@id_commande').getNumberVal() = 4;

-- Rajouter une ligne de commande dans la commande 4 en mettant à jour le montant total de la commande
update archive_commande_xml set comm = updateXML(insertChildXML(comm, '/commande', 'ligne_commande', xmltype('<ligne_commande num="5">
<produit id_produit="8" libelle="Rideau"
prix_unitaire="7" quantite="5" prix_total="35"/>
</ligne_commande>')),'/commmande/@prix_total',to_char(extract(comm,'/commande/@prix_total').getNumberVal() + 35))
where extract(comm,'/commande/@id_commande').getNumberVal() = 4;

-- Création et enregistrement du schéma xsd d'une commande dans la base de données
BEGIN
-- Register the schema
DBMS_XMLSCHEMA.registerSchema('http://www.eisti.fr/schemas/commande.xsd',
'<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="commande" type="tCommande"/>
  <xsd:complexType name="tCommande">
    <xsd:sequence>
	<xsd:element name="commercial" type="tCommercial" minOccurs="1" maxOccurs="1" />
      <xsd:element name="client" type="tClient" minOccurs="1" maxOccurs="1" />
	  <xsd:element name="ligne_commande" type="tLigneCommande" maxOccurs="unbounded"/>
    </xsd:sequence>
	<xsd:attribute name="prix_total" type="xsd:float" use="required" />
	<xsd:attribute name="id_commande" type="xsd:int" use="required" />
	<xsd:attribute name="date_achat" type="xsd:string" use="required" />
  </xsd:complexType>
  <xsd:complexType name="tLigneCommande">
    <xsd:sequence>
      <xsd:element name="produit" type="tProduit" minOccurs="1" maxOccurs="1" />
     </xsd:sequence>
	<xsd:attribute name="num" type="xsd:int" use="required" />
  </xsd:complexType>
  <xsd:complexType name="tProduit">
    <xsd:attribute name="id_produit" type="xsd:int" use="required" />
	<xsd:attribute name="libelle" type="xsd:string" use="required" />
	<xsd:attribute name="prix_unitaire" type="xsd:float" use="required" />
	<xsd:attribute name="quantite" type="xsd:int" use="required" />
	<xsd:attribute name="prix_total" type="xsd:float" use="required" />
  </xsd:complexType>
  <xsd:complexType name="tClient">
    <xsd:attribute name="id_client" type="xsd:int" use="required" />
	<xsd:attribute name="nom" type="xsd:string" use="required" />
  </xsd:complexType>
  <xsd:complexType name="tCommercial">
    <xsd:attribute name="id_commercial" type="xsd:int" use="required" />
	<xsd:attribute name="nom" type="xsd:string" use="required" />
  </xsd:complexType>
</xsd:schema>',true,true,false);
end;
/

-- On crée une table dont la colonne XMLType est associée au schéma
CREATE TABLE archive_commande_xml_xsd ( comm XMLType) 
 XMLType COLUMN comm
 XMLSCHEMA "http://www.eisti.fr/schemas/commande.xsd"
 ELEMENT "commande";
 
drop table transformer;
-- on crée une table pour stocker des feuilles xsl
 create table transformer(xsl XMLType);
 
 -- On insère la feuille xsl pour calculer le montant total d'une commande
insert into transformer values(XMLType(
 '<?xml version="1.0"?> 
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
      <xsl:output encoding="utf-8"/>
    <xsl:template match="commande">  
        <xsl:value-of select="sum(ligne_commande/produit/@prix_total)"/>
	</xsl:template>
    </xsl:stylesheet>'));
	
-- On calcule le montant total de chaque commande
select xmltransform(comm,xsl).getNumberVal() from archive_commande_xml, transformer
