Select dans un XML stocké dans un CLOB

Version imprimable

Bonjour,
J'ai une table qui ressemble à ceci :

Code:

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:

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:

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

Comme je viens de passer plusieurs jours à m'échiner sur un problème similaire, je devrais pouvoir t'aider :
Code:

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:

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

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:

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

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:

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