IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Select dans un XML stocké dans un CLOB [11g]


Sujet :

SQL Oracle

  1. #1
    Membre à l'essai
    Homme Profil pro
    Chef de projet SI
    Inscrit en
    Juin 2019
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Saône et Loire (Bourgogne)

    Informations professionnelles :
    Activité : Chef de projet SI

    Informations forums :
    Inscription : Juin 2019
    Messages : 9
    Points : 10
    Points
    10
    Par défaut Select dans un XML stocké dans un CLOB
    Bonjour,
    J'ai une table qui ressemble à ceci :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    create table MATABLE
    (
      ID NUMBER(20, 0) NOT NULL 
    , PARAMETRAGE CLOB 
    )
    le champ MATABLE.PARAMETRAGE est donc un CLOB qui contient un XML de ce type :
    Code XML : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    <Table Type="S">
        <Fichier Separateur=";" RetourChariot="CrLf" NomFichier="ETABLISSEMENT.txt">
          <Colonnes>
            <Colonne Nom="ID" Type="Nombre" Taille="6" />
            <Colonne Nom="Nom" Type="Texte" Taille="30" />
            <Colonne Nom="Ville" Type="Texte" Taille="30" />
          </Colonnes>
        </Fichier>
        <Fichier Separateur=";" RetourChariot="CrLf" NomFichier="EMPLOYES.txt">
          <Colonnes>
            <Colonne Nom="ID" Type="Nombre" Taille="6" />
            <Colonne Nom="Nom" Type="Texte" Taille="30" />
            <Colonne Nom="Prénom" Type="Texte" Taille="30" />
            <Colonne Nom="ID_ETAB" Type="Nombre" Taille="6" />
          </Colonnes>
        </Fichier>
    </Table>

    Je voudrais obtenir la listes des colonnes par fichier, et donc avoir un résultat comme ceci :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    NomFichier               Colonne
    =========================================
    ETABLISSEMENT.txt        ID
    ETABLISSEMENT.txt        Nom
    ETABLISSEMENT.txt        Ville
    EMPLOYES.txt             ID
    EMPLOYES.txt             Nom
    EMPLOYES.txt             Prénom
    EMPLOYES.txt             ID_ETAB
    Est-ce possible ?

    Et si oui, avez-vous connaissance d'un tuto sur le sujet ? J'ai cherché sur mon ami Google, mais je n'ai pas dû utiliser les bons termes...

    Merci d'avance,
    Flophie

  2. #2
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Retraité
    Inscrit en
    Mai 2002
    Messages
    9 080
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Retraité
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Mai 2002
    Messages : 9 080
    Points : 30 778
    Points
    30 778
    Par défaut
    Comme je viens de passer plusieurs jours à m'échiner sur un problème similaire, je devrais pouvoir t'aider :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    select  FCH.NOM     as NOM_FICHIER
        ,   CLN.NOM     as NOM_COLONNE
    from    MATABLE  SRC
        ,   xmltable
                (   '/Table'        passing SRC.PARAMETRAGE
                columns
                    NOM                     path    'Fichier/@NomFichier'
                ,   COLONNES        xmltype path    'Fichier/Colonnes'
                )   FCH 
        ,   xmltable    
                (   '/Colonnes'     passing     FCH.COLONNES
                columns 
                    NOM                     path    'Colonne/@Nom'
                ,   TYP                     path    'Colonne/@Type'
                ,   TAILLE                  path    'Colonne/@Taille'
                )   CLN
    ;
    A vérifier, je ne l'ai pas testé...

    Je n'ai pas trouvé de tuto autre que la documentation officielle, qui n'est pas toujours très claire ni bien organisée

    PS : Dans les exemples que j'ai trouvés, la colonne qui reçoit le document XML est de type XMLTYPE en ajoutant aux options de stockage
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    xmltype column MA_COLONNE_XML
     store as securefile binary xml
     (tablespace MON_TABLESPACE_LOB)
    La dernière ligne n'étant utile que si tu as prévu un tablespace spécifique pour le stockage des LOB
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  3. #3
    Membre averti
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2014
    Messages
    257
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 30
    Localisation : France, Ille et Vilaine (Bretagne)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Août 2014
    Messages : 257
    Points : 395
    Points
    395
    Par défaut
    Bonjour bonjour

    J'ai trouvé un post avec un problème sur le XML qui me fait un peu penser à celui-ci, notamment sur le fait d'avoir plusieurs balises au même nom.

    Voici la requête qui est faite (Si, comme moi, vous avez envie de sortie le XML pour voir la tête qu'il a, bon courage et à demain...)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    WITH 
    my_table AS 
    (
    SELECT 1 id ,xmltype.createxml('<MessageEnvelope><PayloadList><PayloadGroup Primary="1"><Payload Type="WorkOrderChange" Sequence="1"><Message><WorkOrderChange><MessageHeader><MessageID>e495e616-3762-48ea-8ab5-7816b8b02b30</MessageID><MessageTimeStamp>2013-10-26T08:02:26.370-05:00</MessageTimeStamp><SenderID>SENDER</SenderID><ReceiverID>RECEIVER</ReceiverID><MessageType>WOChange</MessageType><CorrelationID/></MessageHeader><OrderID>12345678901</OrderID><RequestTimeStamp>2013-10-26T08:02:26.370-05:00</RequestTimeStamp><RequestedChange>RELEASE</RequestedChange><OrderPriority/><ShippingInformation><ShipCode/><ServiceLevel/><TransportationMode/><CarrierCode/><CarrierAccount/><FOBCode/><FreightTerms/><CommercialResidential/></ShippingInformation><ShipToFacilityID/><ShipToLocation><CompanyName/><ContactName/><ContactPhone/><ContactEmail/><Address><Line1/><Line2/><Line3/><City/><State/><CountryCode/><PostalCode/></Address></ShipToLocation><DeliveryDate nil="true"/><Instructions><ShippingInstruction/><PackingInstruction/></Instructions></WorkOrderChange></Message></Payload><Payload Type="CSMOrderAttachment" Sequence="2"><Message><CFIDATA><SERVICELIST><SERVICE TYPE="SYSTEM"><ORDER NUMBER="123456789" TIE="1" QUANTITY="2"><ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">ABC1234</VALUE><VALUE SID="2">CBA4321</VALUE></LIST></ASSETTAGDATA></ORDER></SERVICE></SERVICELIST></CFIDATA></Message></Payload></PayloadGroup></PayloadList></MessageEnvelope>') my_xml FROM dual 
    UNION ALL
    SELECT 2  ,xmltype.createxml('<MessageEnvelope><PayloadList><PayloadGroup Primary="1"><Payload Type="WorkOrderChange" Sequence="1"><Message><WorkOrderChange><MessageHeader><MessageID>e495e616-3762-48ea-8ab5-7816b8b02b30</MessageID><MessageTimeStamp>2013-10-26T08:02:26.370-05:00</MessageTimeStamp><SenderID>SENDER</SenderID><ReceiverID>RECEIVER</ReceiverID><MessageType>WOChange</MessageType><CorrelationID/></MessageHeader><OrderID>12345678901</OrderID><RequestTimeStamp>2013-10-26T08:02:26.370-05:00</RequestTimeStamp><RequestedChange>RELEASE</RequestedChange><OrderPriority/><ShippingInformation><ShipCode/><ServiceLevel/><TransportationMode/><CarrierCode/><CarrierAccount/><FOBCode/><FreightTerms/><CommercialResidential/></ShippingInformation><ShipToFacilityID/><ShipToLocation><CompanyName/><ContactName/><ContactPhone/><ContactEmail/><Address><Line1/><Line2/><Line3/><City/><State/><CountryCode/><PostalCode/></Address></ShipToLocation><DeliveryDate nil="true"/><Instructions><ShippingInstruction/><PackingInstruction/></Instructions></WorkOrderChange></Message></Payload><Payload Type="CSMOrderAttachment" Sequence="2"><Message><CFIDATA><SERVICELIST><SERVICE TYPE="SYSTEM"><ORDER NUMBER="123456789" TIE="1" QUANTITY="3"><ASSETTAGDATA><LIST NAME="AssetTag"><VALUE SID="1">12XYZ34567</VALUE><VALUE SID="2">12WXY34567</VALUE><VALUE SID="3">112VWX34567</VALUE></LIST></ASSETTAGDATA></ORDER></SERVICE></SERVICELIST></CFIDATA></Message></Payload></PayloadGroup></PayloadList></MessageEnvelope>')  FROM dual
    )
    SELECT * FROM my_table mt ,
    xmltable(
                     'for $i in /MessageEnvelope/PayloadList/PayloadGroup/Payload/Message/CFIDATA/SERVICELIST/SERVICE/ORDER/ASSETTAGDATA
                      return $i /LIST/VALUE'
                      passing mt.my_xml
                      --
                      COLUMNS col2  varchar2(100) path '/VALUE',
                              col1  varchar2(100)       path '/VALUE/@SID'
                     ) z
    Peut-être qu'une partie du code pourra être réutilisée dans ton cas

    Le petit lien qui va bien : https://www.club-oracle.com/threads/...ml-clob.10036/

    Sinon, la solution donnée précédemment semble bien, je voulais juste montrer une autre syntaxe potentielle

    Bisous bisous

  4. #4
    Membre à l'essai
    Homme Profil pro
    Chef de projet SI
    Inscrit en
    Juin 2019
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Saône et Loire (Bourgogne)

    Informations professionnelles :
    Activité : Chef de projet SI

    Informations forums :
    Inscription : Juin 2019
    Messages : 9
    Points : 10
    Points
    10
    Par défaut
    Merci pour vos réponses respectives

    La 1ère solution fonctionne bien avec quelques adaptations, que je mets ici pour ceux que ça intéresserait :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
     
    WITH 
    MATABLE AS 
    (
    SELECT 1 ID , xmltype.createxml('<Table Type="S"><Fichier Separateur=";" RetourChariot="CrLf" NomFichier="ETABLISSEMENT.txt"><Colonnes><Colonne Nom="ID" Type="Nombre" Taille="6" /><Colonne Nom="Nom" Type="Texte" Taille="30" /><Colonne Nom="Ville" Type="Texte" Taille="30" /></Colonnes></Fichier><Fichier Separateur=";" RetourChariot="CrLf" NomFichier="EMPLOYES.txt"><Colonnes><Colonne Nom="ID" Type="Nombre" Taille="6" /><Colonne Nom="Nom" Type="Texte" Taille="30" /><Colonne Nom="Prénom" Type="Texte" Taille="30" /><Colonne Nom="ID_ETAB" Type="Nombre" Taille="6" /></Colonnes></Fichier></Table>') PARAMETRAGE FROM dual 
    )
    select  FCH.NOM     as NOM_FICHIER
        ,   CLN.NOM     as NOM_COLONNE
    from    MATABLE  SRC
        ,   xmltable
                (   '/Table/Fichier'        passing SRC.PARAMETRAGE
                columns
                    NOM                     path    '@NomFichier'
                ,   COLONNES        xmltype path    'Colonnes'
                )   FCH 
        ,   xmltable    
                (   '/Colonnes/Colonne'     passing     FCH.COLONNES
                columns 
                    NOM                     path    '@Nom'
                ,   TYP                     path    '@Type'
                ,   TAILLE                  path    '@Taille'
                )   CLN
    ;
    Encore merci !

    flophie

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 1
    Dernier message: 11/11/2008, 12h17
  2. Faire un simple SELECT dans une Proc. Stock
    Par MaelstroeM dans le forum Oracle
    Réponses: 2
    Dernier message: 29/08/2007, 10h27
  3. Réponses: 2
    Dernier message: 07/08/2007, 20h48
  4. Réponses: 1
    Dernier message: 20/07/2006, 18h03
  5. Réponses: 8
    Dernier message: 28/04/2006, 00h02

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo