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; |
Partager