XPATH d'un fichier XML avec Oracle

Version imprimable

Bonjour,
je voudrai lister les valeurs des attributs d'un fichiers XML

Citation:

<root>
<s id="s1">
<z code="A01" libelle="ESPACE A01">

....

J'ai fais le code suivant pour aficher le id de la balise s, le code des balises z, et le debut

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
FOR r1 IN (SELECT EXTRACTVALUE (VALUE (p), 's/@id') AS ID
                FROM TABLE (XMLSEQUENCE (EXTRACT (x, '/root/s'))) p)
   LOOP
      DBMS_OUTPUT.put_line ('id_structure = ' || r1.ID);
 
      FOR r IN (SELECT EXTRACTVALUE (VALUE (p), 'z/@code') AS cod
                  FROM TABLE (XMLSEQUENCE (EXTRACT (x,
                                                       '/root/s[@id="'
                                                    || r1.ID
                                                    || '"]/z'
                                                   )
                                          )
                             ) p)
      LOOP
         DBMS_OUTPUT.put_line ('code zone = ' || r.cod);
 
         FOR r2 IN
            (SELECT EXTRACTVALUE (VALUE (p), 'p/@debut') AS debut
               FROM TABLE (XMLSEQUENCE (EXTRACT (x,
                                                    '/root/s/z[@code="'
                                                 || r.cod
                                                 || '"]/p'
                                                )
                                       )
                          ) p)
         LOOP
            DBMS_OUTPUT.put_line ('debut = ' || r2.debut);
         END LOOP;
      END LOOP;
   END LOOP;

est ce qu'il y a une optimisation à faire?

11/03/2014, 10h54
mnitu

Essayez de poster un exemple complet de fichier XML.
Pour extraire des valeurs d'un fichier XML vous n'avez pas besoin de PL/SQL.
11/03/2014, 11h05
DeveloppeurWeb

Je sais TB.
je veux le faire en PL SQL.

la structure es mise dans mon 1er commentaire.

Citation:

<root>
<s id="s1">
<z code="A01" libelle="ESPACE A01">



</z>
</s>
</root>

Pour PL/SQL "vous savez TB" ajoutez Begin, End, Into ...

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
35
36
37
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> With Data As (
  2    Select xmltype('<root>
  3     <s id="s1">
  4     <z code="A01" libelle="ESPACE A01">
  5     <p debut="10/03/2014 00:15" fin="10/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
  6     <p debut="11/03/2014 00:15" fin="11/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
  7     <p debut="12/03/2014 00:15" fin="12/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
  8     </z>
  9     </s>
 10     </root>') x_el from dual
 11  )
 12  Select a.S_Id, a.Z_Code, b.p_debut
 13    From data t,
 14         XmlTable ('/root'
 15                  passing t.x_el
 16                  COLUMNS S_Id       Varchar2(10) Path 's/@id',
 17                          Z_Code     Varchar2(10) Path 's/z/@code',
 18                          p_xel      XmlType      Path 's/z/p'
 19         ) a,
 20         XmlTable ('/p'
 21                   Passing a.p_xel
 22                   COLUMNS p_debut Varchar2(10) Path '@debut'
 23         ) b
 24  /
 
S_ID       Z_CODE     P_DEBUT
---------- ---------- ----------
s1         A01        10/03/2014
s1         A01        11/03/2014
s1         A01        12/03/2014
 
SQL>

11/03/2014, 16h07
DeveloppeurWeb

et si on a plusieurs z dans le même s et plusieurs s dans le root
11/03/2014, 16h26
mnitu

Avez vous un exemple ?
11/03/2014, 17h52
DeveloppeurWeb

Voici un exemple

Citation:

<root>
<s id="s1">
<z code="A01" libelle="ESPACE A01">



</z>
<z code="A02" libelle="ESPACE A02">



</z>
</s>
<s id="s2">
<z code="A01" libelle="ESPACE A01">



</z>
<z code="A02" libelle="ESPACE A02">



</z>
</s>
</root>

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> WITH DATA AS (
  2  SELECT xmltype('<root>
  3   <s id="s1">
  4   <z code="A01" libelle="ESPACE A01">
  5   <p debut="10/03/2014 00:15" fin="10/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
  6   <p debut="10/03/2014 00:15" fin="10/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
  7   <p debut="10/03/2014 00:15" fin="10/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
  8   </z>
  9   <z code="A02" libelle="ESPACE A02">
 10   <p debut="11/03/2014 00:15" fin="11/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
 11   <p debut="11/03/2014 00:15" fin="11/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
 12   <p debut="11/03/2014 00:15" fin="11/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
 13   </z>
 14   </s>
 15   <s id="s2">
 16   <z code="A01" libelle="ESPACE A01">
 17   <p debut="12/03/2014 00:15" fin="12/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
 18   <p debut="12/03/2014 00:15" fin="12/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
 19   <p debut="12/03/2014 00:15" fin="12/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
 20   </z>
 21   <z code="A02" libelle="ESPACE A02">
 22   <p debut="13/03/2014 00:15" fin="13/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
 23   <p debut="13/03/2014 00:15" fin="13/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
 24   <p debut="13/03/2014 00:15" fin="13/03/2014 00:15" in="0" out="0" flag="1" traite="0"/>
 25   </z>
 26   </s>
 27   </root>') x_el FROM dual
 28  )
 29  SELECT a.S_Id, b.Z_Code, c.p_debut
 30    FROM DATA t,
 31         XmlTable ('/root/s'
 32                   passing t.x_el
 33                   COLUMNS S_Id       Varchar2(10) Path '@id',
 34                           z_xel      XMLTYpe      Path 'z'
 35              ) a,
 36              XmlTable ('/z'
 37                        Passing a.Z_xel
 38                        COLUMNS Z_Code Varchar2(10) Path '@code',
 39                                p_xel  XmlType      Path 'p'
 40             ) b,
 41              XmlTable ('/p'
 42                        Passing b.p_xel
 43                        COLUMNS p_debut Varchar2(10) Path '@debut'
 44                       ) c
 45  /
 
S_ID       Z_CODE     P_DEBUT
---------- ---------- ----------
s1         A01        10/03/2014
s1         A01        10/03/2014
s1         A01        10/03/2014
s1         A02        11/03/2014
s1         A02        11/03/2014
s1         A02        11/03/2014
s2         A01        12/03/2014
s2         A01        12/03/2014
s2         A01        12/03/2014
s2         A02        13/03/2014
s2         A02        13/03/2014
s2         A02        13/03/2014
 
12 rows selected
 
SQL>

24/03/2014, 10h49
DeveloppeurWeb

si Je mets un fichier XML de 7 Mo de taille, ça met beaucoup de temps. (ça plantait TOAD).

Est ce qu'il y a une optimisation à faire?
24/03/2014, 15h22
danvoiculet
Avez-vous essayé de charger le fichier (avec SQL*Plus) à partir du disque comme ca
Code:

1 2 3 4 5 6 7 8 9 10 WITH DATA AS ( SELECT XMLTYPE(BFILENAME('Oracle_dossier', 'tmp.xml'), NLS_CHARSET_ID('UTF8')) x_el FROM dual
?

tmp.xml est le nom de la fichier
Oracle_dossier est le nom de Oracle dossier qui contient le fichier
25/03/2014, 09h27
DeveloppeurWeb

mon problème est lors de la lecture des enregistrements résultant de la requête

J'utilise un curseur pour ça.

mais par contre la requête met 1mn:10 pour s'exécuter
25/03/2014, 10h43
mnitu

Chargez d'abord le fichier de 7Mo dans une table temporaire et après utilisez la requête.

c'est ce que j'ai fais , je l'ai mis dans une colonne CLOB
j'ai mis le code suivant pour lire le fichier

curseur:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CURSOR cur_rec IS
WITH DATA AS (
    select xmltype(fichier_xml) as x_el from table where job_id=1  
   )
   SELECT a.S_Id, b.Z_Code, c.p_debut
     FROM DATA t,
          XmlTable ('/root/s'
                    passing t.x_el
                    COLUMNS S_Id       Varchar2(10) Path '@id',
                            z_xel      XMLTYpe      Path 'z'
               ) a,
               XmlTable ('/z'
                         Passing a.Z_xel
                         COLUMNS Z_Code Varchar2(10) Path '@code',
                                p_xel  XmlType      Path 'p'
              ) b,
               XmlTable ('/p'
                         Passing b.p_xel
                         COLUMNS p_debut Varchar2(10) Path '@debut'
                        ) c;

variables:

Code:

1
2
3
4
 
 TYPE numtab IS TABLE OF cur_rec%ROWTYPE INDEX BY BINARY_INTEGER;
nb_rows_report              numtab;
nb_rows_limit                 PLS_INTEGER := 10000;

fecth:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
OPEN cur_rec;
 
   LOOP
 
   FETCH cur_rec BULK COLLECT INTO nb_rows_report LIMIT nb_rows_limit;
 
          FOR i IN 1..nb_rows_report.COUNT 
 
                   LOOP
                   DBMS_OUTPUT.put_line ('id_structure = ');
                 END LOOP;
 
   EXIT WHEN cur_rec%NOTFOUND;  
 
   END LOOP;
 
   CLOSE cur_rec;

avec ce code , le TOAD se bloque

25/03/2014, 14h18
mnitu

Essayez de utiliser un type des données XMLType et non pas Clob. Et vous pouvez lire aussi Performance Tuning for XQuery