Leitura de arquivos XML

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
alcsjorge
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 3
Registrado em: Seg, 11 Ago 2008 1:33 pm
Localização: Rio de Janeiro

Boa tarde!

Li diversos tópicos sobre manipulação de arquivos XML, mas estou com problemas para o passo inicial, ler o arquivo do servidor e inserir na tabela.

Segui os seguintes passos:

1 - Criei a tabela XMLTable (doc_id number, xml_data XMLType);

2 - Criei o xml abaixo no direitorio /tmp/SMS do meu servidor:

Selecionar tudo

<FAQ-LIST> 
              <QUESTION> 
                   <QUERY>Question 1</QUERY> 
                   <RESPONSE>Answer goes here.</RESPONSE> 
              </QUESTION> 
           </FAQ-LIST>
3 - Tentei executar o insert do xml de diversas formas, e nenhuma funcionou

Selecionar tudo

     - insert into XMLTable values (1,XMLType(bfilename('XMLDIR', '1.xml')));
     -   insert into XMLTable values (1,extract(bfilename('XMLDIR', '1.xml'),'/tmp/SMS/ana.xml/FAQ-LIST/QUESTION/RESPONSE'));
Se eu executar o seguinte insert, funciona normalmente:

Selecionar tudo

 insert into XMLTable values (1, 
           XMLType('<FAQ-LIST> 
              <QUESTION> 
                   <QUERY>Question 1</QUERY> 
                   <RESPONSE>Answer goes here.</RESPONSE> 
              </QUESTION> 
           </FAQ-LIST>'));
Mas o problema é que preciso ler diversos arquivos xml de um diretorio.

alguém pode ajudar?

Obrigada
rafaelfrocha
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 45
Registrado em: Qua, 31 Out 2007 9:30 am
Localização: Uberlândia
Rafael Rocha

Olá!

´Para carregar os XML para o Oracle, sugiro fazer o seguinte:

1°) Criar um diretório no Oracle apontando para onde estão seus arquivos no sistema operacional.

Ex:

Selecionar tudo

create or replace directory NOME_DIRETORIO as "Caminho_Diretorio_Servidor"
2°) Para carregar o XML, sugiro fazer um procedimento para realizar esta função. você pode usar algo como carregar um arquivo para uma variável CLOB:

Selecionar tudo

x_Arquivo  := bfilename('NOME_DIRETORIO', 'NOME_ARQUIVO');
DBMS_LOB.fileOpen(x_Arquivo, dbms_lob.file_readonly);
DBMS_LOB.createtemporary(x_conteudo, TRUE, DBMS_LOB.session);
DBMS_LOB.loadFromFile(x_conteudo,x_Arquivo,DBMS_LOB.getLength(x_Arquivo),1,1);
DBMS_LOB.fileClose(x_Arquivo);

// onde x_arquivo variável bfile e x_conteudo variavel clob
3°) Você pode converter o CLOB (x_conteudo) em XMLType.
Ex:

Selecionar tudo

xXML:= XMLType.createxml(x_conteudo)
Em seguida você faz o insert do XML na sua tabela!

Espero ter ajudado!
williankleber
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 25
Registrado em: Qua, 02 Abr 2008 2:40 pm
Localização: Joinville - SC
Contato:

Passei por esse problema recentemente, rodei muito até achar uma saida.


Primeiro crie o diretório no ORACLE apontando para a pasta "/tmp/SMS":

Selecionar tudo

create or replace directory XMLDIR as "/tmp/SMS";
Para colocar o arquivo dentro da tabela use o seguinte select (Usando a extrutura de tablea exemplificada acima):

Selecionar tudo

insert into XMLTable (doc_id,xml_data) values (1,xmltype(bfilename('XMLDIR','ana.xml'),nls_charset_id('AL32UTF8'));
Esse select seleciona os dados do exemplo de XML.

Selecionar tudo

SELECT extractvalue(VALUE(FAQ),
                    '/QUESTION/QUERY') QUERY,
       extractvalue(VALUE(FAQ),
                    '/QUESTION/RESPONSE') RESPONSE
  FROM XMLTable XT,
       TABLE(XMLSequence(Extract(XT.xml_data , '/FAQ-LIST/QUESTION'))) FAQ
 WHERE XT.doc_id = 1

Imaginando que nessa situação poderam ocorrer N nós "<QUESTION>" então o segundo select deverá ser usado!

Selecionar tudo

 SELECT extractvalue(VALUE(QUE),
                    '/QUESTION/QUERY') QUERY,
       extractvalue(VALUE(QUE),
                    '/QUESTION/RESPONSE') RESPONSE
  FROM XMLTable XT,
       TABLE(XMLSequence(Extract(XT.xml_data , '/FAQ-LIST')))  FAQ,           -- Master
       TABLE(XMLSequence(Extract(FAQ , '/FAQ-LIST/QUESTION'))) QUE            -- Detalhes
 WHERE XT.doc_id = 1

Digamos que dentro do nó "<FAQ-LIST>" exista mais informações além do QUESTION:

Selecionar tudo

            <FAQ-LIST>
              <FAQ-ID>01</FAQ-ID>
              <QUESTION>
                   <QUERY>Question 1</QUERY>
                   <RESPONSE>Answer goes here.</RESPONSE>
              </QUESTION>
              <QUESTION>
                   <QUERY>Question 2</QUERY>
                   <RESPONSE>Answer goes here.</RESPONSE>
              </QUESTION>
              <QUESTION>
                   <QUERY>Question 3</QUERY>
                   <RESPONSE>Answer goes here.</RESPONSE>
              </QUESTION>
           </FAQ-LIST>
seria esse select:

Selecionar tudo

 SELECT extractvalue(VALUE(FAQ),
                    '/FAQ-LIST/FAQ-ID') FAQ-ID,
        extractvalue(VALUE(QUE),
                    '/QUESTION/QUERY') QUERY,
        extractvalue(VALUE(QUE),
                    '/QUESTION/RESPONSE') RESPONSE
  FROM XMLTable XT,
       TABLE(XMLSequence(Extract(XT.xml_data , '/FAQ-LIST'))) FAQ,            -- Master
       TABLE(XMLSequence(Extract(FAQ , '/FAQ-LIST/QUESTION'))) QUE            -- Detalhes
 WHERE XT.doc_id = 1
Bom espero ter ajudado. Creio que o Rafael a essa altura já tenha resolvido seu problema, mas resolvi responder sua pergunta devido a falta de informações sobre o assunto em português![/code]
Nelson
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 21
Registrado em: Ter, 09 Jun 2009 9:44 am
Localização: São Paulo

Senhores, peço ajuda. Sou iniciante no tratamento com arquivos xml. Já li os itens acima e estou sabendo o básico. Já importei 02 arquivos de exemplo neste tópico e consegui reproduzir as queries. Tenho um arquivo xml que contém a estrutura assim:
Uma identificação de manifesto <man>, com seus conhecimentos e suas respectivas notas fiscais.
Já tentei sem sucesso retirar esses dados, através das queries, mas não consigo abstrair o código da query que retornaria :
número do manifesto, com seu(s) conhecimento(s) e suas nota(s) fiscal(is).

Podem me ajudar a construir essa query ??
P.s:Trabalho com transportadora, por isso os elementos serem Manifesto/Conhecimento e Nota. Seria análogo ao Pedido, seus clientes e seus itens.
Grato

Selecionar tudo

<?xml version="1.0" encoding="UTF-8" ?>
<man nro='16101046' versao_sw='6.0' dtEmissao='23/05/2008' xmlns='http://www.portal.fucapi.br' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://www.portal.fucapi.br http://alvaraes.suframa.gov.br:7778/PMNRecEViewController/jsp/importardados/ManifestoConhecimento.xsd' >
<traCGCMF>50553828000176</traCGCMF>
<manValorTotalNota>67254.78</manValorTotalNota>
<manQtdeNFs>2</manQtdeNFs>
<manUF_Destino>AC</manUF_Destino>
<conhecimentos>
<conhecimento nro='11000' dtEmissao='22/05/2008'>
<conValorTotalNota>3972.85</conValorTotalNota>
<conQtdeNotaFiscal>2</conQtdeNotaFiscal>
<conCFOP>6360</conCFOP>
<conValorICMS>0.00</conValorICMS>
<conValorFrete>139.43</conValorFrete>
</conhecimento>
</conhecimentos>
<notasFiscais>
<notaFiscal nro='11' dtEmissao='21/05/2008' tipo='0'>
<remCGCMF> 61801862000114</remCGCMF>
<desCGCMF> 05856457000100</desCGCMF>
<conNumero>547</conNumero>
</notaFiscal>
<notaFiscal nro='22' dtEmissao='21/05/2008' tipo='0'>
<remCGCMF> 00387541000146</remCGCMF>
<desCGCMF> 04350799000129</desCGCMF>
<conNumero>547</conNumero>
</notaFiscal>
</notasFiscais>
<conhecimentos>
<conhecimento nro='22000' dtEmissao='22/05/2008'>
<conValorTotalNota>1972.85</conValorTotalNota>
<conQtdeNotaFiscal>1</conQtdeNotaFiscal>
<conCFOP>5360</conCFOP>
<conValorICMS>0.00</conValorICMS>
<conValorFrete>39.43</conValorFrete>
</conhecimento>
</conhecimentos>
<notasFiscais>
<notaFiscal nro='33' dtEmissao='22/05/2008' tipo='0'>
<remCGCMF> 61801862000114</remCGCMF>
<desCGCMF> 05856457000100</desCGCMF>
<conNumero>548</conNumero>
</notaFiscal>
</notasFiscais>
</man>
rafaelfrocha
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 45
Registrado em: Qua, 31 Out 2007 9:30 am
Localização: Uberlândia
Rafael Rocha

Nelson,

Tome um exemplo prático. Suponha que você queira o n° do conhecimento que é um atributo da 1° tag do XML:

Selecionar tudo

select extractValue (XMLVal, '/man/@nro', 'xmlns="http://www.portal.fucapi.br"')
from XML_TAB
where id = 2

--
Resultado: 16101046
Suponha que queira informações sobre os conhecimentos:

Selecionar tudo

select 
  extractValue (VALUE(TAB), '/conhecimentos/conhecimento/conValorTotalNota', 'xmlns="http://www.portal.fucapi.br"') conValorTotalNota,
  extractValue (VALUE(TAB), '/conhecimentos/conhecimento/conQtdeNotaFiscal', 'xmlns="http://www.portal.fucapi.br"') conQtdeNotaFiscal,
  extractValue (VALUE(TAB), '/conhecimentos/conhecimento/conCFOP', 'xmlns="http://www.portal.fucapi.br"') conCFOP,
  extractValue (VALUE(TAB), '/conhecimentos/conhecimento/conValorICMS', 'xmlns="http://www.portal.fucapi.br"') conValorICMS,
  extractValue (VALUE(TAB), '/conhecimentos/conhecimento/conValorFrete', 'xmlns="http://www.portal.fucapi.br"') conValorFrete
from XML_TAB, TABle(XMLSequence(extract(XMLVal,
             '/man/conhecimentos'
             ,'xmlns="http://www.portal.fucapi.br"'))) TAB
where id = 2
/
Sugiro que você dê uma olhada no tópico:
http://glufke.net/oracle/viewtopic.php?t=4020

espero ter ajudado!
Nelson
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 21
Registrado em: Ter, 09 Jun 2009 9:44 am
Localização: São Paulo

Senhores, o exemplo da query dos dados do Conhecimento ficou SHOW !!!
Analogamente fiz para recuperar dados da nnf, vem 3 linhas (correto), só que sem informação !!!. Podem me ajudar mais uma vez ???

Selecionar tudo

select 
  extractValue (VALUE(TAB), '/notasFiscais/notaFiscal/@nro', 'xmlns="http://www.portal.fucapi.br"') nronnf, 
  extractValue (VALUE(TAB), '/notasFiscais/notaFiscal/remCGCMF', 'xmlns="http://www.portal.fucapi.br"') remetente, 
  extractValue (VALUE(TAB), '/notasFiscais/notaFiscal/desCGCMF', 'xmlns="http://www.portal.fucapi.br"') destinatario, 
from XML_TAB, TABle(XMLSequence(extract(XMLVal, 
             '/man/notasFiscais' 
             ,'xmlns="http://www.portal.fucapi.br"'))) TAB 
where id = 2
Grato
Nelson
rafaelfrocha
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 45
Registrado em: Qua, 31 Out 2007 9:30 am
Localização: Uberlândia
Rafael Rocha

Nelson,

Quando você tem tags que se repetem (como no seu caso o n° da NF), no caminho do extractValue você deve informar um caminho mais específico (/man/notasFiscais/notaFiscal). Note que a única coisa que mudou na query foi o Path das Tags:

Selecionar tudo

select 
extractValue (VALUE(TAB), '/notaFiscal/@nro', 'xmlns="http://www.portal.fucapi.br"') nronnf, 
extractValue (VALUE(TAB), '/notaFiscal/remCGCMF', 'xmlns="http://www.portal.fucapi.br"') remetente, 
extractValue (VALUE(TAB), '/notaFiscal/desCGCMF', 'xmlns="http://www.portal.fucapi.br"') destinatario
from XML_TAB, TABle(XMLSequence(extract(XMLVal, 
'/man/notasFiscais/notaFiscal' 
,'xmlns="http://www.portal.fucapi.br"'))) TAB 
where id = 2 
/
Nelson
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 21
Registrado em: Ter, 09 Jun 2009 9:44 am
Localização: São Paulo

Rafel | Rocha ... Caras, vocês são D+...... funcionou legal !!!
Valeo pelas dicas. Um super obrigado.
Nelson
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 21
Registrado em: Ter, 09 Jun 2009 9:44 am
Localização: São Paulo

Rafael | Rocha, boa tarde. Sou eu denovo. Obrigado pelo 'super apoio' dado, mas...... caras, eu não consigo 'recuperar' a informação
<infNFe Id ????
deveria vir a

Selecionar tudo

<infNFe Id="NFe35080599999090910270550010000000015180051273"
e

Selecionar tudo

<infNFe Id="NFe22222222222222222222222222222222222222222222"
Por favor, onde estou errando ??

a query q estou usando é:

Selecionar tudo

select  extractValue (XMLVal,'/infNFe/@Id', 'xmlns="http://www.portalfiscal.inf.br/nfe"') NroNNF 
from XML_TAB, 
TABle(XMLSequence(extract(XMLVal,'/NFe/infNFe','xmlns="http://www.portalfiscal.inf.br/nfe"'))) NNF
o arquivo XML tem esse formato:

Selecionar tudo

<?xml version="1.0" encoding="utf-8" ?> 
- <NFe xmlns="http://www.portalfiscal.inf.br/nfe">
- <infNFe Id="NFe35080599999090910270550010000000015180051273" versao="1.10">
+ <ide>
+ <emit>
+ <dest>
+ <retirada>
+ <entrega>
+ <det nItem="1">
+ <det nItem="2">
+ <total>
+ <transp>
+ <infAdic>
  <infAdFisco>Nota Fiscal de exemplo NF-eletronica.com</infAdFisco> 
  </infAdic>
  </infNFe>
- <infNFe Id="NFe22222222222222222222222222222222222222222222" versao="1.10">
+ <ide>
+ <emit>
+ <dest>
+ <retirada>
+ <entrega>
+ <det nItem="1">
+ <det nItem="2">
+ <total>
+ <transp>
+ <infAdic>
  <infAdFisco>Nota Fiscal de exemplo NF-eletronica.com</infAdFisco> 
  </infAdic>
  </infNFe>
  </NFe>
rafaelfrocha
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 45
Registrado em: Qua, 31 Out 2007 9:30 am
Localização: Uberlândia
Rafael Rocha

Nelson,

Aparentemente o PATH (caminho) que você utilizou para extrair a informação está incorreto!

Tente assim:

Selecionar tudo

Select extractValue (XMLVal,'/NFe/infNFe/@Id', 'xmlns="http://www.portalfiscal.inf.br/nfe"') NroNNF 
from XML_TAB, 
TABle(XMLSequence(extract(XMLVal,'/NFe','xmlns="http://www.portalfiscal.inf.br/nfe"'))) NNF 
Note que utilizei somente '/NFe' no path...
Nelson
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 21
Registrado em: Ter, 09 Jun 2009 9:44 am
Localização: São Paulo

testei e deu ....

Selecionar tudo

ORA-19025: EXTRACTVALUE returns value of only one node
alexrsilva
Rank: Analista Sênior
Rank: Analista Sênior
Mensagens: 153
Registrado em: Ter, 27 Mai 2008 1:31 pm
Localização: Rio de Janeiro - RJ

Nelson.
Eu fiz diferente.
Eu criei uma procedure para pegar o arquivo e transformá-lo em um xmltype

Selecionar tudo

parser := xmlparser.newParser;

  -- Parse the XML document found in the file
  xmlparser.parse (parser, dir || '/' || file);

  -- Retrieve a navigable document tree

  retorno := xmlparser.getDocument (parser);
depois eu criei mais uma procedure para buscar as informações que queria

Selecionar tudo

nodes := xmldom.getElementsByTagName (doc, '*'); -- o asterisco pode ser substituido pela tag que quer pesquisar.
depois fiz uma varredura para pegar um array desses nodes

Selecionar tudo

FOR node_index IN 0 .. xmldom.getLength (nodes) - 1  -- me traz os elements
     LOOP
dentro desse for busco os attibutos desse array

Selecionar tudo

node_map := xmldom.getAttributes (one_node);  -- me traz os attributes
  
       FOR attr_index IN
          0 .. xmldom.getLength (node_map) - 1
       LOOP
Espero ter ajudado.

Alex Silva
rafaelfrocha
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 45
Registrado em: Qua, 31 Out 2007 9:30 am
Localização: Uberlândia
Rafael Rocha

Nelson,

Na verdade você estava utilizando extractValue(nome_da_coluna), em vez de utilizar extractValue(value(NNF)). Faltou só um pouco de atenção! Testei aqui e deu certo!

ex:

Selecionar tudo

Select extractValue (value(NNF),'/infNFe/@Id', 'xmlns="http://www.portalfiscal.inf.br/nfe"') NroNNF 
from XML_TAB, 
TABle(XMLSequence(extract(XMLVal,'/NFe/infNFe','xmlns="http://www.portalfiscal.inf.br/nfe"'))) NNF 
Where id = 5
Nelson
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 21
Registrado em: Ter, 09 Jun 2009 9:44 am
Localização: São Paulo

PessoALL, testei e foi OK. Valeo pelo apoio e as ótimas respostas, todas bem fundamentadas e com exemplos muito valiosos. O tratamento de arquivos XML pelo Oracle é muito poderoso. Há várias formas de incluir e manusear os XML. Já percebi que vou ter que 'estudar' mais sobre essa tecnologia. Tecnologia esta, que está 'crescendo' em virtude também, de projetos como a NFe e CTe.
Gente, mais uma vez, muito obrigado.
Nelson
Nelson
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 21
Registrado em: Ter, 09 Jun 2009 9:44 am
Localização: São Paulo

Rafael | Rocha, boa tarde . me desculpe mais uma vez, mas só tenho vocês.
Já estou tentando há algum tempo e não obtive exito.
.
No exemplo que mandei anteriormente(da NFe) deu certo, consegui ler o id da nfe(existem 2 registros de nfe).
estou tentando ler /infNFe Id e o /det nItem (por exemplo) e não consigo.
Os registros vem duplicados. Ou seja eu quero ler o Pai (/infNFe) e os filhos (no caso do exemplo, o /det nItem).

Como faço isso ???


Grato
Nelson
Nelson
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 21
Registrado em: Ter, 09 Jun 2009 9:44 am
Localização: São Paulo

Query de exemplo:

Selecionar tudo

select  
extractValue (VALUE(NNF),'/infNFe/@Id', 'xmlns="http://www.portalfiscal.inf.br/nfe"') NroNNF, 
extractValue (VALUE(EMI), '/vol/pesoB', 'xmlns="http://www.portalfiscal.inf.br/nfe"') peso
from info_xml, 
TABle(XMLSequence(extract(conteudo,'/NFe/infNFe','xmlns="http://www.portalfiscal.inf.br/nfe"'))) NNF,
TABle(XMLSequence(extract(conteudo,'/NFe/infNFe/transp/vol','xmlns="http://www.portalfiscal.inf.br/nfe"'))) EMI
where id = 7

Imagem
Nelson
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 21
Registrado em: Ter, 09 Jun 2009 9:44 am
Localização: São Paulo

o peso ocorre uma vez para cada nota
rafaelfrocha
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 45
Registrado em: Qua, 31 Out 2007 9:30 am
Localização: Uberlândia
Rafael Rocha

Aparentemente é problema de ligação...

você não estabeleceu nenhuma ligação entre info_xml, NNF, e EMI..
Nelson
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 21
Registrado em: Ter, 09 Jun 2009 9:44 am
Localização: São Paulo

na verdade não sei como fazer essa 'ligação' !!!!!!!!
Nelson
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 21
Registrado em: Ter, 09 Jun 2009 9:44 am
Localização: São Paulo

Rafael | Rocha,
Eu postei a query com problema no
http://www.glufke.net/oracle/viewtopic.php?t=4020
Grato
nelson
Nelson
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 21
Registrado em: Ter, 09 Jun 2009 9:44 am
Localização: São Paulo

Srs, boa tarde
Pesquisei , pesquisei e não encontrei.
A dúvida com relação ao diretório para importação de arquivo XML persiste.

Podem me ajudar ??

A procedure LOAD_XML está criada assim:

Selecionar tudo

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;
/
No programa(forms) eu executo:

Selecionar tudo

LOAD_XML (p_id=>7, p_filename => 'xml_teste07.xml'); -- nfe da sefaz
O XMLDIR foi criado assim:

Selecionar tudo

create or replace directory XMLDIR as '/tmp/sms';
A tabela foi criada assim:

Selecionar tudo

create table INFO_XML (id number primary key, data_atualizacao date default sysdate,  conteudo  XMLType);
Importa legal.

Só que eu tenho que copiar o arquivo pro diretório do servidor Oracle.

Preciso ler o arquivo XML do diretório 'c:\temp\' (por exemplo), ou outro diretório que NÃO O DO SERVIDOR ORACLE.

vocês, podem me ajudar ???
Grato
Nelson
leandromiranda87
Rank: Programador Pleno
Rank: Programador Pleno
Mensagens: 43
Registrado em: Ter, 20 Mar 2012 11:37 am
Localização: Campinas
Leandro L. Miranda

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,
gfkauer
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 389
Registrado em: Ter, 27 Jul 2010 1:34 pm
Localização: Sapiranga - RS
Contato:
Quem falou que programar era fácil??

Quanto mais dificil for a implementação mais valorizado seu trabalho será!

Acessem: www.gfkauer.com.br

Saudações a todos.

Ando estudando a leitura de arquivos XML. Agora me surgiram algumas duvidas.
Como eu faria para ler um arquivo que desconheço o layout?

Na verdade eu conheço o layout. Mas são 17 possiveis layouts e eu quero tratar tudo em uma unica leitura...
e_muniz
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Qua, 01 Jul 2009 10:35 am
Localização: Belo Horizonte / MG

Também tenho dificuldades com vários layouts de XMLs e precisaria de algo semelhante a um "select * from tabela" isso me retornaria todos os campos do xml e assim me permitiria seguir com a validação do xml.

Obrigado
shinobyghost
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 5
Registrado em: Qui, 18 Jun 2015 10:10 am
Localização: Bauru/SP

Boa tarde, eu sou novo em plsql e xml, gostaria da ajuda de todos para solucionar meu problema.
Eu tenho o Oracle 11gr2 instalado em um Windows Server 2008.

Preciso configurar o servidor e criar uma procedure que leia o conteúdo do xml e insira as informações nas tabelas correspondentes.

As tabelas são as seguintes:

Selecionar tudo

CREATE TABLE ALUNO(
	ALUNO_ID NUMBER(6,0) PRIMARY KEY,
	FIRST_NAME VARCHAR2(150),
	LAST_NAME VARCHAR2(150),
	EMAIL VARCHAR2(150),
	PHONE_NUMBER VARCHAR2(150),
	CPF NUMBER(11,0)
);

---------------------------------------------------

CREATE TABLE CURSO(
	CURSO_ID NUMBER(6,0) PRIMARY KEY,
	ALUNO_ID NUMBER(6,0),
	MATERIA_ID NUMBER(6,0),
	NOTA NUMBER(4,2),
	DATAHORA DATE
);
 
Eu tenho um xml assim:

Selecionar tudo

<?xml version="1.0" encoding="windows 1252" ?> 
  <listaalunos>
  <aluno>
  <id>1001</id> 
  <firstname>HUGO</firstname> 
  <lastname>GELADEIRA</lastname> 
  <email>hugo@hotmail.com</email>
  <phone>988665522</phone>
  <cpf>22722722764</cpf> 
  <curso_id>101</curso_id> 
  <idmateria>13</idmateria> 
  <nota>8,75</nota>
  <data>14/07/2015</data>
  </aluno>
  <aluno>
  <firstname>RODRIGO</firstname> 
  <lastname>TESTE</lastname> 
  <email>rodrigo@hotmail.com</email>
  <phone>988772211</phone>
  <cpf>25825825897</cpf> 
  <curso_id>102</curso_id> 
  <idmateria>12</idmateria> 
  <nota>9,75</nota>
  <data>14/07/2015</data>
  </aluno>
  </listaalunos>
Eu tenho que inserir esse xml nas duas tabelas acima.
Como que eu poderia fazer isso usando UTL_FILE, ExtractValue...
Já testei conforme algumas respostas deste posto, mas deram erro...
Muito obrigado pela atenção de todos...
Caio549
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 1
Registrado em: Sex, 18 Set 2015 9:12 pm

Pessoal boa noite, por favor estou com dificuldades para extrair uma informação do xml, pois ele tem tags repetidas

Li os topicos acima, porém não consegui fazer funcionar.

Exemplo:

o xml abaixo esta na minha tabela "tmp_xml" e coluna "xml"

Selecionar tudo

<NFe xmlns="http://www.portalfiscal.inf.br/nfe">
  <infNFe Id="NFe35080599999090910270550010000000015180051273" versao="1.10">
    <ide>
      <cUF>35</cUF>
      <cNF>518005127</cNF>
      <natOp>Venda a vista</natOp>
      <indPag>0</indPag>
      <mod>55</mod>
      <serie>1</serie>
      <nNF>1</nNF>
      <dEmi>2008-05-06</dEmi>
      <dSaiEnt>2008-05-06</dSaiEnt>
      <tpNF>0</tpNF>
      <cMunFG>3550308</cMunFG>
      <tpImp>1</tpImp>
      <tpEmis>1</tpEmis>
      <cDV>3</cDV>
      <tpAmb>2</tpAmb>
      <finNFe>1</finNFe>
      <procEmi>0</procEmi>
      <verProc>NF-eletronica.com</verProc>
    </ide>
    <emit>
      <CNPJ>99999090910270</CNPJ>
      <xNome>NF-e Associacao NF-e</xNome>
      <xFant>NF-e</xFant>
      <enderEmit>
        <xLgr>Rua Central</xLgr>
        <nro>100</nro>
        <xCpl>Fundos</xCpl>
        <xBairro>Distrito Industrial</xBairro>
        <cMun>3502200</cMun>
        <xMun>Angatuba</xMun>
        <UF>SP</UF>
        <CEP>17100171</CEP>
        <cPais>1058</cPais>
        <xPais>Brasil</xPais>
        <fone>1733021717</fone>
      </enderEmit>
      <IE>123456789012</IE>
    </emit>
    <dest>
      <CNPJ>00000000000191</CNPJ>
      <xNome>DISTRIBUIDORA DE AGUAS MINERAIS</xNome>
      <enderDest>
        <xLgr>AV DAS FONTES</xLgr>
        <nro>1777</nro>
        <xCpl>10 ANDAR</xCpl>
        <xBairro>PARQUE FONTES</xBairro>
        <cMun>5030801</cMun>
        <xMun>são Paulo</xMun>
        <UF>SP</UF>
        <CEP>13950000</CEP>
        <cPais>1058</cPais>
        <xPais>BRASIL</xPais>
        <fone>1932011234</fone>
      </enderDest>
      <IE> </IE>
    </dest>
    <retirada>
      <CNPJ>99171171000194</CNPJ>
      <xLgr>AV PAULISTA</xLgr>
      <nro>12345</nro>
      <xCpl>TERREO</xCpl>
      <xBairro>CERQUEIRA CESAR</xBairro>
      <cMun>3550308</cMun>
      <xMun>são PAULO</xMun>
      <UF>SP</UF>
    </retirada>
    <entrega>
      <CNPJ>99299299000194</CNPJ>
      <xLgr>AV FARIA LIMA</xLgr>
      <nro>1500</nro>
      <xCpl>15 ANDAR</xCpl>
      <xBairro>PINHEIROS</xBairro>
      <cMun>3550308</cMun>
      <xMun>são PAULO</xMun>
      <UF>SP</UF>
    </entrega>
    <det nItem="1">
      <prod>
        <cProd>00001</cProd>
        <cEAN/>
        <xProd>Agua Mineral</xProd>
        <CFOP>5101</CFOP>
        <uCom>dz</uCom>
        <qCom>1000000.0000</qCom>
        <vUnCom>1</vUnCom>
        <vProd>10000000.00</vProd>
        <cEANTrib/>
        <uTrib>und</uTrib>
        <qTrib>12000000.0000</qTrib>
        <vUnTrib>1</vUnTrib>
      </prod>
      <imposto>
        <ICMS>
          <ICMS00>
            <orig>0</orig>
            <CST>00</CST>
            <modBC>0</modBC>
            <vBC>10000000.00</vBC>
            <pICMS>18.00</pICMS>
            <vICMS>1800000.00</vICMS>
          </ICMS00>
        </ICMS>
        <PIS>
          <PISAliq>
            <CST>01</CST>
            <vBC>10000000.00</vBC>
            <pPIS>0.65</pPIS>
            <vPIS>65000</vPIS>
          </PISAliq>
        </PIS>
        <COFINS>
          <COFINSAliq>
            <CST>01</CST>
            <vBC>10000000.00</vBC>
            <pCOFINS>2.00</pCOFINS>
            <vCOFINS>200000.00</vCOFINS>
          </COFINSAliq>
        </COFINS>
      </imposto>
    </det>
    <det nItem="2">
      <prod>
        <cProd>00002</cProd>
        <cEAN/>
        <xProd>Agua Mineral</xProd>
        <CFOP>5101</CFOP>
        <uCom>pack</uCom>
        <qCom>5000000.0000</qCom>
        <vUnCom>2</vUnCom>
        <vProd>10000000.00</vProd>
        <cEANTrib/>
        <uTrib>und</uTrib>
        <qTrib>3000000.0000</qTrib>
        <vUnTrib>0.3333</vUnTrib>
      </prod>
      <imposto>
        <ICMS>
          <ICMS00>
            <orig>0</orig>
            <CST>00</CST>
            <modBC>0</modBC>
            <vBC>10000000.00</vBC>
            <pICMS>18.00</pICMS>
            <vICMS>1800000.00</vICMS>
          </ICMS00>
        </ICMS>
        <PIS>
          <PISAliq>
            <CST>01</CST>
            <vBC>10000000.00</vBC>
            <pPIS>0.65</pPIS>
            <vPIS>65000</vPIS>
          </PISAliq>
        </PIS>
        <COFINS>
          <COFINSAliq>
            <CST>01</CST>
            <vBC>10000000.00</vBC>
            <pCOFINS>2.00</pCOFINS>
            <vCOFINS>200000.00</vCOFINS>
          </COFINSAliq>
        </COFINS>
      </imposto>
    </det>


Preciso extrair as informações dos itens que estao a partir da tag <det nItem="1">

Comando que estou utilizando:

Selecionar tudo

 SELECT EXTRACTVALUE(xml,
                    '//det/@nItem',
                    'xmlns="http://www.portalfiscal.inf.br/nfe"') as num
  FROM TMP_XML a;

O oracle me dá um erro ORA-19025(RETORNA VALOR DE APENAS UM NÓ)

alguém sabe, como consigo extrair essa informação?
saziba
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 3
Registrado em: Dom, 22 Fev 2015 11:04 am

Caio,

A resposta simples para a sua pergunta é a seguinte:

Selecionar tudo

SELECT EXTRACTVALUE(xml,
'//det[1]/@nItem',
'xmlns="http://www.portalfiscal.inf.br/nfe"') as num
FROM TMP_XML a;
Mas eu aconselho a não utilizar mais a função EXTRACTVALUE, pois a Oracle descontinuou essa funcionalidade no 11g release 2, mantendo-a somente para retrocompatibilização. Vide o link:
http://docs.oracle.com/cd/E11882_01/ser ... SQLRF06173

A abordagem que eu recomendo para o seu caso é utilizando a sintaxe XMLTABLE, a partir do seguinte exemlo:

Selecionar tudo

SELECT x.*
FROM   tmp_xml t,
       XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.portalfiscal.inf.br/nfe'),
                'NFe/infNFe/det' PASSING t.xml
                COLUMNS cProd VARCHAR2(4000) PATH 'prod/cProd'
                       ,nItem VARCHAR2(4000) PATH '@nItem') x;
Recomendo bastante que você se familiarize-se com essa sintaxe. A partir do 10g já se pode usá-la e se for no 11g2, então é melhor ainda. Não crie um código legado novinho em folha.

PS.
Tive que fechar umas tags ao final do seu XML para poder testar...
saziba
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 3
Registrado em: Dom, 22 Fev 2015 11:04 am

shinobyghost escreveu:Boa tarde, eu sou novo em plsql e xml, gostaria da ajuda de todos para solucionar meu problema.
Eu tenho o Oracle 11gr2 instalado em um Windows Server 2008.

Preciso configurar o servidor e criar uma procedure que leia o conteúdo do xml e insira as informações nas tabelas correspondentes.

As tabelas são as seguintes:

Selecionar tudo

CREATE TABLE ALUNO(
	ALUNO_ID NUMBER(6,0) PRIMARY KEY,
	FIRST_NAME VARCHAR2(150),
	LAST_NAME VARCHAR2(150),
	EMAIL VARCHAR2(150),
	PHONE_NUMBER VARCHAR2(150),
	CPF NUMBER(11,0)
);

---------------------------------------------------

CREATE TABLE CURSO(
	CURSO_ID NUMBER(6,0) PRIMARY KEY,
	ALUNO_ID NUMBER(6,0),
	MATERIA_ID NUMBER(6,0),
	NOTA NUMBER(4,2),
	DATAHORA DATE
);
 
Eu tenho um xml assim:

Selecionar tudo

<?xml version="1.0" encoding="windows 1252" ?> 
  <listaalunos>
  <aluno>
  <id>1001</id> 
  <firstname>HUGO</firstname> 
  <lastname>GELADEIRA</lastname> 
  <email>hugo@hotmail.com</email>
  <phone>988665522</phone>
  <cpf>22722722764</cpf> 
  <curso_id>101</curso_id> 
  <idmateria>13</idmateria> 
  <nota>8,75</nota>
  <data>14/07/2015</data>
  </aluno>
  <aluno>
  <firstname>RODRIGO</firstname> 
  <lastname>TESTE</lastname> 
  <email>rodrigo@hotmail.com</email>
  <phone>988772211</phone>
  <cpf>25825825897</cpf> 
  <curso_id>102</curso_id> 
  <idmateria>12</idmateria> 
  <nota>9,75</nota>
  <data>14/07/2015</data>
  </aluno>
  </listaalunos>
Eu tenho que inserir esse xml nas duas tabelas acima.
Como que eu poderia fazer isso usando UTL_FILE, ExtractValue...
Já testei conforme algumas respostas deste posto, mas deram erro...
Muito obrigado pela atenção de todos...
Veja se ajuda...

Selecionar tudo

INSERT ALL
  WHEN 1 = 1 THEN
    INTO aluno (aluno_id,first_name,last_name,email,phone_number,cpf) 
      VALUES(id, firstname, lastname, email, phone, cpf)
  WHEN 1 = 1 THEN
    INTO CURSO (curso_id, aluno_id, materia_id, nota, datahora) 
      VALUES(curso_id, ID, idmateria, nota, datahora)
SELECT to_number(x.id) ID
      ,firstname 
      ,lastname 
      ,email    
      ,phone    
      ,to_number(cpf) cpf
      ,to_number(curso_id) curso_id
      ,to_number(idmateria) idmateria
      ,to_number(nota) nota
      ,TO_DATE(DATA,'dd/mm/yyyy') DATAhora
FROM   tmp_xml t
      ,xmltable('listaalunos/aluno' passing t.xml COLUMNS
                id VARCHAR2(4000) path 'id'
               ,firstname VARCHAR2(4000) path 'firstname'
               ,lastname  VARCHAR2(4000) path 'lastname'
               ,email     VARCHAR2(4000) path 'email'
               ,phone     VARCHAR2(4000) path 'phone'
               ,cpf       VARCHAR2(4000) path 'cpf'
               ,curso_id  VARCHAR2(4000) path 'curso_id'
               ,idmateria VARCHAR2(4000) path 'idmateria'
               ,nota      VARCHAR2(4000) path 'nota'
               ,data      VARCHAR2(4000) path 'data') x;
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 4 visitantes