Posts

Showing posts from August, 2013

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_C…