Comment transformer un fichier XML en SQL ?

Comment transformer un fichier XML en SQL ?

Telle est la problématique que je viens de rencontrer.

Je dispose d'un fichier XML de 160 Mo contenant quasiment 5 millions de lignes.

J'ai essayé d'importer le fichier avec Excel en mettant comme source de données mon fichier XML, mais celui-ci après 4 heures d'import a affiché un message d'erreur.
Un collègue m'a alors suggéré de regarder du coté de XSLT.

XSLT est un langage qui permet de transformer un (ou plusieurs) document(s) XML en un autre document XML, HTML ou texte.

La plupart du temps, il est utilisé pour séparer les données (XML) du code/présentation (XSLT) pour un résultat final affichable (HTML).
Une autre utilisation, moins répandue, est la conversion d'un schéma XML en un autre, afin de permettre l'interopérabilité entre des systèmes logiciels différents et qui communiquent par flux XML.

Dans notre cas nous voulons transformer du XML en SQL.

Si vous voulez en savoir plus sur le langage XSL, je vous invite à consulter le site suivant:
http://www.laltruiste.com/document.php?url=http://www.laltruiste.com/coursxsl/intro.html

Voici un exemple de fichier XML nommé ici certif.xml:

<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet href="certif.xsl" type="text/xsl"?>
<!DOCTYPE diplomes SYSTEM "http://www.foobar.com/dtd.dtd">
<diplomes>
    <parametres>
        <type-export>complet</type-export>
        <horodatage>31/08/2010 22:01:07</horodatage>
    </parametres>
    <fiche-diplome>
        <type-intervention>C</type-intervention>
        <code-diplome>1874</code-diplome>
        <code-type-diplome>105</code-type-diplome>
        <intitule-type-diplome>Mastère spécialisé</intitule-type-diplome>
        <code-niveau>1</code-niveau>
        <code-niveau-europeen />
        <code-scolarite />
        <code-sise>9730703</code-sise>
        <code-onisep />
        <code-onisep-ideo />
        <code-specificite-ideo />
        <code-cqp />
        <code-ministere-emploi />
        <code-afpa />
        <code-nsf>116</code-nsf>
        <code-lettre-nsf />
        <code-carif-07 />
        <code-carif-26 />
        <inscrit-rncp>2</inscrit-rncp>
        <type-sigle>Mastère spécialisé</type-sigle>
        <type-complet>Mastère spécialisé</type-complet>
        <intitule-diplome>Mastère spécialisé mastère spécialisé chimie 'ts' ttt</intitule-diplome>
        <dominante>chimie</dominante>
        <mention />
        <specialite />
        <intitule-sigle />
        <code-niveau-entree />
        <accessibilite-fi />
        <accessibilite-ca>2</accessibilite-ca>
        <accessibilite-fc>2</accessibilite-fc>
        <accessibilite-cp>2</accessibilite-cp>
        <accessibilite-vae>1</accessibilite-vae>
        <accessibilite-ind>2</accessibilite-ind>
        <accessibilite-uc />
        <texte-chapo />
        <texte-objectif />
        <texte-programme />
        <texte-admission />
        <texte-poursuite />
        <texte-debouches />
        <texte-source />
        <valideur />
        <annee-premiere-session>2002</annee-premiere-session>
        <annee-derniere-session />
        <etat>3</etat>
        <date-creation>11/02/2004</date-creation>
        <date-maj>11/02/2004</date-maj>
        <validation-interne />
        <validation-alfa />
        <modalites-diplomes>
            <modalite>
                <code-type-modalite>2</code-type-modalite>
                <code-modalite>5</code-modalite>
                <libelle>Label de la Conférence des grandes écoles (CGE)</libelle>
            </modalite>
        </modalites-diplomes>
        <jos>
        </jos>
        <modules>
        </modules>
        <liens-diplomes-formacodes>
        </liens-diplomes-formacodes>
        <liens-diplomes-gfe>
        </liens-diplomes-gfe>
        <liens-diplomes-romes>
        </liens-diplomes-romes>
        <liens-diplomes-fap>
        </liens-diplomes-fap>
        <liens-diplomes-validations>
            <lien-diplomes-validations>
                <code-validation>1015</code-validation>
                <nom-validation>Diplôme de la recherche</nom-validation>
                <certificateur>1</certificateur>
                <valideur />
                <code-habilitation />
                <code-rncp />
                <duree-habilitation />
                <date-ouverture>00/00/0000</date-ouverture>
                <date-fermeture>00/00/0000</date-fermeture>
                <validite-actuelle />
                <date-maj>01/01/2007</date-maj>
            </lien-diplomes-validations>
        </liens-diplomes-validations>
        <historiques-diplomes>
        </historiques-diplomes>
    </fiche-diplome>
</diplomes>

Voici le code XSL permettant de transformer les données provenant de notre fichier XML.

Dans notre cas, nous voulons récupérer les colonnes suivantes code, code_type, nom, nom_certificateur, code_validation, type_certification. et les mettre dans une table diplome.

Nous voulons aussi nettoyer les données des apostrophes.

Voici le fichier : certif.xsl

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 
	<xsl:param name="apostrophe">&apos;</xsl:param>
	<xsl:template name="cleanQuote">
		<xsl:param name="string" />
		<xsl:if test="contains($string, $apostrophe)"><xsl:value-of select="substring-before($string, $apostrophe)" />&apos;&apos;<xsl:call-template name="cleanQuote"><xsl:with-param name="string"><xsl:value-of select="substring-after($string, $apostrophe)" /></xsl:with-param></xsl:call-template></xsl:if>
		<xsl:if test="not(contains($string, $apostrophe))"><xsl:value-of select="$string" /></xsl:if>
	</xsl:template>
 
    <xsl:template match="diplomes/parametres"/>
    <xsl:template match="diplomes/fiche-diplome">
            <xsl:text> INSERT INTO diplome (code, code_type, nom, nom_certificateur, code_validation, type_certification) VALUES ( </xsl:text>
            <xsl:value-of select="code-diplome"/>
            <xsl:text>, </xsl:text>
            <xsl:value-of select="code-type-diplome"/>
            <xsl:text>, &apos;</xsl:text>
			<xsl:call-template name="cleanQuote"><xsl:with-param name="string"><xsl:value-of select="intitule-diplome"/></xsl:with-param></xsl:call-template>
            <xsl:text>&apos;, &apos;</xsl:text>
			<xsl:call-template name="cleanQuote"><xsl:with-param name="string"><xsl:value-of select="liens-diplomes-validations/lien-diplomes-validations/nom-validation"/></xsl:with-param></xsl:call-template>
            <xsl:text>&apos;, </xsl:text>
            <xsl:value-of select="liens-diplomes-validations/lien-diplomes-validations/code-validation"/>
            <xsl:text>, &apos;</xsl:text>
			<xsl:call-template name="cleanQuote"><xsl:with-param name="string"><xsl:value-of select="intitule-type-diplome"/></xsl:with-param></xsl:call-template>
            <xsl:text>&apos; </xsl:text>
            <xsl:text>);</xsl:text><br/>        
    </xsl:template>
</xsl:stylesheet>

Lorsque notre fichier XSL est créé, il nous faut rajouter
cette ligne dans le fichier certif.xml

<?xml-stylesheet href="certif.xsl" type="text/xsl"?>

On appelle alors notre fichier certif.xml dans un navigateur comme Internet Explorer ou Firefox.

Voici le code obtenu.

INSERT INTO diplome (code, code_type, nom, nom_certificateur, code_validation, type_certification) VALUES ( 1874, 105, 'Mastère spécialisé mastère spécialisé chimie ''ts'' ttt', 'Diplôme de la recherche', 1015, 'Mastère spécialisé' );

Il ne nous reste plus qu'à créer une table diplome dans notre base de données et à exécuter le code obtenu pour mettre à jour notre table.

CREATE TABLE diplome
(
  code character varying(255),
  code_type character varying(255),
  nom character varying(255),
  nom_certificateur character varying(255),
  code_validation character varying(255),
  type_certification character varying(255)
);

Si vous connaissez d'autres techniques n'hésitez pas à poster un commentaire.

33 commentaires

  1. Generally I do not study post with weblogs, even so wish to point out that this kind of write-up incredibly urged everyone to take a look at and also undertake it! Your current way of writing continues to be stunned my family. Thank you, very wonderful document.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Captcha *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.