Oracle - Ler/Insert ficheros XML

Teniendo un fichero XML:

<?xml version="1.0" encoding="ISO-8859-9"?>
<ROW>
<F_ID>1</F_ID>
<F_TITLE>TESTE</F_TITLE>
<F_DESCRIPTION>ESTO ES SOLO UN TEST</F_DESCRIPTION>
</ROW>

Una tabla:

CREATE TABLE XML_TEST (id number, title varchar2(200), description varchar2(200));

Para ler el fichero en la consola:

SELECT *
FROM XMLTABLE('/Row' PASSING XMLTYPE(BFILENAME('<DIRECTORY OBJECT CONTAINING THE XML FILE>', '<FILENAME>'),
NLS_CHARSET_ID('CHAR_CS'))
COLUMNS
ID NUMBER PATH 'F_ID',
TITLE VARCHAR2(200) PATH 'F_TITLE',
DESCRIPTION VARCHAR2(200) PATH 'F_DESCRIPTION'
);

Otra alternativa es hacer el insert de un fichero XML en una tabla:

INSERT INTO xml_test (ID, TITLE, DESCRIPTION)
SELECT *
FROM XMLTABLE('/Row' PASSING XMLTYPE(BFILENAME('<DIRECTORY OBJECT CONTAINING THE XML FILE>', '<FILENAME>'),
NLS_CHARSET_ID('CHAR_CS'))
COLUMNS
ID NUMBER PATH 'F_ID',
TITLE VARCHAR2(200) PATH 'F_TITLE',
DESCRIPTION VARCHAR2(200) PATH 'F_DESCRIPTION'
);

Comments

Popular posts from this blog

Oracle - duplicate fields in RECORD,TABLE or argument list are not permitted

Oracle - ORA-29282: invalid file ID, ORA-06512: at "SYS.UTL_FILE"