Intégration d'un fichier XML sous Oracle entete et détail

Version imprimable

Bonjour,
j 'ai un soucis d'intégration d'un fichier XML.
celui-ci est composé comme ceci :

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
 
<?xml version="1.0" encoding="ISO-8859-1"?>
<OPs>
	<OP>
		<OrdrePreparation>POC120087324</OrdrePreparation>
			<Ligne>
			<CodeArticle>65456</CodeArticle>
			<Quantite>1</Quantite>
			</Ligne>
			<Ligne>
			<CodeArticle>65585</CodeArticle>
			<Quantite>1</Quantite>
			</Ligne>
	</OP>
	<OP>
		<OrdrePreparation>POC120087321</OrdrePreparation>
			<Ligne>
			<CodeArticle>65318</CodeArticle>
			<Quantite>1</Quantite>
			</Ligne>
			<Ligne>
			<CodeArticle>65458</CodeArticle>
			<Quantite>1</Quantite>
			</Ligne>
	</OP>
</OPs>

on peut donc remarquer trois niveaux:
OPs niveau root
OP entete
Ligne détail

mon but est d'intégrer le fichier dans ma table MATABLE comme cela :

Code:

1
2
3
4
5
6
7
 
resultat attendu :
ordrepreparation		codearticle			quantite
POC120087324			65456				1
POC120087324			65585				1
POC120087321			65318				1
POC120087321			65458				1

Evidemment je n'obtiens pas ça
mes entetes et lignes font un produit carthésien qui donne ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
 
resultat obtenu :
ordrepreparation		codearticle			quantite
POC120087324			65456				1
POC120087324			65585				1
POC120087324			65318				1
POC120087324			65458				1
POC120087321			65456				1
POC120087321			65585				1
POC120087321			65318				1
POC120087321			65458				1

voici ma procedure stockée :

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
70
71
72
73
 
procedure integ()
  l_bfile   BFILE;
  l_clob    CLOB;
  l_parser  dbms_xmlparser.Parser;
  l_doc     dbms_xmldom.DOMDocument;
  l_nl      dbms_xmldom.DOMNodeList;
  l_nl2      dbms_xmldom.DOMNodeList;
  l_n2       dbms_xmldom.DOMNode;
  l_n       dbms_xmldom.DOMNode;
 
src_csid       NUMBER := NLS_CHARSET_ID('UTF8');  
dest_offset    INTEGER := 1;
src_offset     INTEGER := 1;
lang_context   INTEGER := dbms_lob.default_lang_ctx;
warning        INTEGER;
 
Ordre_Preparation		Varchar2(50);
Code_Article		Number;
Qte			Number;
 
 
BEGIN
 
  l_bfile := BFileName(PATH,FIC_XML);
 
  dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');  
 
    dbms_lob.createtemporary(l_clob, cache=>FALSE);
 
  dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
  dbms_lob.loadclobfromFile(  l_clob,
                          l_bfile,
                         dbms_lob.getLength(l_bfile),
                        dest_offset,
                        src_offset, 
                        src_csid, 
                        lang_context,
                        warning);
   dbms_lob.close(l_bfile);
 
  l_parser := dbms_xmlparser.newParser;
  dbms_xmlparser.parseClob(l_parser, l_clob);
  l_doc := dbms_xmlparser.getDocument(l_parser);  
 
  dbms_lob.freetemporary(l_clob);
  dbms_xmlparser.freeParser(l_parser);
 
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/OPs/OP');
-- Boucle entete
  FOR cur_stage_xml IN 0 .. dbms_xmldom.getLength(l_nl)-1  LOOP
    l_n := dbms_xmldom.item(l_nl, cur_stage_xml);
 
			dbms_xslprocessor.valueOf(l_n,'OrdrePreparation/text()',Ordre_Preparation);
 
				l_nl2 := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/OPs/OP/Ligne');
 
                                                 -- Boucle détail
				              FOR cur_stage_xml2 IN 0 .. dbms_xmldom.getLength(l_nl2) LOOP  
                				l_n2 := dbms_xmldom.item(l_nl2, cur_stage_xml2);
 
                				dbms_xslprocessor.valueOf(l_n2,'CodeArticle/text()',Code_Article);
                				dbms_xslprocessor.valueOf(l_n2,'Quantite/text()',Qte);
 
                                                INSERT INTO MATABLE (OrdrePreparation,CodeArticle,Quantite)
                    	                      VALUES(Ordre_Preparation,Code_Article,Qte);
 
                                                  END LOOP;
  END LOOP; 
 
COMMIT; 
dbms_xmldom.freeDocument(l_doc);
END;

je suis preneur de tout commentaire ou de toute solution alternative.

merci de vos reponses

vincent

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
 
create or replace function getFileContent(file bfile)
return CLOB deterministic
is
   charContent     CLOB := ' ';
   targetFile      bfile;
   warning         number;
begin
  targetFile := file;
  DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly);
  DBMS_LOB.loadfromFile(charContent,targetFile,
  DBMS_LOB.getLength(targetFile),1,1);
  DBMS_LOB.fileclose(targetFile);
  return charContent;
end;
/            
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0 
Connected as mni
 
SQL> 
SQL> Select EXTRACTVALUE(e.column_value,'/OP/OrdrePreparation') Ordrepeparation,
  2         EXTRACTVALUE(f.column_value,'/Ligne/CodeArticle')  CodeArticle,
  3         EXTRACTVALUE(f.column_value,'/Ligne/Quantite') Quantite
  4    From (Select XmlType(Getfilecontent(BFILENAME('DIR_EXTRACTION','test.xml'))) col_xml
  5            From dual
  6         ) d,
  7    TABLE(XMLSequence(extract(d.col_xml,'/'))) l,
  8    TABLE(XMLSequence(extract(value(l), '/OPs/OP'))) e,
  9    TABLE(XMLSequence(extract(value(e), '/OP/Ligne'))) f
 10  /
 
ORDREPEPARATION                                                                  CODEARTICLE                                                                      QUANTITE
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
POC120087324                                                                     65456                                                                            1
POC120087324                                                                     65585                                                                            1
POC120087321                                                                     65318                                                                            1
POC120087321                                                                     65458                                                                            1
 
SQL>

11/06/2009, 16h13
xanadu78

merci a toi mnitu !

ta méthode marche nickel
même si dans la forme je n'ai pas trop compris comment son jointe les tables d,e, l et f.

en revanche , je remarque que tu ne parse pas le fichier or j'en ai besoin pour valider sa structure.

qu'en penses tu ?

Je pense que cella est fait en dessous du marché.

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
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0 
Connected as mni
 
SQL> 
SQL> Select EXTRACTVALUE(e.column_value,'/OP/OrdrePreparation') Ordrepeparation,
  2         EXTRACTVALUE(f.column_value,'/Ligne/CodeArticle')  CodeArticle,
  3         EXTRACTVALUE(f.column_value,'/Ligne/Quantite') Quantite
  4    From (Select XmlType(Getfilecontent(BFILENAME('DIR_EXTRACTION','fich.xml'))) col_xml
  5            From dual
  6         ) d,
  7    TABLE(XMLSequence(extract(d.col_xml,'/'))) l,
  8    TABLE(XMLSequence(extract(value(l), '/OPs/OP'))) e,
  9    TABLE(XMLSequence(extract(value(e), '/OP/Ligne'))) f
 10  /
 
ORDREPEPARATION                                                                  CODEARTICLE                                                                      QUANTITE
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
 
SQL> 
SQL> Select EXTRACTVALUE(e.column_value,'/livre/id') id,
  2         EXTRACTVALUE(e.column_value,'/livre/titre')  titre,
  3         EXTRACTVALUE(e.column_value,'/livre/auteur') auteur
  4    From (Select XmlType(Getfilecontent(BFILENAME('DIR_EXTRACTION','fich.xml'))) col_xml
  5            From dual
  6         ) d,
  7    TABLE(XMLSequence(extract(d.col_xml,'/'))) l,
  8    TABLE(XMLSequence(extract(value(l), '/livres/livre'))) e
  9  /
 
ID                                                                               TITRE                                                                            AUTEUR
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1                                                                                Base de données oracle                                                           Eric pillon
2                                                                                test

En fonction de ta version d’Oracle et de ton cahier de charge il y a peut être de meilleur méthodes. Regarde aussi une alternative ici.
Quelque explication sur le « comment ça marche » de la requête tu trouve ici.

merci encore mnitu!

je me suis servis de ton "select" et je l'ai adapté dans une procedure stocké!
un collègue a moi y a rajouté un contrôle de strucure suivant :

la procedure d'intégration :

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
70
 
PROCEDURE PROC_INTEGRATION (dir VARCHAR2, inpfile VARCHAR2) 
IS
  l_clob    CLOB;
  l_bfile   BFILE;
  l_parser  dbms_xmlparser.Parser;
  l_doc     dbms_xmldom.DOMDocument;
  l_nl      dbms_xmldom.DOMNodeList;
  l_n       dbms_xmldom.DOMNode;
  l_temp    VARCHAR2(4000);
  src_csid       NUMBER := NLS_CHARSET_ID('UTF8');  
  dest_offset    INTEGER := 1;
  src_offset     INTEGER := 1;
  lang_context   INTEGER := dbms_lob.default_lang_ctx;
  warning        INTEGER;
 
  TYPE tab_type IS TABLE OF CMDCLI_TEST%ROWTYPE;
  t_tab  tab_type := tab_type();
BEGIN
    DELETE FROM CMDCLI ;
    COMMIT;
 
  l_bfile := BFileName(dir, inpfile);
 
  dbms_lob.createtemporary(l_clob, cache=>FALSE);
 
  dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
  dbms_lob.loadclobfromFile(  l_clob,
                          l_bfile,
                         dbms_lob.getLength(l_bfile),
                        dest_offset,
                        src_offset, 
                        src_csid, 
                        lang_context,
                        warning);
  dbms_lob.close(l_bfile);
 
  dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');
 
 
       l_parser := dbms_xmlparser.newParser;
    --Declaration du parseur, controle de la structure du fichier XML
        dbms_xmlparser.parseClob(l_parser, l_clob);
        l_doc := dbms_xmlparser.getDocument(l_parser);
 
        dbms_lob.freetemporary(l_clob);
        dbms_xmlparser.freeParser(l_parser);
 
 INSERT INTO MATABLE  (select 
        EXTRACTVALUE(e.column_value,'/OP/CodeActivite') CodeActivite, 
        EXTRACTVALUE(f.column_value,'/Ligne/CodeArticle') CodeArticle, 
        EXTRACTVALUE(f.column_value,'/Ligne/Quantite') Quantite
FROM (SELECT XmlType(GETFILECONTENT(BFILENAME(dir,inpfile))) col_xml 
        FROM dual 
 ) d,
    TABLE(XMLSequence(extract(d.col_xml,'/'))) l ,
    TABLE(XMLSequence(extract(value(l), '/OPs/OP'))) e, 
    TABLE(XMLSequence(extract(value(e), '/OP/Ligne'))) f 
 );
commit;
 
  COMMIT; 
  dbms_xmldom.freeDocument(l_doc);
EXCEPTION
  WHEN OTHERS THEN
    dbms_lob.freetemporary(l_clob);
    dbms_xmlparser.freeParser(l_parser);
    dbms_xmldom.freeDocument(l_doc);
raise ;
END;

la fonction GETFILECONTENT :

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
 
FUNCTION GETFILECONTENT(file bfile) RETURN CLOB deterministic 
IS charContent CLOB := ' '; 
targetFile bfile; 
 
  dest_offset    INTEGER := 1;
 src_offset     INTEGER := 1;
   l_parser  dbms_xmlparser.Parser;
   l_doc     dbms_xmldom.DOMDocument;
 
 lang_context   INTEGER := dbms_lob.default_lang_ctx;
 warning        INTEGER;
src_csid       NUMBER := NLS_CHARSET_ID('UTF8');  
 
begin targetFile := file; 
DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly); 
--BMS_LOB.loadfromFile(charContent,targetFile, DBMS_LOB.getLength(targetFile),1,1); 
 dbms_lob.loadclobfromFile( charContent,
                          targetFile,
                         dbms_lob.getLength(targetFile),
                        dest_offset,
                        src_offset, 
                        src_csid, 
                        lang_context,
                        warning);
DBMS_LOB.fileclose(targetFile); 
 
 
 RETURN charContent; end;

Nous avons du modifié la fonction GETFILECONTENT afin de pouvoir gerer le format UTF8.

see you soon on Developpez.net

vincent

11/06/2009, 21h36
mnitu
J'aurais fait plutôt une fonction pipelined pour écrire directement un
Code:

1 2 Insert into matable select … from table(ma_fonction(p1,p2)))
mais ce n'est qu'une idée.

Bonjour,

ma procedure qui marchait bien depuis 1 mois me retourne dorenavant
l'erreur suivante :

Code:

1
2
3
4
5
6
7
 
o_sqlcode = -31011
o_errmsg = ORA-31011: Echec d'analyse XML
ORA-19202: Une erreur s'est produite lors du traitement la fonction XML (
LPX-00004: erreur interne "MultiChar overflow"
Error at line 1
)

Ma procedure

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
70
 
PROCEDURE PROC_INTEGRATION (dir VARCHAR2, inpfile VARCHAR2) 
IS
  l_clob    CLOB;
  l_bfile   BFILE;
  l_parser  dbms_xmlparser.Parser;
  l_doc     dbms_xmldom.DOMDocument;
  l_nl      dbms_xmldom.DOMNodeList;
  l_n       dbms_xmldom.DOMNode;
  l_temp    VARCHAR2(4000);
  src_csid       NUMBER := NLS_CHARSET_ID('UTF8');  
  dest_offset    INTEGER := 1;
  src_offset     INTEGER := 1;
  lang_context   INTEGER := dbms_lob.default_lang_ctx;
  warning        INTEGER;
 
  TYPE tab_type IS TABLE OF CMDCLI_TEST%ROWTYPE;
  t_tab  tab_type := tab_type();
BEGIN
    DELETE FROM CMDCLI ;
    COMMIT;
 
  l_bfile := BFileName(dir, inpfile);
 
  dbms_lob.createtemporary(l_clob, cache=>FALSE);
 
  dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
  dbms_lob.loadclobfromFile(  l_clob,
                          l_bfile,
                         dbms_lob.getLength(l_bfile),
                        dest_offset,
                        src_offset, 
                        src_csid, 
                        lang_context,
                        warning);
  dbms_lob.close(l_bfile);
 
  dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');
 
 
       l_parser := dbms_xmlparser.newParser;
    --Declaration du parseur, controle de la structure du fichier XML
        dbms_xmlparser.parseClob(l_parser, l_clob);
        l_doc := dbms_xmlparser.getDocument(l_parser);
 
        dbms_lob.freetemporary(l_clob);
        dbms_xmlparser.freeParser(l_parser);
 
 INSERT INTO MATABLE  (SELECT 
        EXTRACTVALUE(e.column_value,'/OP/CodeActivite') CodeActivite, 
        EXTRACTVALUE(f.column_value,'/Ligne/CodeArticle') CodeArticle, 
        EXTRACTVALUE(f.column_value,'/Ligne/Quantite') Quantite
FROM (SELECT XmlType(GETFILECONTENT(BFILENAME(dir,inpfile))) col_xml 
        FROM dual 
 ) d,
    TABLE(XMLSequence(extract(d.col_xml,'/'))) l ,
    TABLE(XMLSequence(extract(value(l), '/OPs/OP'))) e, 
    TABLE(XMLSequence(extract(value(e), '/OP/Ligne'))) f 
 );
commit;
 
  COMMIT; 
  dbms_xmldom.freeDocument(l_doc);
EXCEPTION
  WHEN OTHERS THEN
    dbms_lob.freetemporary(l_clob);
    dbms_xmlparser.freeParser(l_parser);
    dbms_xmldom.freeDocument(l_doc);
raise ;
END;

et une fonction rattachée :

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
 
FUNCTION GETFILECONTENT(file bfile) RETURN CLOB deterministic 
IS charContent CLOB := ' '; 
targetFile bfile; 
 
  dest_offset    INTEGER := 1;
 src_offset     INTEGER := 1;
   l_parser  dbms_xmlparser.Parser;
   l_doc     dbms_xmldom.DOMDocument;
 
 lang_context   INTEGER := dbms_lob.default_lang_ctx;
 warning        INTEGER;
src_csid       NUMBER := NLS_CHARSET_ID('UTF8');  
 
begin targetFile := file; 
DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly); 
--BMS_LOB.loadfromFile(charContent,targetFile, DBMS_LOB.getLength(targetFile),1,1); 
 dbms_lob.loadclobfromFile( charContent,
                          targetFile,
                         dbms_lob.getLength(targetFile),
                        dest_offset,
                        src_offset, 
                        src_csid, 
                        lang_context,
                        warning);
DBMS_LOB.fileclose(targetFile); 
 
 
 RETURN charContent; end;

je suis preneur de tous commentaire et de toute modif

merci de votre aide

17/09/2009, 08h33
mnitu

A priori il y a peu d’espoir d’avoir la solution ici

Citation:

LPX-00004: internal error "~s"
Cause: An internal error has occurred.

Action: Contact Oracle Support Services and report the error

Pourquoi ta fonction est déclarée comme "deterministic" ? Si je ne me trompe pas t’as fonction ne rempli pas les conditions pour être déclarée "deterministic".
17/09/2009, 08h49
xdescamp

Vu ici en cherchant "MultiChar overflow" sur Google: http://forums.oracle.com/forums/thre...sageID=3695268.

Visiblement il s'agit d'un bug connu en 9i, corrigé par un patchset ou en 10g. Mais quelqu'un l'a reproduit en 11g. A voir, je ne sais pas quelle version de base tu utilises.

Autre piste à creuser: ça peut également venir d'un problème de jeu de caractères:

Citation:

Mixups between NLS client and database (and therefore forcing conversion) could cause this.

Si ça a fonctionné pendant un mois, je pencherais plutôt vers cette seconde piste.
17/09/2009, 12h19
xanadu78

bonjour, merci de vos reponses .
le fichier xml qui a provoquer mon plantage faisait 298 ko.
alors que j avais l'habitude d'en recevoir de moins de 100 Ko.
j ai donc manuellement decoupé mon fichier en 4, et là plus de probleme !
mais cette solution n'est pas viable pour moi.
il y a donc un rapport avec la volumétrie du fichier a intégrer.
est ce que cette piste serait a suivre ?