Discussion :

[Corazon01]

Bonjour

Je dois lire des XML ayant des relations (1,n) en cascade et je bute, dès le départ, sur un simple problème de parents/enfants J'ai créé ici un exemple très simplifié avec deux factures ayant chacune deux lignes de détail :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
DECLARE
  l_clob          CLOB := '<?xml version="1.0" encoding="UTF-8"?>
<invoices>
  <invoice>                                                            
    <inv_nr>1</inv_nr>
    <customer>A</customer>
    <inv_detail>                                                     
      <inv_code>1/1</inv_code>
      <inv_amount>10</inv_amount>
    </inv_detail>                                                    
    <inv_detail>                                                     
      <inv_code>1/2</inv_code>
      <inv_amount>20</inv_amount>
    </inv_detail>                                                    
  </invoice>       
  <invoice>                                                            
    <inv_nr>2</inv_nr>
    <customer>B</customer>
    <inv_detail>                                                     
      <inv_code>2/1</inv_code>
      <inv_amount>40</inv_amount>
    </inv_detail>                                                    
    <inv_detail>                                                     
      <inv_code>2/2</inv_code>
      <inv_amount>50</inv_amount>
    </inv_detail>                                                    
  </invoice>       
</invoices>';
  l_parser        DBMS_XMLPARSER.parser;
  my_invoices     DBMS_XMLDOM.domdocument;
  curr_invoice    DBMS_XMLDOM.domnodelist;
  curr_inv_det    DBMS_XMLDOM.domnodelist;
  curr_item       DBMS_XMLDOM.domnode;
  curr_item_det   DBMS_XMLDOM.domnode;
  vs_inv_nr       VARCHAR2(10);
  vs_custonmer    VARCHAR2(10);
  vs_inv_code     VARCHAR2(10);
  vs_inv_amount   VARCHAR2(10);
  student_id      NUMBER;
  student_name    VARCHAR2(100);
BEGIN
  DBMS_SESSION.set_nls('NLS_DATE_FORMAT', '''DD-MON-YYYY''');
  l_parser := DBMS_XMLPARSER.newparser;
  DBMS_XMLPARSER.parseclob(l_parser, l_clob);
  my_invoices := DBMS_XMLPARSER.getdocument(l_parser);
  DBMS_LOB.freetemporary(l_clob);
  DBMS_XMLPARSER.freeparser(l_parser);
  --
  curr_invoice := DBMS_XSLPROCESSOR.selectnodes(DBMS_XMLDOM.makenode(my_invoices), 'invoices/invoice');
  DBMS_OUTPUT.put_line(CHR(10) || 'number of invoices ' || vs_inv_nr || ' - ' || DBMS_XMLDOM.getlength(curr_invoice));

  FOR i IN 0 .. DBMS_XMLDOM.getlength(curr_invoice) - 1 LOOP
    curr_item := DBMS_XMLDOM.item(curr_invoice, i);
    DBMS_XSLPROCESSOR.valueof(curr_item, 'inv_nr/text()', vs_inv_nr);
    DBMS_XSLPROCESSOR.valueof(curr_item, 'customer/text()', vs_custonmer);
    --
    -- I think the problem is here, I read all the XML, instead of only the part of XML concerning the actual invoice !
    -- I don't find how to select only this part.
    curr_inv_det := DBMS_XSLPROCESSOR.selectnodes(DBMS_XMLDOM.makenode(my_invoices), 'invoices/invoice/inv_detail');
    DBMS_OUTPUT.put_line('lines for inv ' || vs_inv_nr || '-' || vs_custonmer || ' : ' || DBMS_XMLDOM.getlength(curr_inv_det));

    FOR j IN 0 .. DBMS_XMLDOM.getlength(curr_inv_det) - 1 LOOP
      curr_item_det := DBMS_XMLDOM.item(curr_inv_det, j);
      DBMS_XSLPROCESSOR.valueof(curr_item_det, 'inv_code/text()', vs_inv_code);
      DBMS_XSLPROCESSOR.valueof(curr_item_det, 'inv_amount/text()', vs_inv_amount);
      DBMS_OUTPUT.put_line(' detail : ' || j || ' - ' || vs_inv_code || '-' || vs_inv_amount);
    END LOOP;
  END LOOP;

  DBMS_XMLDOM.freedocument(my_invoices);
/* RESULT OF OUTPUT
number of invoices  - 2
invoice is : 1-A
number of detail lines for invoice 1-A : 4            -- Should be 2
 detail : 0 - 1/1-10
 detail : 1 - 1/2-20
 detail : 3 - 2/1-40                                  -- Should not be selected, belongs to inv 2-B
 detail : 4 - 2/2-50                                  -- Should not be selected, belongs to inv 2-B
invoice is : 2-B
number of detail lines for invoice 2-B : 4            -- Should be 2
 detail : 0 - 1/1-10                                  -- Should not be selected, belongs to inv 1-A
 detail : 1 - 1/2-20                                  -- Should not be selected, belongs to inv 1-A
 detail : 3 - 2/1-40
 detail : 4 - 2/2-50
*/
END;

Mon problème, c'est que lorsque je veux lire les détails, il les lit tous, au lieu de ne lire que ceux qui concernent la facture. Je devrai donc pouvoir lui dire de ne tenir compte que de la portion d'XML de la facture en cours. Je suis convaincu que le problème se situe au niveau de la ligne en gras, car j'y passe en paramètre la totalité du document en paramètre : BMS_XMLDOM.makenode(my_invoices), or je devrai certainement ne passer qu'une partie, mais comment ???

D'avance, merci pour votre aide.

Amitiés
Martin

[mnitu]

J'utilise d'habitude XMLTable pour extraire les informations d'un document XML. Néanmoins à ce que j'ai compris sur l'utilisation du XMLDOM après avoir lu l'élément DBMS_XMLDOM.item vous lisez ses attributs et vous obtenez la liste des ses éléments enfants via DBMS_XMLDOM.getchildnodes.
Tel que vous l'avez écrit votre code obtiens la liste de tous les inv_details, répété pour chaque éléments de type invoice.

[Corazon01]

Merci mnitu

J'ai essayé, mais je bute encore toujours dès le second niveau 1,n. Je vais voir si je peux trouver un exemple parlant du XMLTable.
L'XML ne me semble vraiment pas évident à lire en PL-SQL et j'avoue avoir des difficultés à comprendre certaines notion liées aux XML !

Amitiés
Martin

[mnitu]

C'est quelque chose de ce type. En PL/SQL c'est un select into et le clob est passez directement dans XmlTable sans besoin de la partie With.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> With Data As (
  2    Select xmltype('<?xml version="1.0" encoding="UTF-8"?>
  3  <invoices>
  4    <invoice>
  5      <inv_nr>1</inv_nr>
  6      <customer>A</customer>
  7      <inv_detail>
  8        <inv_code>1/1</inv_code>
  9        <inv_amount>10</inv_amount>
 10      </inv_detail>
 11      <inv_detail>
 12        <inv_code>1/2</inv_code>
 13        <inv_amount>20</inv_amount>
 14      </inv_detail>
 15    </invoice>
 16    <invoice>
 17      <inv_nr>2</inv_nr>
 18      <customer>B</customer>
 19      <inv_detail>
 20        <inv_code>2/1</inv_code>
 21        <inv_amount>40</inv_amount>
 22      </inv_detail>
 23      <inv_detail>
 24        <inv_code>2/2</inv_code>
 25        <inv_amount>50</inv_amount>
 26      </inv_detail>
 27    </invoice>
 28  </invoices>'
 29  ) as xel
 30    from dual
 31  )
 32  Select t.inv_nr,
 33         t.costumer,
 34         s.inv_code,
 35         s.inv_amount
 36    from Data,
 37         XmlTable('/invoices/invoice'
 38                  passing xel
 39                  Columns inv_nr    Varchar2(15) Path 'inv_nr',
 40                          costumer  Varchar2(15) Path 'customer',
 41                          xdetail   XmlType      Path 'inv_detail'
 42                 ) t,
 43         XmlTable('/inv_detail'
 44                  passing t.xdetail
 45                  Columns inv_code    Varchar2(15) Path 'inv_code',
 46                          inv_amount  Number       Path 'inv_amount'
 47                 ) s
 48  /
 
INV_NR          COSTUMER        INV_CODE        INV_AMOUNT
--------------- --------------- --------------- ----------
1               A               1/1                     10
1               A               1/2                     20
2               B               2/1                     40
2               B               2/2                     50
 
SQL>

[Corazon01]

Merci Mnitu

Je vais chercher dans cette voie

Amitiés
Martin

[Corazon01]

Re-bonjour

Cela fonctionne du tonnerre . Vraiment pratique comme commande. Grand merci.

Je bute par contre encore sur un problème de character set

Notre database est paramétrée comme suit : NLS_CHARACTERSET='WE8MSWIN1252' / NLS_NCHAR_CHARACTERSET='AL16UTF16' et le fichier que je vais lire est en UTF8 : <?xml version="1.0" encoding="UTF-8"?>

Le fichier XML est multilangues (exemple) et je bute sur les caractères accentués, comme par exemple les Umlaut en allemand : ä/ö/ü. Je sais les stocker sans problèmes dans la database, le problème n'est donc pas le character set de la database, mais la conversion ou la lecture erronée du fichier.

Je pensais initialement que c'était la conversion en xml (xmltype.createxml) qui posait problème, mais le clob est déjà mauvais avant celle-ci !
Pour vérifier, j'ai fait une simple table toto avec 2 champs : texte1+clob1 et utilisé ce code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
DECLARE
  l_bfile   BFILE;
  l_clob    CLOB;
  vx_xml    XMLTYPE;
BEGIN
  l_bfile := BFILENAME('PLDA_BE_FIC_IMPORT', 'IE931-TEST.XML');
  DBMS_LOB.createtemporary(l_clob, cache => FALSE);
  DBMS_LOB.open(l_bfile, DBMS_LOB.lob_readonly);
  DBMS_LOB.loadfromfile(dest_lob => l_clob, src_lob => l_bfile, amount => DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.close(l_bfile);
 
  INSERT INTO toto(texte1, clob1) VALUES (TO_CHAR(SYSDATE, 'HH24MISS'), l_clob);
 
  -- Conversion de la variable clob en variable de type XML
  -- vx_xml := xmltype.createxml(l_clob);
  DBMS_LOB.freetemporary(l_clob);
END;

Le clob contient <CusOffUsuNam45>Mäder</CusOffUsuNam45> au lieu de <CusOffUsuNam45>Mäder</CusOffUsuNam45>

J'ai beau chercher, je ne vois pas où je pourrai dre que mon fichier est en UTF8 afin que la base fasse la bonne conversion !
Peut-être que ma méthode de "conversion" en XML n'est pas non plus la meilleure (passer par un clob en premier) ???

Amitiés
Martin