Oracle:Extraire valeur d'une balise XML

Version imprimable

Bonjour,

J'ai un fichier XML EDI, je veux extraire des valeurs des balises avec un script oracle , comme suivant:
je vous donne une partie du fichier, et un exemple de la valeur que je veux le récupéré

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<?xml version="1.0" encoding="ISO-8859-15"?>
 <?xml-stylesheet type="text/xsl" href="https://one.edf.fr/sites/default/files/documents/ebxml_edf_visualiseur_v03.05.xsl"?>
 <Invoice xmlns="urn:oasis:names:specification:ubl:schemasd:Invoice-2" xmlns:cac="urn:oasis:names:specification:ubl:schemasd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schemasd:CommonBasicComponents-2" xmlns:ext="urn:oasis:names:specification:ubl:schemasd:CommonExtensionComponents-2" xmlnssi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:oasis:names:specification:ubl:schemasd:Invoice-2 UBL-Invoice-2.1.xsd">
<cbc:UBLVersionID>2.1</cbc:UBLVersionID>
<cbc:CustomizationID schemeName="Stylesheet" schemeAgencyName="EDF" schemeID="EDF.03.05">EDF-FLX-V19.90</cbc:CustomizationID>
<cbcrofileID>bpid:urn:oasis:names:draft:bpss:ubl-2-sbs-invoice-notification-draft</cbcrofileID>
<cbc:ID>10096545085</cbc:ID>
<cbc:CopyIndicator>true</cbc:CopyIndicator>
<cbc:UUID>0d4a62e0-62d9-489a-8d08-f62ae1498135</cbc:UUID>
<cbc:IssueDate>2019-07-03</cbc:IssueDate>
<cbc:InvoiceTypeCode name="Facture" listName="FFA616-MDL_MONOH_19-0" listAgencyName="ELEC" listID="FEF">380</cbc:InvoiceTypeCode>
<cbc:Note>Origine 2017 de l'électricité vendue par EDF : 85,9% nucléaire, 7,2 % renouvelables (dont 5,3% hydraulique), 1,9% charbon, 3,7% gaz, 1,3% fioul. Indicateurs d'impact environnemental sur www.edf.fr</cbc:Note>
<cbc:Note>Document à conserver 10 ans</cbc:Note>
<cbc:Note>Retrouvez l'ensemble de nos offres, des tarifs et autres informations sur la gestion de votre énergie sur www.edf.fr/collectivites</cbc:Note>
<cbc:Note>Délai de préavis de résiliation du contrat : aucun pour le Tarif Bleu.</cbc:Note>
<cbc:TaxPointDate>2019-07-03</cbc:TaxPointDate>
<cbcocumentCurrencyCode>EUR</cbcocumentCurrencyCode>
<cbc:TaxCurrencyCode>EUR</cbc:TaxCurrencyCode>
<cbcaymentCurrencyCode>EUR</cbcaymentCurrencyCode>
<cbc:LineCountNumeric>18</cbc:LineCountNumeric>
<cac:InvoicePeriod>
<cbc:StartDate>2019-09-03</cbc:StartDate>
<cbcescription>Date prochaine facture</cbcescription>
</cac:InvoicePeriod>
<cac:BillingReference>
<cac:AdditionalDocumentReference>
<cbc:ID>1-DZ8-1171</cbc:ID>
<cbc:UUID>225635703350689</cbc:UUID>
<cbc:IssueDate>1977-10-28</cbc:IssueDate>
<cbcocumentTypeCode>SI_2373</cbcocumentTypeCode>
<cbcocumentType>Tarif Bleu pour clients non résidentiels Option Base 9kVA - Prix réglementés</cbcocumentType>
</cac:AdditionalDocumentReference>
</cac:BillingReference>
 </Invoice>

------------------------------------------------------------------------------------------------------------------
les lignes que je veux récupérer avec un select (Oracle) de ce fichier:

Code:

1
2
3
 <cbc:ID>1-DZ8-1171</cbc:ID>
 <cbc:UUID>225635703350689</cbc:UUID>
 <cbc:IssueDate>1977-10-28</cbc:IssueDate>

pour avoir les trois valeurs
1-DZ8-1171
225635703350689
1977-10-28

Je vous remercie .

Ceci dépend bien comment les données du xml sont chargées. Prenons un cas où les données sont stokées dans une table du type xmltype (binaire). Pour faire l'idée plus claire, cette table, dit gtt_, est crée symboliquement comme ça.
Code:

1 2 create table gtt_ of XMLType XMLType STORE AS BINARY XML;
On peut faire une vue façon table relationnelle par un sql avec la fonction xmltable().
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 select x.* from gtt_ t , xmltable( xmlnamespaces( 'urn:oasis:names:specification:ubl:schemasd:Invoice-2' as "ns0" , 'urn:oasis:names:specification:ubl:schemasd:CommonAggregateComponents-2' as "ns1" , 'urn:oasis:names:specification:ubl:schemasd:CommonBasicComponents-2' as "ns2" ) , '/ns0:Invoice/ns1:BillingReference/ns1:AdditionalDocumentReference' passing t.OBJECT_VALUE columns ID varchar2(40) path 'ns2:ID' , UUID varchar2(40) path 'ns2:UUID' , IssueDate varchar2(40) path 'ns2:IssueDate' ) x ;
J'ai délibérément choisi les préfixes des namespaces bien différents de ceux qui apparaîtrent dans le xml de source pour illustrer la nature arbitraire de préfixe.

Comme ça, on voit sortir une table relationnelle. Comment l'utiliser est encore dépend. On peut faire une procédure stockée et puis interfacer la sortie par une application traditionnelle pour une base relationnelle de données. Ce serait plus éfficace comme la besogne de traîter xml est relégué au xml db (Oracle) bien dédiée à cela au lieu de le faire par l'application elle-même. Voilà !

Oracle:Extraire valeur d'une balise XML

Bonjour,
Merci pour la réponse.

Pour répondre à votre question "Ceci dépend bien comment les données du xml sont chargées",

je veux utiliser ce genre de requête:
Code:

1 2 3 4 5 6 Select Extractvalue(l.column_value,'/Invoice/ID') REF_EXTERNE From (Select XmlType(XMLSerialize(document xmltype(bfilename('DIRECTORY_ORACLE','MODELE_EDI.xml'), nls_charset_id('WE8ISO8859P15')))) col_xml From dual ) d, TABLE(XMLSequence(extract(d.col_xml,'/'))) l; TABLE(XMLSequence(extract(value(l), '/Invoice'))) e;
Je veux extraire les données avec la fonction EXTRACVALUE.

J'ai essaye votre solution, mais aucune ligne n'a été récupérée, en replaçant la table gtt_ par
Code:

1 2 3 (Select XmlType(XMLSerialize(document xmltype(bfilename('DIRECTORY_ORACLE','MODELE_EDI.xml'), nls_charset_id('WE8ISO8859P15')))) col_xml From dual ) d,

06/11/2019, 15h55
tsuji

Quelle version de bdd ? Je parle de 11g+. ExtractValue() et tout ça ne sont plus d'usage encouragé. En tout cas, des xpath là sont tellement faux qu'il vaut mieux que vous étudiez les documentations de version correspondante.
06/11/2019, 17h33
Habib91

Je vais analyser pourquoi avec la solution proposée je récupère aucune ligne.
la version Oracle est 11G.
Fichier XML en pièce jointe.Pièce jointe 516140
06/11/2019, 18h17
tsuji
Vous pouvez créer une table gtt_ (ou quelque nom comme vous voulez) comme ça.
Code:

1 2 3 4 -- drop table gtt_; create table gtt_ of XMLType XMLType STORE AS BINARY XML; /
(Et dropper gtt_ après la teste, si vous voulez.)

Et puis la charger comme ça.
Code:

1 2 3 insert into gtt_(object_value) values ( XMLTYPE(bfilename('DIRECTORY_ORACLE','MODELE_EDI.xml'), nls_charset_id('WE8ISO8859P15')) );
Puis, c'est prêt.

J'ai pu trouvé pourquoi je ne récupère pas de donnée, il y avait uniquement une erreur dans

Code:

urn:oasis:names:specification:ubl:schemasd:Invoice-2

au lieu de

Code:

urn:oasis:names:specification:ubl:schema:xsd:Invoice-2

Merci pour votre solution.

Je voulais aussi récupérer en même temps d'autres valeur qui sont dans la balise "cbc", comme suivant:
Code:

1 2 3 4 5 6 7 8 9 ---<Invoice xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2 UBL-Invoice-2.1.xsd"> <cbc:UBLVersionID>2.1</cbc:UBLVersionID> <cbc:CustomizationID schemeName="Stylesheet" schemeAgencyName="EDF" schemeID="EDF.03.05">EDF-FLX-V19.90</cbc:CustomizationID> <cbc:ProfileID>bpid:urn:oasis:names:draft:bpss:ubl-2-sbs-invoice-notification-draft</cbc:ProfileID> <cbc:ID>10096545085</cbc:ID> <cbc:CopyIndicator>true</cbc:CopyIndicator> <cbc:UUID>0d4a62e0-62d9-489a-8d08-f62ae1498135</cbc:UUID> <cbc:IssueDate>2019-07-03</cbc:IssueDate> --
les valeurs à récupérer sont: cbc:ID et cbc:IssueDate.

Qu'est-ce que je dois ajouter dans votre requête initiale afin de récupérer ces champs supplémentaires ?
Merci.

Oui, c'est ça : j'avais corrigé pas mal de textes qui étaient affectés par le rendrement d'emoji de ce forum. (Il vaut mieux quand vous postez séléctionner refuse dans un des choix concernant emoji.)

Pour les nouvelles données à retirer, on est sensé de noter que les xpaths apparus dans la partie COLUMNS n'acceptent pas le remontage du niveau (parent::*, ...) et par conséquent, on part au niveau plus haut dans l'arbre des noeuds. A titre d'exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 select x.* from gtt_ t , xmltable( xmlnamespaces( 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' as "ns0" , 'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as "ns1" , 'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as "ns2" ) , '/ns0:Invoice' passing t.OBJECT_VALUE columns ID varchar2(40) path 'ns1:BillingReference/ns1:AdditionalDocumentReference/ns2:ID' , UUID varchar2(40) path 'ns1:BillingReference/ns1:AdditionalDocumentReference/ns2:UUID' , IssueDate varchar2(40) path 'ns1:BillingReference/ns1:AdditionalDocumentReference/ns2:IssueDate' , ID_PARENT varchar2(40) path 'ns2:ID' , IssueDate_Parent varchar2(40) path 'ns2:IssueDate' ) x ;
Voilà !

09/11/2019, 00h42
Habib91

Merci Beaucoup tsuji pour votre aide qui m'a permis d'avoir la bonne requête pour l'extraction des données d'un fichier XML.

Cordialement.
02/12/2019, 17h41
Habib91
Bonjour tsuji

Je reviens vers vous, pour un complément pour le même fichier XML.

J'ai une balise dans le fichier XML avec trois valeurs, j'arrive à recupérer la dernière, mais je ne sais comment gérer ce cas.
Voici ce que j'ai dans le fichier XML:
Code:

1 2 <cbc:IssueDate>20190703</cbc:IssueDate> <cbc:InvoiceTypeCode name="Facture" listName="MONOH_19-0" listAgencyName="ELEC" listID="FEF">380</cbc:InvoiceTypeCode>
Je veux récupérer la valeur "Facture".
avec ce script (la partie path) je récupère la dernière valeur "380"

Citation:

type_code Varchar2(40) Path 'cbc:InvoiceTypeCode',

Merci pour le retour.
03/12/2019, 15h29
tsuji

Citation:

type_code Varchar2(40) Path 'cbc:InvoiceTypeCode',

J'éssayerais guidé par le principe de xpath :

Code:

type_code_name Varchar2(40) Path 'cbc:InvoiceTypeCode/@name',
04/12/2019, 14h55
Habib91

Merci beaucoup pour votre aide.
C'est résolu.
05/12/2019, 16h44
Habib91

Est-il possible de gérer le path dans une variable,
exemple

Code:

type_code_name Varchar2(40) Path 'cbc:InvoiceTypeCode',

Je veux que la partie qui est après le "path" soit une variable

Code:

v_type:='cbc:InvoiceTypeCode'

résultat: type_code_name Varchar2(40) Path v_type.

Est-c'est possible ?
09/12/2019, 17h39
tsuji

Une fashion de faire est

Code:

var v_type varchar2(40) := 'cbc:InvoiceTypeCode'

édition : (ligne éditée sur le typage)
et puis

Code:

type_code_name Varchar2(40) Path '&v_type',

Mais ce n'est pas partout écrit comme ça : détails de faire appeler dans un environnement de programmation ou de cli dépendent, et la fashion exacte de coder change beaucoup et devient facilement irreconnaissable.