XML file - sqlldr et ou table externe - Table ORACLE

[argoet]

Bonjour à tous !
A regarder ce qui est fait concernant la manipulation de données XML dans ORACLE , j'ai l'impression que c'est tous sauf simple ...
Je suis en environnement Oracle 9i Sur HP/UX 11

J'ai la table ORACLE suivante

Code :

1
2
3
4
5
6
DESC distrib
 Name                                      NULL?    Type
 ----------------------------------------- -------- ----------------------------
 DISTRIB_ID                                         NUMBER(4)
 DISTRIB_NAME                                       VARCHAR2(32)
 DISTRIB_PARAM                                      VARCHAR2(100)

J'ai le fichier XML suivant

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
<?xml version="1.0" encoding="ISO-8859-1"?>                
<message STATUS="complete">
<distrib>
    <distrib_id>0001</distrib_id>
    <distrib_name>nom distrib 1</distrib_name>
    <distrib_param>parametre A</distrib_param>
</distrib>
<distrib>
    <distrib_id>0002</distrib_id>
    <distrib_name>nom distrib 2</distrib_name>
    <distrib_param>parametre B</distrib_param>
</distrib>
<distrib>
    <distrib_id>0003</distrib_id>
    <distrib_name>nom distrib 3</distrib_name>
    <distrib_param>parametre C</distrib_param>
</distrib>
 
<distrib>
    <distrib_id>0004</distrib_id>
    <distrib_name>nom distrib 4</distrib_name>
    <distrib_param>parametre D</distrib_param>
</distrib>
</message>

La vous m'avez compris !! comment charger les données du fichiers XML dans la table DISTRIB ?

Est il possible via une table externe ORACLE de voir les données du fichier XML comme le contenu de la table distrib ?

Un truc du genre

Code :

SELECT * FROM Ma_Table_Externe

Qui me rend :

Code :

1
2
3
4
5
6
DISTRIB_ID DISTRIB_NAME                     DISTRIB_PARAM
---------- -------------------------------- --------------------
         1 nom distrib 1                    Parametre A
         2 nom distrib 2                    Parametre B
 
etc..etc

Je ne voit pas comment configurer la table externe / ou le fichier de controle sqlldr pour avoir cela
Merci de votre aide
Cordialement
@argoet

[plabrevo]

En modifiant legerement le .xml, place sous /usr/tmp, cela pourrait donner

Inbound document:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<message STATUS="complete">
<distrib>
    <distrib_id>0001</distrib_id>
    <distrib_name>nom distrib 1</distrib_name>
    <distrib_param>parametre A</distrib_param>
</distrib>
<distrib>
    <distrib_id>0002</distrib_id>
    <distrib_name>nom distrib 2</distrib_name>
    <distrib_param>parametre B</distrib_param>
</distrib>
<distrib>
    <distrib_id>0003</distrib_id>
    <distrib_name>nom distrib 3</distrib_name>
    <distrib_param>parametre C</distrib_param>
</distrib>
<distrib>
    <distrib_id>0004</distrib_id>
    <distrib_name>nom distrib 4</distrib_name>
    <distrib_param>parametre D</distrib_param>
</distrib>
</message>

Execution Output:

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
TABLE dropped.
 
 
Directory dropped.
 
 
TABLE created.
 
 
Directory created.
 
Processing record 1
Distrib Id   0001
Distrib Name nom distrib 1
Processing record 2
Distrib Id   0002
Distrib Name nom distrib 2
Processing record 3
Distrib Id   0003
Distrib Name nom distrib 3
Processing record 4
Distrib Id   0004
Distrib Name nom distrib 4
 
PL/SQL procedure successfully completed.
 
 
DISTRIB_ID DISTRIB_NAME    DISTRIB_PARAM
---------- --------------- ----------------------------------------
         1 nom distrib 1   parametre A
         2 nom distrib 2   parametre B
         3 nom distrib 3   parametre C
         4 nom distrib 4   parametre D
 
 
Commit complete.

Script:

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
DROP TABLE plx_distrib
/
DROP DIRECTORY plx_directory
/
CREATE TABLE plx_distrib
(DISTRIB_ID      INTEGER
,DISTRIB_NAME    VARCHAR2(32)
,DISTRIB_PARAM   VARCHAR2(100)
)
/
CREATE DIRECTORY plx_directory AS '/usr/tmp'
/
 
SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
l_record_counter      INTEGER;
l_nested_counter      INTEGER;
C_MAX_CUSTOMER        INTEGER := 100;
 
BEGIN
l_record_counter := 0;
 
FOR j IN (SELECT extractValue(value(ct),'distrib/distrib_id')             Distrib_id
                ,extractValue(value(ct),'distrib/distrib_name')           Distrib_name
                ,extractValue(value(ct),'distrib/distrib_param')          Distrib_param
          FROM   TABLE(XMLSequence(extract(XMLTYPE(bfilename('PLX_DIRECTORY','distrib.xml'),nls_charset_id('AL32UTF8')),'/message/distrib'))) ct) LOOP
 
      l_record_counter := l_record_counter + 1;
 
      dbms_output.put_line('Processing record '||TO_CHAR(l_record_counter));
 
      dbms_output.put_line('Distrib Id   ' ||j.distrib_id);
      dbms_output.put_line('Distrib Name ' ||j.distrib_name);
 
      INSERT INTO plx_distrib
      (distrib_id
      ,distrib_name
      ,distrib_param)
      VALUES
      (j.distrib_id
      ,j.distrib_name
      ,j.distrib_param)
      ;
END LOOP;
END;
/
 
COL distrib_id    FOR 999
COL distrib_name  FOR A15
COL distrib_param FOR A40
 
SELECT *
FROM   plx_distrib
/
COMMIT
/
EXIT

[argoet]

Envoyé par plabrevo

En modifiant legerement le .xml, place sous /usr/tmp, cela pourrait donner .... + Code PL/SQL

Je vous remercie , "plabrevo" , cependant ce n'est pas la réponse que j'atendais (c'est à quelques chose pres ce que je fait aujourd'hui) .

PS : Votre syntaxe est en 10G (Je suis en 9i tel que précisé en début de post )

Mon propos ce situe vraiement à la lecture du fichier via sqlldr et ou une table exetrne qui utilise le "loader" ORACLE

[argoet]

C'est pas encore le top mais je pense que cela pourra aider
prérequis : les 2 premieres lignes du fichier sont à supprimer
l'ordre de présence des "<tag>" distrib_id , distrib_name , distrib_param dans le fichier doit toujours etre le meme
Fichier EXT_DISTRIB.xml

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<distrib>
    <distrib_id>0001</distrib_id>
    <distrib_name>nom distrib 1</distrib_name>
    <distrib_param>parametre A</distrib_param>
</distrib>
<distrib>
    <distrib_id>0002</distrib_id>
    <distrib_name>nom distrib 2</distrib_name>
    <distrib_param>parametre B</distrib_param>
</distrib>
<distrib>
    <distrib_id>0003</distrib_id>
    <distrib_name>nom distrib 3</distrib_name>
    <distrib_param>parametre C</distrib_param>
</distrib>
<distrib>
    <distrib_id>0004</distrib_id>
    <distrib_name>nom distrib 4</distrib_name>
    <distrib_param>parametre D</distrib_param>
</distrib>

Création de la table Externe , basé sur le fichier xml

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE EXT_Distrib (
  Distrib_Id            varchar2(50),
  Distrib_Name          varchar2(50),
  Distrib_Param         varchar2(50)
)
Organization External
 (Type Oracle_Loader
  DEFAULT directory TMP_DIR
  ACCESS Parameters
      (
       Records Delimited BY '</distrib>'
       Badfile TMP_DIR:'EXT_Distrib.bad'        
       logfile TMP_DIR:'EXT_Distrib.log'        
       FIELDS (
              Vide        char ENCLOSED BY "<distrib" AND ">",
              Distrib_Id  char ENCLOSED BY "<distrib_id>" AND "</distrib_id>",
              Distrib_Name char ENCLOSED BY "<distrib_name>" AND "</distrib_name>",
              Distrib_Param char ENCLOSED BY "<distrib_param>" AND "</distrib_param>"
              )
      )
  Location ('EXT_DISTRIB.xml')
 )
 reject LIMIT unlimited;

Code :

1
2
3
4
5
6
7
8
9
 
SELECT * FROM ext_distrib;
 
DISTRIB_ID               DISTRIB_NAME             DISTRIB_PARAM
-----  ----------------- ------------------------ ------------------------                                                      
0001                     nom distrib 1            parametre A
0002                     nom distrib 2            parametre B
0003                     nom distrib 3            parametre C
0004                     nom distrib 4            parametre D