Exemplo de uso do componente XMLDB para a carga de arquivos XML para tabelas Oracle

A Oracle implementou o componente XMLDB (pacote de funções para a manipulação de arquivos XML) desde pelo menos a versão 9i do banco de dados. Esta “feature” do banco de dados pode ser bastante útil quando se precisa fazer a carga de arquivos do tipo XML para tabelas Oracle, para posterior leitura e manipulação dos dados. Vou tentar exemplificar (sem a pretensão de utilizar todo o poder do XMLDB) de uma forma bastante simplificada como se realiza a carga de um arquivo XML e posteriormente a leitura de suas TAGS através de uma instrução SQL.

1º) Verificar se o componente XMLDB está instalado no banco de dados

select comp_name from dba_registry where comp_name like ‘%XML%’;

COMP_NAME
Oracle XML Database

2º) Criar um diretório no banco de dados apontando para o diretório físico (dentro do servidor de banco de dados), onde está o arquivo XML

SQL>create or replace directory XMLDIR as ‘/u01/xml’;

3º) Criar uma tabela para armazenar o arquivo XML a ser carregado

SQL> create table INFO_XML (
id number primary key,
data_atualizacao date default sysdate,
conteudo XMLType);

4º) Criar um procedimento para realizar a carga do arquivo para a tabela INFO_XML

SQL>

CREATE OR REPLACE PROCEDURE load_xml ( p_id IN NUMBER,
p_filename IN VARCHAR2) AS
l_bfile BFILE := BFILENAME( ‘XMLDIR’, p_filename);
targetfile BFILE;
l_clob CLOB;
BEGIN

targetfile := l_bfile;

DBMS_LOB.createtemporary (l_clob, TRUE);

DBMS_LOB.fileopen(targetfile, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile(l_clob, targetfile, DBMS_LOB.getlength(targetfile));
DBMS_LOB.fileclose(targetfile);

INSERT INTO INFO_XML (
id,
data_atualizacao,
conteudo
)
VALUES (
p_id,
sysdate,
XMLTYPE.createXML(l_clob)
);
COMMIT;

DBMS_LOB.freetemporary (l_clob);
END;
/

5º) Realizar a carga do arquivo XML para dentro da tabela

SQL>BEGIN
load_xml(p_id=>1, p_filename => ‘xml_teste.xml’);
END;

6º) Verificar o conteudo carregado para a tabela INFO_XML

SQL> select conteudo from info_xml;

CONTEUDO
——————————————————————————–

1
EDUARDO ARRUDA
RUA TESTE, 598
VILA OLIMPIA
SAO PAULO
SP
03233-999

7º) Selecionar o conteúdo da tabela INFO_XML no formato de colunas

Se o arquivo XML possuir uma estrutura com mais de uma linha de PESSOA e a tag principal for TABELA_PESSOA (conforme o formato abaixo)

1
EDUARDO ARRUDA
RUA TESTE, 598
VILA OLIMPIA
SAO PAULO
SP
03233-999

2
EMILIA SHIDA
RUA TESTE, 999
BUTANTA
SAO PAULO
SP
04440-000

O comando SQL para a recuperação das informações no formato de tabela Oracle seria o seguinte:

select ExtractValue( value( pes ) , ‘/PESSOA/ID’ ) ID,
ExtractValue( value( pes ) , ‘/PESSOA/NOME’ ) NOME,
ExtractValue( value( pes ) , ‘/PESSOA/ENDERECO’ ) ENDERECO,
ExtractValue( value( pes ) , ‘/PESSOA/BAIRRO’ ) BAIRRO,
ExtractValue( value( pes ) , ‘/PESSOA/CIDADE’ ) CIDADE,
ExtractValue( value( pes ) , ‘/PESSOA/ESTADO’ ) ESTADO,
ExtractValue( value( pes ) , ‘/PESSOA/CEP’ ) CEP
from info_xml, TABLE( XMLSequence( Extract ( conteudo, ‘/TABELA_PESSOA/PESSOA’ ) ) ) pes
order by 1