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
  

Mensagemem Qui, 07 Mai 2009 4:17 pm

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:
Código: Selecionar todos
<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
Código: Selecionar todos
     - 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:
Código: Selecionar todos
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
alcsjorge
Localização: Rio de Janeiro

Mensagemem Seg, 11 Mai 2009 5:17 pm

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:
Código: Selecionar todos
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:

Código: Selecionar todos
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:

Código: Selecionar todos
xXML:= XMLType.createxml(x_conteudo)


Em seguida você faz o insert do XML na sua tabela!

Espero ter ajudado!
rafaelfrocha
Localização: Uberlândia

Rafael Rocha

Mensagemem Ter, 26 Mai 2009 3:25 pm

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


Primeiro crie o diretório no ORACLE apontando para a pasta "/tmp/SMS":
Código: Selecionar todos
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):
Código: Selecionar todos
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.
Código: Selecionar todos
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!
Código: Selecionar todos
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:
Código: Selecionar todos
            <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:
Código: Selecionar todos
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]
williankleber
Localização: Joinville - SC

Mensagemem Seg, 15 Jun 2009 5:32 pm

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


Código: Selecionar todos
<?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>
Nelson
Localização: São Paulo

Mensagemem Ter, 16 Jun 2009 8:41 am

Nelson,

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

Código: Selecionar todos
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:
Código: Selecionar todos
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!
rafaelfrocha
Localização: Uberlândia

Rafael Rocha

Mensagemem Ter, 16 Jun 2009 11:27 am

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 ???

Código: Selecionar todos
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
Nelson
Localização: São Paulo

Mensagemem Ter, 16 Jun 2009 3:46 pm

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:

Código: Selecionar todos
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
/
rafaelfrocha
Localização: Uberlândia

Rafael Rocha

Mensagemem Ter, 16 Jun 2009 4:37 pm

Rafel | Rocha ... Caras, vocês são D+...... funcionou legal !!!
Valeo pelas dicas. Um super obrigado.
Nelson
Localização: São Paulo

Mensagemem Qua, 17 Jun 2009 4:32 pm

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
Código: Selecionar todos
<infNFe Id="NFe35080599999090910270550010000000015180051273"

e
Código: Selecionar todos
<infNFe Id="NFe22222222222222222222222222222222222222222222"


Por favor, onde estou errando ??

a query q estou usando é:
Código: Selecionar todos
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:

Código: Selecionar todos
<?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>
Nelson
Localização: São Paulo

Mensagemem Qua, 17 Jun 2009 5:04 pm

Nelson,

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

Tente assim:
Código: Selecionar todos
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...
rafaelfrocha
Localização: Uberlândia

Rafael Rocha

Mensagemem Qua, 17 Jun 2009 5:49 pm

testei e deu ....

Código: Selecionar todos
ORA-19025: EXTRACTVALUE returns value of only one node
Nelson
Localização: São Paulo

Mensagemem Qui, 18 Jun 2009 9:48 am

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

Código: Selecionar todos
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
Código: Selecionar todos
nodes := xmldom.getElementsByTagName (doc, '*'); -- o asterisco pode ser substituido pela tag que quer pesquisar.


depois fiz uma varredura para pegar um array desses nodes
Código: Selecionar todos
FOR node_index IN 0 .. xmldom.getLength (nodes) - 1  -- me traz os elements
     LOOP


dentro desse for busco os attibutos desse array
Código: Selecionar todos
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
alexrsilva
Localização: Rio de Janeiro - RJ

Mensagemem Qui, 18 Jun 2009 9:53 am

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:
Código: Selecionar todos
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
rafaelfrocha
Localização: Uberlândia

Rafael Rocha

Mensagemem Qui, 18 Jun 2009 10:24 am

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
Localização: São Paulo

Mensagemem Sex, 19 Jun 2009 4:10 pm

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
Localização: São Paulo

Mensagemem Sex, 19 Jun 2009 4:37 pm

Query de exemplo:

Código: Selecionar todos
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
Localização: São Paulo

Mensagemem Sex, 19 Jun 2009 4:41 pm

o peso ocorre uma vez para cada nota
Nelson
Localização: São Paulo

Mensagemem Sex, 19 Jun 2009 5:20 pm

Aparentemente é problema de ligação...

você não estabeleceu nenhuma ligação entre info_xml, NNF, e EMI..
rafaelfrocha
Localização: Uberlândia

Rafael Rocha

Mensagemem Sex, 19 Jun 2009 5:35 pm

na verdade não sei como fazer essa 'ligação' !!!!!!!!
Nelson
Localização: São Paulo

Mensagemem Seg, 22 Jun 2009 2:33 pm

Rafael | Rocha,
Eu postei a query com problema no
http://www.glufke.net/oracle/viewtopic.php?t=4020
Grato
nelson
Nelson
Localização: São Paulo


  • Veja também
    Respostas
    ExibiÇões
    Última mensagem