1 pièce(s) jointe(s)
[11g][XML] Problème de lecture d'un XML parents/enfants
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 :oops: J'ai créé ici un exemple très simplifié avec deux factures ayant chacune deux lignes de détail :
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
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
