1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| CREATE OR REPLACE directory W_DIR AS '/dvagirh/dev/14.0/site/edition';
GRANT READ,WRITE ON DIRECTORY W_DIR to public;
ALTER session SET nls_numeric_characters=', ';
insert into xml_retph7(
select '081',MATRIC,PHTYPE,NATURE,to_number(RELIQUAT),to_number(MONTANT) from(
WITH DATA AS (SELECT XMLTYPE(BFILENAME('W_DIR','DocPaye.SARVCN.19081.201404.xml'), NLS_CHARSET_ID('UTF8')) x_el FROM dual)
select '081',X.MATRIC,Y.PHTYPE,Y.NATURE,Y.RELIQUAT,Y.MONTANT
from data t,xmltable('$e/DocPaye/Agent' passing t.x_el as "e"
columns MATRIC char(6) path './Matricule/@V',
PRECOMPTE xmltype path './PrecompteObligatoire') X
,xmltable ('/PrecompteObligatoire' passing X.PRECOMPTE
columns PHTYPE char(3) path './Nature/@V',
nature char(3) path 'Type/@V',
valini char(10) path 'ValIni/@V',
reliquat char(18) path 'Reliquat/@V',
montant char(18) path 'Montant/@V') (+) Y)); |
Partager