Olá pessoal,
Estou fazendo a importação e tratamento de arquivos XML na base Oracle. Todo o processo de identificação dos arquivos XML no diretório, importação dos arquivos na base está ok. Não estou conseguindo fazer a leitura dos dados da table sys.xmltype.
Já tentei fazer a leitura de várias formas, mas nenhuma delas com sucesso.
--ETAPA DE LEITURA DOS ARQUIVOS XML E IMPORTAÇÃO PARA BD
Selecionar tudo
---CRIA DIRETÓRIOS PARA LEITURA E GRAVAÇÃO DOS ARQUIVOS
CREATE OR REPLACE DIRECTORY d_received_files AS 'D:\Receive';
CREATE OR REPLACE DIRECTORY d_bkp_received_files AS 'D:\Bkp_Receive';
CREATE OR REPLACE DIRECTORY d_sended_files AS 'D:\Send';
CREATE OR REPLACE DIRECTORY d_bkp_sended_files AS 'D:\Bkp_Send';
---GRANT LEITURA E GRAVAÇÃO NOS DIRETÓRIOS (USUÁRIO SYS)
GRANT READ, WRITE ON DIRECTORY d_received_files TO mlxlevesul;
GRANT READ, WRITE ON DIRECTORY d_bkp_received_files TO mlxlevesul;
GRANT READ, WRITE ON DIRECTORY d_sended_files TO mlxlevesul;
GRANT READ, WRITE ON DIRECTORY d_bkp_sended_files TO mlxlevesul;
---CRIA TABELA QUE RECEBERÁ O CONTEÚDO DOS ARQUIVOS XML
CREATE TABLE custom_xml_received(
nome_arquivo VARCHAR2(200),
caminho_arquivo VARCHAR2(4000),
conteudo_arquivo SYS.XMLTYPE,
data_recepcao DATE DEFAULT SYSDATE);
---GRANT GRAVAÇÃO A TABELA AOS USUÁRIOS (USUÁRIO SYS)
GRANT ALL ON custom_xml_received TO mlxlevesul;
---GRANT ACESSO A PACK DBMS_BACKUP_RESTORE (USUÁRIO SYS)
GRANT ALL ON DBMS_BACKUP_RESTORE TO mlxlevesul;
---GRANT LEITURA A VIEW XKRBMSFT
GRANT SELECT ON XKRBMSFT TO mlxlevesul;
---CRIA PROCEDURE PARA SELECIONAR TODOS OS ARQUIVOS DO DIRETÓRIO RECEIVE
CREATE OR REPLACE PROCEDURE custom_list_received_archives
(directory IN VARCHAR2,
v_error_code_arm OUT NUMBER)
IS
PROCNAME CONSTANT VARCHAR2(100) := 'custom_list_received_archives';
ns VARCHAR2(1024);
v_directory VARCHAR2(1024);
v_error_code NUMBER;
v_error_text VARCHAR2(300);
CURSOR c_archive_names IS
SELECT fname_archive, fname_krbmsft
FROM sys.xkrbmsft;
r_archive_names c_archive_names%ROWTYPE;
BEGIN
OPEN c_archive_names;
v_directory := directory;
sys.dbms_backup_restore.searchfiles(v_directory, ns);
v_error_code := 0;
v_error_text := '';
FOR each_file IN (SELECT fname_krbmsft AS name FROM sys.xkrbmsft) LOOP
EXIT WHEN c_archive_names%NOTFOUND;
FETCH c_archive_names INTO r_archive_names;
custom_armazenar_xml(v_file => r_archive_names.fname_archive,
v_caminho => r_archive_names.fname_krbmsft,
v_error_code_out => v_error_code_arm);
IF v_error_code_arm = 0 /*AND r_archive_names.fname_archive IN (SELECT nome_arquivo FROM custom_xml_received)*/ THEN
UTL_FILE.FCOPY(SRC_LOCATION => 'D_RECEIVED_FILES',
SRC_FILENAME => R_ARCHIVE_NAMES.FNAME_ARCHIVE,
DEST_LOCATION => 'D_BKP_RECEIVED_FILES',
DEST_FILENAME => R_ARCHIVE_NAMES.FNAME_ARCHIVE);
UTL_FILE.FREMOVE(LOCATION => 'D_RECEIVED_FILES',
FILENAME => R_ARCHIVE_NAMES.FNAME_ARCHIVE);
END IF;
END LOOP;
CLOSE c_archive_names;
EXCEPTION
WHEN OTHERS THEN
v_error_code := SQLCODE;
v_error_text := SUBSTR(SQLERRM, 1, 300);
END;
---CRIA PROCEDURE PARA INSERÇÃO DO CONTEÚDO DOS ARQUIVOS EM TABELA
CREATE OR REPLACE PROCEDURE custom_armazenar_xml (v_file IN VARCHAR2,
v_caminho IN VARCHAR2,
v_error_code_out OUT NUMBER) AS
v_error_code NUMBER := 0;
v_error_text VARCHAR2(300) := '';
BEGIN
v_error_code_out := 0;
INSERT INTO custom_xml_received
(nome_arquivo, caminho_arquivo, conteudo_arquivo)
VALUES
(v_file, v_caminho, xmltype(bfilename('D_RECEIVED_FILES', v_file),nls_charset_id('AL32UTF8')));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_error_code := SQLCODE;
v_error_text := SUBSTR(SQLERRM, 1, 300);
v_error_code_out := v_error_code;
END custom_armazenar_xml;
--ETAPA LEITURA DAS TAGS
Tentei fazer com EXTRACT() -- Sem sucesso
Selecionar tudo
SELECT extract(xt.conteudo_arquivo, '/AGENDA/CONTATO/ID').getnumberval() ID,
extract(xt.conteudo_arquivo, '/AGENDA/CONTATO/NOME').getstringval() NOME,
extract(xt.conteudo_arquivo, '/AGENDA/CONTATO/SOBRENOME').getstringval() SOBRENOME,
extract(xt.conteudo_arquivo, '/AGENDA/CONTATO/EMAIL').getstringval() EMAIL,
extract(xt.conteudo_arquivo, '/AGENDA/CONTATO/TELEFONE').getstringval() TELEFONE
FROM custom_xml_received XT/*,
TABLE(XMLSequence(Extract(XT.conteudo_arquivo , '/AGENDA'))) AGENDA, -- Master
TABLE(XMLSequence(Extract(VALUE(AGENDA) , '/AGENDA/CONTATO'))) CONTATO */ -- Detalhes
WHERE XT.nome_arquivo = 'contato001.XML'
ExtractValue() -- Sem sucesso
Selecionar tudo
SELECT extractvalue(VALUE(AGENDA), '/AGENDA/ID') ID,
extractvalue(VALUE(AGENDA), '/AGENDA/NOME') NOME,
extractvalue(VALUE(AGENDA), '/AGENDA/SOBRENOME') SOBRENOME
FROM custom_xml_received XT,
TABLE(XMLSequence(Extract(XT.conteudo_arquivo , '/AGENDA'))) AGENDA -- Detalhes
WHERE XT.nome_arquivo = 'contato001.XML'
O mais próximo que cheguei até agora:
O resultado vem vazio (null)
Selecionar tudo
SELECT extractvalue(xt.conteudo_arquivo, '/AGENDA/CONTATO/ID') ID,
extractvalue(xt.conteudo_arquivo, '/AGENDA/CONTATO/NOME') NOME,
extractvalue(xt.conteudo_arquivo, '/AGENDA/CONTATO/SOBRENOME') SOBRENOME,
extractvalue(xt.conteudo_arquivo, '/AGENDA/CONTATO/EMAIL') EMAIL,
extractvalue(xt.conteudo_arquivo, '/AGENDA/CONTATO/TELEFONE') TELEFONE
FROM custom_xml_received XT/*,
TABLE(XMLSequence(Extract(XT.conteudo_arquivo , '/AGENDA'))) AGENDA, -- Master
TABLE(XMLSequence(Extract(VALUE(AGENDA) , '/AGENDA/CONTATO'))) CONTATO */ -- Detalhes
WHERE XT.nome_arquivo = 'contato001.XML'
Segue também exemplo do arquivo XML
Selecionar tudo
<?xml version="1.0"?>
<agenda>
<contato id="1">
<nome>Rodrigo</nome>
<sobrenome>Almeida</sobrenome>
<email>contato@rodrigoalmeida.net</email>
<telefone tipo="res">11 9999 9999</telefone>
<telefone tipo="com">11 8888 8888</telefone>
</contato>
</agenda>
Podem me ajudar com este problema?
Att,
