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

Resultado da tabela INFO_XML (formatado em 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

Anúncios

36 comentários em “Exemplo de uso do componente XMLDB para a carga de arquivos XML para tabelas Oracle

  1. Bom dia,

    Estou utilizando para teste o oracle xe, tudo corre bem até a importação, estou recebendo os seguintes erros:

    ORA-31011: falha no parse XML
    ORA-19202: Ocorreu um erro no processamento XML
    LPX-00210: esperava ‘<‘ em vez de ‘P’
    Error at line 1
    ORA-06512: em “SYS.XMLTYPE”, line 5
    ORA-06512: em “SYSTEM.LOAD_XML”, line 15
    ORA-06512: em line 3

    Você saberia me dizer o poderia estar causando isso?

    • Olá Eduardo. O erro: ORA-31011: falha no parse XML, refere-se a um problema de “parsing” (interpretação) de seu arquivo XML. Abra-o
      com algum editor XML para encontrar o problema na estrutura e repita novamente o passo 5. Se quiser, pode postar seu arquivo XML que eu dou uma olhada. Grato por acessar meu blog.
      Eduardo Arruda

  2. Bom dia Eduardo,

    Meu arquivo xml foi um ctrl+c ctrl+v do seu exemplo (apesar de ter realizado testes com outros mais simples, sempre com o mesmo problema) mas segue como ele está (abrindo no xml marker não ha problemas):

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

    Caso eu passe todo esse conteúdo como uma string funciona normal, o insert ocorre.
    O problema está aqui “XMLTYPE.createXML(l_clob)”, algo relacionado ao arquivo mesmo, dei uma pesquisada na net a respeito dos erros mas sem sucesso, qualquer idéia fico agradecido, abraços e obrigado pela atenção…

  3. Olá Eduardo sou eu de novo,

    Acabei resolvendo o problema de uma forma mais simples, em meu procedimento agora possuo apenas o insert, veja como funcionou para mim (caso alguem chegue ate aqui e tenha o mesmo problema):

    INSERT INTO INFO_XML (
    id_nf,
    data_atualizacao,
    conteudo
    )
    VALUES (
    p_nf_id,
    sysdate,
    xmltype(bfilename(p_cam, p_arq), nls_charset_id(‘AL32UTF8’))
    );

    Escreverei um artigo em meu blog a respeito disso, e irei indicar seu site como fonte, vlw.. , t+…

  4. Eduardo, bom dia.
    Estou com uma dúvida quanto ao 2º item. Por que deve-se criar o diretório dentro do servidor ???. Eu não posso ler o arquivo XML do c:\temp por exemplo ??
    Grato
    Nelson

    • Olá Nelson. Bom dia. Neste exemplo utilizo o comando DBMS_LOB.loadfromfile para efetuar a leitura do arquivo XML, para que este comando funcione o arquivo deverá ser disponibilizado no diretório criado dentro do servidor de banco de dados.

  5. Eduardo, boa tarde.
    Fiz os procedimentos acima, porem no item 6º ao dar o select conteudo from info_xml, me retorna a mensagem :
    OCI-21560: argument 4 is null, invalid, or out of range.
    Pode me esclarecer ??
    Grato
    Nelson

    • Olá Nelson. Para que este exemplo funcione, recomendo utilizar Oracle9i ou 10g (XE), que foram os ambientes que utilizei para homologar este exemplo. Qual a versão do Oracle você utiliza ?

      • Eduardo. A versão do Oracle é 10g. O erro ocorreu pq eu estava dando o comando de select errado. Está tudo funcionando CORRETAMENTE. Muito grato pela sua explicação. Sempre que puder, farei publicidade do seu link.Muito grato.

  6. Eduardo, bom dia. Conforme seus exemplos :
    -Já consigo importar arquivos XML;
    -Já consigo extrair informações da tabela(info_xml);

    Porém, preciso que os arquivos XML sejam lidos a partir da unidade ” C:\TEMP\ ” do usuário(ou outro caminho), desde que NÃO seja no diretório do servidor.

    Pode me dar uma ajuda para que eu consiga ler de outra unidade ??
    Grato
    Nelson

    • Olá Nelson. Boa tarde.
      Ótimo ! Pelo visto você já está a um passo de completar esse desafio. Para este novo problema que você encontrou (enviar um arquivo de uma estação local), eu recomendo construir um Servlet Java. Caso queira um exemplo de upload de arquivos através de um servlet, me passe seu e-mail que eu lhe envio. Grato novamente pelo acesso.

  7. eu tinha enviado a mensagem, mas não estou vendo no blog.
    por isso estou mandando novamente
    ..
    Eduardo, mais uma ajuda por favor !!!!!!!!
    No xml abaixo, há dois registros de e seus ‘itens’.
    Estou ‘patinando’ para ler o Pai e seus Filhos.
    Ainda não ‘peguei’ a lógica para fazer essa query.
    Já consegui recuperar as , mas quando tento ler a mais o elemento /vol/pesoB a query retorna duplicado.

    A query que vem corretamente o id da nfe é:
    Select extractValue (value(IDE),’/infNFe/@Id’, ‘xmlns=”http://www.portalfiscal.inf.br/nfe”‘) NroNNF
    from info_xml,
    TABle(XMLSequence(extract(conteudo,’/NFe/infNFe’,’xmlns=”http://www.portalfiscal.inf.br/nfe”‘))) IDE
    where id = 7

    a query que vem o /vol/pesoB é:
    select extractValue (VALUE(EMI), ‘/vol/pesoB’, ‘xmlns=”http://www.portalfiscal.inf.br/nfe”‘) peso
    from info_xml,
    TABle(XMLSequence(extract(conteudo,’/NFe/infNFe/transp/vol’,’xmlns=”http://www.portalfiscal.inf.br/nfe”‘))) EMI
    where id = 7

    mas não consigo ‘juntar’ as duas informações infNFe + vol/pesoB

    Gostaria por exemplo, de recuperar
    a infNFe Id, emit/CNPJ, dest/CNPJ, det nItem e prod/xProd numa mesma query. Ou seja, vai aparecer Os Pais e Filhos. Isso é possível ???
    Se vc puder me ajudar, agradecerei muito.
    Nelson

    • Olá Nelson. Recebi, mas sobre a análise do seu arquivo XML, você vai precisar pesquisar um pouco mais. Atuo na engenharia de produto de uma empresa de software. Coordeno as atividades de minha equipe e nas horas vagas tenho procurado responder as perguntas no meu blog. Mas analisar seu problema demandaria um tempo o qual não disponho no momento pois trabalho em varios projetos em simultâneo. Grato pelos acessos.

  8. Eduardo, muito obrigado.
    Os exemplos q vc disponibilizou foram muito úteis, com os exemplos eu consegui avançar bastante. Com relação à query, tudo bem, vou continuar na ‘pesquisa’. Mais uma vez, muito obrigado.
    Abraços
    Nelson

      • Eduardo,
        Obrigado pelo exemplos disponiveis, fiz tudo conforme o solicitado.
        Porem meu amigo, estou lendo um NFe, mas quando vou cheguei aos detalhes, ou seja, na det onde fica os produtos, não conseguir retornar, pois conforme o seu exemplo acima, não consegui encaixar na minha query.

        Obrigado,

        Elves

  9. Ola Herbert, boa noite.
    O objetivo deste post foi somente abrir um “caminho das pedras” para quem possua a necessidade de trabalhar com XML dentro do banco de dados Oracle. Não cheguei a fazer testes além disso, mas recomendo que você busque mais informações para a sua necessidade em otn.oracle.com.
    Grato pelo acesso.

  10. Olá Eduardo,

    Tive o mesmo problema que seu xará, no primeiro comentário do artigo:

    ORA-31011: falha no parse XML
    ORA-19202: Ocorreu um erro no processamento XML
    LPX-00210: esperava ‘<' em vez de '?'

    Mas no meu caso, não posso inserir os dados como ele fez. Preciso que os dados sejam lidos do arquivo .XML

    Tem alguma idéia do que pode ser? Obrigado

    • Prezado Leandro.
      O objetivo deste post foi somente abrir um “caminho das pedras” para quem possua a necessidade de trabalhar com XML dentro do banco de dados Oracle. Não cheguei a fazer testes além disso, mas recomendo que você busque mais informações para a sua necessidade em otn.oracle.com. Recomendo também que veja a solução de http://programero.blogspot.com/ para o mesmo problema.

      Grato pelo acesso.

      • Eduardo,

        Obrigado pela atenção e pelas indicações, resolvi o problema inserindo os dados de uma forma diferente, onde passo o formato dos dados.

        insert into XMLTable (doc_id,xml_data) values (1,xmltype(bfilename(‘XMLDIR’,’ana.xml’),nls_charset_id(‘AL32UTF8’));

        É um pouco estranho, pois o padrão do oracle está no mesmo formato e além disso colocava o padrão UTF-8 no arquivo XML.

        Parabéns pelo seu trabalho.

        Abraço.

  11. Olá,
    consegui fazer todo o procedimento corretamente, já inseri o arquivo XML na tabela, porém não estou conseguindo recuperar os registros do arquivo, porque não entendo nada de XML! Meu arquivo é este:

    13259340890
    HRQQZTBQSPMKVKPRBJR IHYAFXHNLXAXFXZSLNXI
    3
    2
    AAIYYH XYTUELXKROLTTAQER
    19450720
    1
    0666909651422
    0000
    19161220

    81706464134
    KYF PYDVVPGYS
    5
    2
    VFWLYQIK BNMYDVVPGYS
    19610415
    1
    0466315235967
    0000
    19590618

    06849869817
    ORXVCHDKMXBKLRRTZRX VNACG WSLSHUSCPS
    9
    2
    AVZ VKIKOHYWVKG WSLSHUSCPS
    19150401
    1
    0470021715630
    0000
    19160425

    Como devo fazer a consulta para recuperar esses dados?
    Não consigo entender como escrever o comando, por favor me dê uma luz!

    Att,
    Renata Barreto

      • ?xml version=”1.0″ encoding=”UTF-8″?
        soap:Envelope xmlns:soap=”http://www.w3.org/2003/05/soap-envelope” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”
        soap:Body
        ConsultarCPFP2TResponse xmlns=”https://infoconv.receita.fazenda.gov.br/ws/cpf/”
        ConsultarCPFP2TResult
        PessoaPerfil2

    • Cara, Renata. Boa noite. O Passo 7 do tutorial recupera o conteudo do arquivo XML que foi inserido no banco de dados. Mas acho difícil
      você recuperar as informações que você deseja sem conhecer um pouco de XML. Você pode baixar apostilas e livros gratuitos sobre o assunto
      na internet.

      Grato pelo acesso,

      Eduardo Arruda

  12. boa tarde.. tenho usado no Oracle campo xmltype onde faço insert do arquivo .xml e com comando “extractValue” com “nó” ou sem tranquillo sem problema. O caso agora é que tenho um arquivo com uma TAG assim:

    alguém sabe como extrair ….pois informo o caminho com “beneficiario cco” e não retorna nada.

  13. Olá,

    Estou manipulando alguns dados em XML porém preciso de um valor que se encontra dentro de uma Tag. Abaixo um exemplo:

    Na tag, preciso capturar o valor “NOME_QUE_PRECISO”.

    Alguem pode me ajudar, por favor?

    • Caro Thiago. O objetivo do texto é fornecer algum “caminho das pedras” para a leitura/manipulação de arquivos XML com Oracle. Veja que o texto é antigo (2009). Recomendo que você tente fazer passo-a-passo o que diz no texto. Caso não obtenha sucesso, você pode procurar por leituras mais atuais deste tema em metalink.oracle.com.
      Grato,

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s