Base de donnee Oracle et XML

Comment faire pour lire un fichier XML a partir d'une procedure stockee dans Oracle 9?

Comment creer un fichier XML?

..C'est pas trop complique, en fait, une fois qu'on a ecume internet.


1 - Un peu de preparation
(on considere que l'on range nos fichiers a lire et a ecrire dans le repertoire /home/user/xml)

- bien evidemment, il faut donner les droits de lecture et ecriture pour ce repertoire a l'utilisateur qui va lancer les procedures. Si on file les droits a tout le monde:
    sudo chmod 777 /home/user/xml

- il faut declarer a Oracle que l'on va utiliser un repertoire physique sur le disque du serveur. Pour se faire, on exécute une commande pl/sql:
    CREATE OR REPLACE DIRECTORY XMLREP AS '/home/user/xml';

- le package XML DB est bien installe dans Oracle?
lancer la requete:
    SELECT * FROM ALL_USERS;
et verifier que l'utilisateur XDB est bien dans la liste


2 - Lecture d'un fichier XML physique
exemple de script qui fait un peu de tout:

declare
  sch         varchar2(200);
  xt            xmltype;
  ordre_no varchar2(35);
  tst_sch    number;
begin
    -- un schema de base, il en faut au moins un pour la lecture des elements

    sch := 'xmlns:xs="http://www.w3.org/2001/XMLSchema"';

    -- enregistre dans Oracle le schema xsd utilise pour verifier les fichiers xml, si on en a un sous la main

    -- on donne le nom banal 'fichierxsd' a notre schema

    -- le schema est base sur le fichier 'monFichier.xsd' present dans le repertoire accessible par Oracle lie a XMLREP

    -- la, il y a une conversion de UTF-8 vers ascii car le ficdhier xsd est en UTF-8 et la base de donnees est en ascii

    DBMS_XMLSCHEMA.registerSchema( 'fichierxsd', convert( DBMS_XSLPROCESSOR.read2clob( 'XMLREP', 'monFichier.xsd'), 'US7ASCII', 'AL32UTF8' ), True, False, False, False );

    -- on lit le fichier physique 'monFichier.xml' qui doit etre present dans le repertoire lie a XMLREP.

    -- les donnees du fichier sont rangees dans un CLOB

    -- on convertit les donnees de UTF-8 (le format du fichier) vers ascii us (format des donnees de ma base de donnees) -> pas necessaire si la base de donnee utilise UTF-8 (..ce qui est mieux!)

    -- on cree un xmltype avec ces donnees
    xt := xmltype( convert( DBMS_XSLPROCESSOR.read2clob('XMLREP','monFichier.xml'), 'US7ASCII', 'AL32UTF8' ) );

    -- si le fichier n'existe pas, il y a une exception!

    -- si on veut tester les donnees XML par rapport a un XSD avec un element racine 'Noyau':
    tst_sch := xt.isSchemaValid( 'fichierxsd', 'Noyau' );
    IF tst_sch <> 1 THEN
        dbms_output.put_line( 'fichier xml ne correspond pas au schema voulu!' );
        return;
    END IF;

    -- test si il y a l'element voulu dans le fichier XML:
    IF xt.existsNode( '/Noyau/Ordre[1]' ) = 0 THEN
        dbms_output.put_line( 'xml ne contient pas ordre!' );
        return;
    END IF;
    

    -- recupere l'attribut No
    ordre_no := xt.extract( '/Noyau/Ordre[1]/@No', sch ).getStringVal();
end;

3 - Ecriture d'un fichier xml a partir de donnees de tables

declare

    xmlval  clob;

    tstXsd  number;

    xt      xmltype;

begin

    -- on cree une requete a base de xmlelement, xmlattributes et xmlagg

    -- xmlagg va ranger a la suite toutes les lignes renvoyees par la requete sql

    -- j'utilise des case when pour eviter d'afficher un element si aucun attribut de l'element n'est initialise

    SELECT XMLELEMENT("Noyau" 

                ,xmlattributes('02' as "XMLVersion" )

                ,XMLELEMENT("FileDate", xmlattributes( to_char( sysdate, 'YYYY') as "Y", to_char( sysdate, 'MM') as "M", to_char( sysdate, 'DD') as "D", to_char( sysdate, 'HH24:MI') as "T" ) )

                ,XMLELEMENT("ListDetails", xmlattributes( 'France' as "OriginID", 'Tahiti' as "DestinationID", ) )

                ,XMLAGG( 

                    XMLELEMENT( "Ordre", xmlattributes( osta.no as "No")

                        ,case when osta.state is not null then

                         XMLELEMENT( "Event", xmlattributes( osta.state as "StateCd" )

                            ,XMLELEMENT( "Date", xmlattributes( to_char( osta.evtlcldt, 'YYYY') as "Y", to_char( osta.evtlcldt, 'MM') as "M", to_char( osta.evtlcldt, 'DD') as "D", to_char( osta.evtlcldt, 'HH24:MI') as "T" ) )

                         ) end

                    )

            )).extract('/*').getclobVal()

    INTO xmlval

    FROM ORDER_STATUS osta;

    -- Ecriture du fichier physique

    dbms_xslprocessor.clob2file( xmlval, 'XMLREP', 'monFichier.xml', nls_charset_id('AL32UTF8') );

    -- verifier que le fichier a bien ete cree 

    -- et qu'il est valide par rapport au xsd

    xt := xmltype( convert( DBMS_XSLPROCESSOR.read2clob( 'XMLREP',  'monFichier.xml' ), 'US7ASCII', 'AL32UTF8' ) );

    tstXsd := xt.isSchemaValid( 'fichierxsd', 'Noyau' );

    IF tstXsd <> 1 THEN

        dbms_output.put_line( 'Erreur: fichier xml non valide!' );

        return;

    END IF;

end;

et voila!!