arquivo xml

[freire28]

pessoal

to criando uma rotina pra fazer uma importacao de um arquivo xml

tem como eu trabalhar com arquivo xml no plsql ?

[rafaelfrocha]

tem sim! no caso o que você quer fazer?

[freire28]

ola

quero pegar um arquivo xml e importar os dados pra dentro do banco

[Trevisolli]

Brother,

Estou trabalhando com XML atualmente aqui na Empresa.
Para fazer a leitura, estou utilizando PACKAGES do próprio Oracle, como extractValue e extract, após eu ter inserido o arquivo XML em um campo XMLType.

Dá uma pesquisada sobre essas funções, que creio que lhe ajudarão.

Segue um exemplo do que estou fazendo:

Selecionar tudo

SELECT extractValue(x.teu_campo_xmltype, '/Dept/NomeDepto/') "Nome do Departamento"
   FROM tua_table_xml x;

Assim que for tendo mais informações, vou postando aqui... é q tb sou novato no assunto.

[rafaelfrocha]

Só complementando a resposta do Trevisolli, em alguns casos é necessário informar o xmlns. Ex:

Selecionar tudo

select extractvalue(colunaXMLType, '/retConsReciNFe/cStat', 'xmlns="http://www.portalfiscal.inf.br/nfe"')
from tabelaX

Também sou novato em XML no Oracle, tenho aprendido muita coisa, tenho aprendido nos últimos tempos! Vamos aproveitar esse tópico para trocar mais experiências!

[Trevisolli]

Rafael, aproveitando...

Não sei se passou por isso, porém, minha TAG XML vêm com Namespace (acho q é esse o termo) e, o extractValue me resulta em:

Invalid XPATH.

O motivo, creio eu, é porque a tag vem da seguinte forma:

Selecionar tudo

<abc:Mensagem>
  <abc:Usuario>

Seria pra essa situação o uso do XMLNS (onde meu namespace seria o abc)?

Valeu pela força.

[rafaelfrocha]

Trevisolli,

quando o XML que você estiver manipulando tiver um namespace declarado, aí você deve colocar ele no seu select, dentro do extractvalue.

exemplo de XML:

Selecionar tudo

<?xml version="1.0" encoding="UTF-8"?>
<retCancNFe xmlns="http://www.portalfiscal.inf.br/nfe" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" versao="1.07">
  <infCanc>
    <tpAmb>2</tpAmb>
    <verAplic>1.07</verAplic>
    <cStat>101</cStat>
    <xMotivo>Cancelamento de NF-e homologado</xMotivo>
    <cUF>31</cUF>
  <chNFe>31081265382137000101550000000000190006177015</chNFe>
    <dhRecbto>2008-12-09T17:42:21</dhRecbto>
    <nProt>131080003632333</nProt>
  </infCanc>
</retCancNFe>

-----
Agora imagina que quero pegar o campo xMotivo!

Selecionar tudo

select extractvalue(xmlRetorno, '/retCancNFe/infCanc/cStat', 'xmlns="http://www.portalfiscal.inf.br/nfe')
from nfe_webservices
where idtransacao = 1119

testei o mesmo select sem o xmlns e me retorna null!

Testa aí no seu select e fala se deu certo!

[Trevisolli]

Brother, fiz um teste com EXTRACT para visualizar AS informações e, com este código:

Selecionar tudo

SELECT EXTRACT  (x.dsxml ,'/mensagemTISS/prestadorParaOperadora/loteGuias/guias/guiaFaturamento/guiaSP_SADT/dadosBeneficiario/numeroCarteira','xmlns="http://www.ans.gov.br/padroes/tiss/schemas"')
FROM rg_xml x;

Me retorna um XMLTYPE, contendo AS seguintes informações:

Selecionar tudo

<tiss:numeroCarteira xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas">00020602001854007</tiss:numeroCarteira>
<tiss:numeroCarteira xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas">00026704001948007</tiss:numeroCarteira>
<tiss:numeroCarteira xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas">00029675005421002</tiss:numeroCarteira>
<tiss:numeroCarteira xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas">00029674008760004</tiss:numeroCarteira>

Porém, estou necessitando que me retorne 4 linhas, sem AS tags XML:

Selecionar tudo

00020602001854007
00026704001948007
00029675005421002
00029674008760004

Tentei utilizar o ExtractValue da forma abaixo, porém, retorna NULL:

Selecionar tudo

SELECT extractValue(VALUE (d), '/guiaSP_SADT/dadosBeneficiario','xmlns="http://www.ans.gov.br/padroes/tiss/schemas"') 
   FROM rg_xml x,
        TABLE (XMLSEQUENCE (EXTRACT (x.dsxml ,'/mensagemTISS/prestadorParaOperadora/loteGuias/guias/guiaFaturamento/guiaSP_SADT'))) d;      

Utilizando o ExtractValue sem XMLSequence ele me retorna o seguinte erro:

ORA-19025: EXTRACTVALUE returns value of only one node.

Estou realizando pesquisas e testes aqui, porém, caso alguém já tenha passado por isso, agradeço a ajuda.

Nota: Estou trabalhando com o TISS, da ANS (Saúde Suplementar) e, preciso retornar os DETALHES das Guias.

Complementando:

A query abaixo até funciona (retornar número carteira) sem erros, porém, retorna NULL:

Selecionar tudo

select extractvalue(value(d),'/dadosBeneficiario/numeroCarteira','xmlns="http://www.ans.gov.br/padroes/tiss/schemas"') carteira
  from rg_xml x,
       table(xmlsequence(extract(x.dsxml, '/mensagemTISS/prestadorParaOperadora/loteGuias/guias/guiaFaturamento/guiaSP_SADT/dadosBeneficiario'))) d;

[rafaelfrocha]

Trevisolli,

Posta o exemplo seu seu XML para vermos o que pode ser feito!

att,

[Trevisolli]

Pra constar:

Consegui extrair o campo Número Carteira do XML, onde o mesmo deve retornar (neste caso) 4 linhas.

Selecionar tudo

SELECT extractvalue(VALUE(tab),'/tiss:numeroCarteira','xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') Carteira -- Passar namespace e end do namespace
FROM rg_xml x,
  table (
    XMLSequence(extract(
     x.dsxml,
     '/mensagemTISS/prestadorParaOperadora/loteGuias/guias/guiaFaturamento/guiaSP_SADT/dadosBeneficiario/numeroCarteira','xmlns="http://www.ans.gov.br/padroes/tiss/schemas"') -- Não utilizar o namespace, apenas o endereço (sem o TISS)
    ) 
  ) tab;

[Trevisolli]

Pessoal, complementando, para se ler mais de uma coluna do XML:

Selecionar tudo

SELECT extractvalue(VALUE(tab),'/tiss:identificacaoGuiaSADTSP/tiss:registroANS'
                      ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') registroANS
           , extractvalue(VALUE(tab),'/tiss:identificacaoGuiaSADTSP/tiss:dataEmissaoGuia'
                        ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') dataEmissaoGuia
           , extractvalue(VALUE(tab),'/tiss:identificacaoGuiaSADTSP/tiss:numeroGuiaPrestador'
                        ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') numeroGuiaPrestador
           , extractvalue(VALUE(tab),'/tiss:identificacaoGuiaSADTSP/tiss:numeroGuiaOperadora'
                        ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') numeroGuiaOperadora
        FROM tmp_recebe_arq_xml p,
        table(XMLSequence(extract(p.txt_arq, 
             '/mensagemTISS/prestadorParaOperadora/loteGuias/guias/guiaFaturamento/guiaSP_SADT/identificacaoGuiaSADTSP'
             ,'xmlns="http://www.ans.gov.br/padroes/tiss/schemas"'))) tab;

  -- No table: informar o até o nó Pai.
  -- No extractvalue: Informar o Pai ('/tiss:identificacaoGuiaSADTSP'), mais o filho 
  --                  que se deseja listar ('/tiss:registroANS'), ficando: '/tiss:identificacaoGuiaSADTSP/tiss:registroANS'      

[williankleber]

Boa tarde Trevisolli

usei como base seu exemplo para buscar os dados dentro do XML. (TISS/ANS)

Porém dessa maneira esta extremamente lento a consulta! O custo é > 10.000.000.000.000

O código que estou utilizando é o seguinte:

Selecionar tudo

SELECT extractvalue(VALUE(tac),'/tiss:mensagemTISS/tiss:cabecalho/tiss:identificacaoTransacao/tiss:tipoTransacao'
                  ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') tipoTransacao,
       extractvalue(VALUE(tac),'/tiss:mensagemTISS/tiss:cabecalho/tiss:identificacaoTransacao/tiss:sequencialTransacao'
                  ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') sequencialTransacao,
       extractvalue(VALUE(tac),'/tiss:mensagemTISS/tiss:cabecalho/tiss:identificacaoTransacao/tiss:dataRegistroTransacao'
                  ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') dataRegistroTransacao,
       extractvalue(VALUE(tac),'/tiss:mensagemTISS/tiss:cabecalho/tiss:identificacaoTransacao/tiss:horaRegistroTransacao'
                  ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') horaRegistroTransacao,
       extractvalue(VALUE(tac),'/tiss:mensagemTISS/tiss:cabecalho/tiss:origem/tiss:codigoPrestadorNaOperadora/tiss:codigoPrestadorNaOperadora'
                  ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') codigoPrestadorNaOperadora,
       extractvalue(VALUE(tac),'/tiss:mensagemTISS/tiss:cabecalho/tiss:destino/tiss:registroANS'
                  ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') registroANS,
       extractvalue(VALUE(tac),'/tiss:mensagemTISS/tiss:cabecalho/tiss:versaoPadrao'
                  ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') versaoPadrao,
       extractvalue(VALUE(tac),'/tiss:prestadorParaOperadora/tiss:loteGuias/tiss:numeroLote'
                  ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') numeroLote,       
       extractvalue(VALUE(guiaSP_SADT),'/tiss:guiaSP_SADT/tiss:numeroGuiaPrincipal'
                  ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') nr_guia_principal,
       extractvalue(VALUE(guiaSP_SADT),'/tiss:guiaSP_SADT/tiss:dadosBeneficiario/tiss:numeroCarteira'
                  ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') nr_carteira_beneficiario,
       extractvalue(VALUE(guiaSP_SADT),'/tiss:guiaSP_SADT/tiss:dadosBeneficiario/tiss:nomeBeneficiario'
                  ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') nm_beneficiario,
       extractvalue(VALUE(SP_SADT_proc),'/tiss:procedimentos/tiss:procedimento/tiss:codigo'
                  ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') cd_procedimento,
       extractvalue(VALUE(SP_SADT_proc),'/tiss:procedimentos/tiss:procedimento/tiss:descricao'
                  ,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"') nm_procedimento
  FROM TABLE_XML p,
       table(XMLSequence(extract(p.xml_data,
            '/mensagemTISS'
            ,'xmlns="http://www.ans.gov.br/padroes/tiss/schemas"'))) tac,
       table(XMLSequence(extract(value(tac),
            '/mensagemTISS/prestadorParaOperadora/loteGuias/guias/guiaFaturamento'
            ,'xmlns="http://www.ans.gov.br/padroes/tiss/schemas"'))) guiaFaturamento,
       table(XMLSequence(extract(value(guiaFaturamento),
            '/guiaFaturamento/guiaSP_SADT'
            ,'xmlns="http://www.ans.gov.br/padroes/tiss/schemas"'))) guiaSP_SADT,
       table(XMLSequence(extract(value(guiaSP_SADT),
            '/guiaSP_SADT/procedimentosRealizados/procedimentos'
            ,'xmlns="http://www.ans.gov.br/padroes/tiss/schemas"'))) SP_SADT_proc;

Fiz dessa maneira para poder selecionar os diversos relacionamentos de um para muitos do arquivo!

Gostaria de uma dica para otimizar essa consulta. Conhece alguma maneira de organizar esse select para que faça a mesma coisa com um custo pelo menos "um milhão de vezes" menor!

Ah, esse exemplo que estou listando é apenas parte do que deverá ser o select quando ficar pronto, se nesse ponto do desenvolvimento o select já esta com o custo dessa maneira imagina quando eu colocar o dobro de colunas que existem atualmente!

[rafaelfrocha]

Willian,

Quando você faz um select utilizando XMLQuery, o Oracle irá retornar todos os dados das tags no formato varchar2(4000).

Será que se você tratar o tipo de dados que ele irá retornar a performance melhora?

Ex: to_number(extractvalue(VALUE(tac),'/tiss:mensagemTISS/tiss:cabecalho/tiss:identificacaoTransacao/tiss:sequencialTransacao'
,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"')) sequencialTransacao

para campos varchar que você sabe o tamanho:

Ex: substr (extractvalue(VALUE(guiaSP_SADT),'/tiss:guiaSP_SADT/tiss:dadosBeneficiario/tiss:nomeBeneficiario'
,'xmlns:tiss="http://www.ans.gov.br/padroes/tiss/schemas"'),1,60) nm_beneficiario

Eu tenho aqui algumas views utilizando o XMLQuery, depois que tratei cada campo notei uma melhora na performance!

[Klayton Anselmo]

Ola pessoal boa tarde,

Tenho uma tag que contem as seguintes informações:

Selecionar tudo

	<vPrest>
		<vTPrest>22.02</vTPrest>
		<vRec>22.02</vRec>

	                <Comp>
	                              <xNome>DESPACHO TOTAL</xNome>	                              <vComp>11.36</vComp>
                     	</Comp>

                                <Comp>
                                              <xNome>PEDAGIO TOTAL</xNome>
		              <vComp>0.32</vComp>
		</Comp>

		<Comp>
		              <xNome>FRETE PESO VOLU</xNome>
		              <vComp>8.75</vComp>
		</Comp>

		<Comp>
		              <xNome>FRETE VALOR TOT</xNome>
		              <vComp>1.59</vComp>
		</Comp>
	</vPrest>

Consegui extrai todas as informações só que em 2 select´s distintos:

Selecionar tudo

SELECT extractvalue(VALUE(PREST), '/XNOME')
  FROM T_XML_CTE CC,
       TABLE(XMLSequence(Extract(CC.CORPO_XML, '/ENVICTE/CTE/INFCTE/VPREST/COMP/XNOME'))) PREST
 WHERE CC.DOCUMENTO = '115038'
   AND CC.SERIE             = 'A1'
   AND CC.FILIAL             = '003';

Selecionar tudo

SELECT extractvalue(VALUE(VCOMP), '/VCOMP')  
FROM T_XML_CTE CC,
       TABLE(XMLSequence(Extract(CC.CORPO_XML, '/ENVICTE/CTE/INFCTE/VPREST/COMP/VCOMP'))) VCOMP
 WHERE CC.DOCUMENTO = '115038'
   AND CC.SERIE             = 'A1'
   AND CC.FILIAL             = '003';

Com esse outro select consegui extrair as informações, mas elas vieram duplicadas: forma selecioandas 16 linhas e não 4 como deveria.

Selecionar tudo

  SELECT distinct(extractvalue(VALUE(PREST), '/XNOME')),
         extractvalue(VALUE(VCOMP), '/VCOMP')
  FROM T_XML_CTE CC,
       TABLE(XMLSequence(Extract(CC.CORPO_XML, '/ENVICTE/CTE/INFCTE/VPREST/COMP/XNOME'))) PREST,
       TABLE(XMLSequence(Extract(CC.CORPO_XML, '/ENVICTE/CTE/INFCTE/VPREST/COMP/VCOMP'))) VCOMP
WHERE CC.DOCUMENTO = '115038'
   AND CC.SERIE             = 'A1'
   AND CC.FILIAL             = '003';

alguém tem alguma ideia?


Abraços.

[williankleber]

Klayton Anselmo...

Desculpe a demora, mas seu codigo ficaria mais ou menos assim:

Selecionar tudo

SELECT extractvalue(VALUE(COMP),
                    '/COMP/XNOME') PREST,
       extractvalue(VALUE(COMP),
                    '/COMP/VCOMP') VCOMP
  FROM T_XML_CTE CC,
       TABLE(XMLSequence(Extract(CC.CORPO_XML, '/ENVICTE/CTE/INFCTE/VPREST/COMP'))) COMP
 WHERE CC.DOCUMENTO = '115038'
   AND CC.SERIE = 'A1'
   AND CC.FILIAL = '003';

Quem saiba uma maneira de melhorar esse codigo tambem seria uma dica bem vinda para mim!!!!

[Amarildomarques]

Pessoal,

gostaria de saber se é possivel extrair informacoes de um xml onte tem uma tag de grupo e seus dados logo abaixo: ex:
. . .
<DETALHE ORDEM="1">
. . .
<DETALHE ORDEM="20">
. . .
gostaria de pegar este numero 1, 20. estou usando as dicas citadas anteriormente da funcao extractvalue() e não estou conseguindo
grato pela atenção

[rafaelfrocha]

Colega,

Se entendi bem você deseja pegar um atributo da Tag XML. Você pode utilizar da seguinte forma:

Selecionar tudo

extractvalue(VALUE(TAB),'/caminho/@ORDEM

Então se o atributo da tag que você quer extrair é "ordem" você deve utilizar o "@" antes!

[Amarildomarques]

Valeu,

que Deus continue te abençoando e que você continua assim ajudando as pessoas, caso algum dia posso ajuda-lo será uma satisfaçao.

abraços e fique com Deus!

[Amarildomarques]

Pessoal,
estou gerando um arquivo xml usando pl/sql, e no inicio do mesmo deveria ter esta informacao:

Selecionar tudo

<?xml version="1.0" encoding="UTF-8" ?> .

alguém poderia me ajudar ?

Amarildo.

[Nelson]

Pessoall, boa tarde. Estou com o seguinte problema:

Na query:

Selecionar tudo

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

resultado:

Selecionar tudo

NRONNF
NFe35080599999090910270550010000000015180051273
NFe22222222222222222222222222222222222222222222

Na query:

Selecionar tudo

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

resultado:

Selecionar tudo

PESO
1200000000.000
2200000000.000

na query:

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

resultado:

Selecionar tudo

NRONNF	                                                                    PESO
NFe35080599999090910270550010000000015180051273 1200000000.000
NFe35080599999090910270550010000000015180051273 2200000000.000
NFe22222222222222222222222222222222222222222222 1200000000.000
NFe22222222222222222222222222222222222222222222 2200000000.000

ou seja, quando eu tento juntar a NRONNF com o PESO (por exemplo) o resultado fica duplicado.

Deveria resultar assim :

Selecionar tudo

NRONNF	                                                                    PESO
NFe35080599999090910270550010000000015180051273 1200000000.000
NFe22222222222222222222222222222222222222222222 2200000000.000

Não estou conseguindo ler o 'pai' com seus 'filhos'.
Desde já agradeço,
Nelson

[rafaelfrocha]

Nelson,

porque você está utilizando 2 paths de XMLSequence?

Selecionar tudo

TABle(XMLSequence(extract(conteudo,'/NFe/infNFe','xmlns="http://www.portalfiscal.inf.br/nfe"'))) NNF,

e

TABle(XMLSequence(extract(conteudo,'/NFe/infNFe/transp/vol','xmlns="http://www.portalfiscal.inf.br/nfe"'))) EMI 

Não dá pra sintetizar tudo em um unico Path não?
ex:

Selecionar tudo

Select extractValue (value(xmlNFE),'/infNFe/@Id', 'xmlns="http://www.portalfiscal.inf.br/nfe"') NroNNF, 
extractValue (VALUE(xmlNFE), '/infNFe/transp/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"'))) xmlNFE
Where id = 7 

só uma idéia! verifica aí se funciona...

[Nelson]

Rafael, entendi o path a ser colocado,
mas porque que na query o < det nItem > e o xProd não aparecem ????

Selecionar tudo

Select extractValue (value(xmlNFE),'/infNFe/@Id', 'xmlns="http://www.portalfiscal.inf.br/nfe"') NroNNF, 
extractValue (VALUE(xmlNFE), 'infNFe/det/nItem', 'xmlns="http://www.portalfiscal.inf.br/nfe"') item, 
extractValue (VALUE(xmlNFE), 'infNFe/det/nItem/prod/xProd', 'xmlns="http://www.portalfiscal.inf.br/nfe"') xprod 
from info_xml, 
TABle(XMLSequence(extract(conteudo,'/NFe/infNFe','xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE 
Where id = 7

só mais uma vez, e ... acredito que essa fase vai terminar......
grato

[Nelson]

OK, entendi o path a ser colocado,
mas porque que na query o < det nItem > e o xProd não aparecem ????

Selecionar tudo

Select extractValue (value(xmlNFE),'/infNFe/@Id', 'xmlns="http://www.portalfiscal.inf.br/nfe"') NroNNF, 
extractValue (VALUE(xmlNFE), 'infNFe/det/nItem', 'xmlns="http://www.portalfiscal.inf.br/nfe"') item, 
extractValue (VALUE(xmlNFE), 'infNFe/det/nItem/prod/xProd', 'xmlns="http://www.portalfiscal.inf.br/nfe"') xprod 
from info_xml, 
TABle(XMLSequence(extract(conteudo,'/NFe/infNFe','xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE 
Where id = 7 

só mais uma vez, e ... acredito que essa fase vai terminar......
grato

[williankleber]

No link www.blognovo.com tem um exemplo de como fazer JOIN's nos selects de XML.

Mas mesmo assim,
creio que sua query ficaria assim:

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(value(NNF),'/infNFe/transp/vol','xmlns="http://www.portalfiscal.inf.br/nfe"'))) EMI 
Where id = 7 

Veja que a segunda query do seu select, ao invés de "extract(conteudo" ficou como "extract(value(NNF)"

Abraço, qualquer dúvida, estamos aí!

[Nelson]

corretíssimo. Valeo.
Brigadão

[Nelson]

mas a query que não funciona é:

Selecionar tudo

Select extractValue (value(xmlNFE),'/infNFe/@Id', 'xmlns="http://www.portalfiscal.inf.br/nfe"') NroNNF, 
extractValue (VALUE(xmlNFE), 'infNFe/det/nItem', 'xmlns="http://www.portalfiscal.inf.br/nfe"') item, 
extractValue (VALUE(xmlNFE), 'infNFe/det/nItem/prod/xProd', 'xmlns="http://www.portalfiscal.inf.br/nfe"') xprod 
from info_xml, 
TABle(XMLSequence(extract(conteudo,'/NFe/infNFe','xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE 
Where id = 7 

não mostra o nItem e o xProd .....

[williankleber]

OK...

Imaginei que seu problema era em extrair dados mestre-detalhes.

Bom mas o que não esta funcionando nessa query?

Teria como passar um trecho do XML?

[williankleber]

Selecionar tudo

 Select extractValue (value(xmlNFE),'/infNFe/@Id', 'xmlns="http://www.portalfiscal.inf.br/nfe"') NroNNF, 
extractValue (VALUE(xmlNFE), 'infNFe/det/nItem', 'xmlns="http://www.portalfiscal.inf.br/nfe"') item, 
extractValue (VALUE(xmlNFE_prod), 'prod/xProd', 'xmlns="http://www.portalfiscal.inf.br/nfe"') xprod 
from info_xml, 
TABle(XMLSequence(extract(conteudo,'/NFe/infNFe','xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE 
TABle(XMLSequence(extract(VALUE(xmlNFE),'/infNFe/det/nItem/prod/','xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE_prod 
Where id = 7 

[Nelson]

segue um trecho ..

Selecionar tudo

<?xml version="1.0" encoding="utf-8"?>
<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>
...
...

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

lembrando que pode ter (n) itens

[williankleber]

Amigo, o ultimo select que te passei não resolve esse caso?

Se não deu certo vou fazer uma simulação assim que chegar em casa. aqui não tenho nenhum banco disponível para fazer testes!

[Nelson]

Query,

Selecionar tudo

Select extractValue (value(xmlNFE),'/infNFe/@Id', 'xmlns="http://www.portalfiscal.inf.br/nfe"') NroNNF, 
extractValue (VALUE(xmlNFE), 'infNFe/det/nItem', 'xmlns="http://www.portalfiscal.inf.br/nfe"') item, 
extractValue (VALUE(xmlNFE_prod), 'prod/xProd', 'xmlns="http://www.portalfiscal.inf.br/nfe"') xprod 
from info_xml, 
TABle(XMLSequence(extract(conteudo,'/NFe/infNFe','xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE, 
TABle(XMLSequence(extract(VALUE(xmlNFE),'/infNFe/det/nItem/prod/','xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE_prod 
Where id = 7  

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '/infNFe/det/nItem/prod/'

deu esse erro !!!!!!

[Nelson]

na linha

Selecionar tudo

TABle(XMLSequence(extract(VALUE(xmlNFE),'/infNFe/det/nItem/prod/','xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE_prod 

retirei o " / "

Selecionar tudo

TABle(XMLSequence(extract(VALUE(xmlNFE),'/infNFe/det/nItem/prod','xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE_prod 

e não apareceu nada

grato
Nelson

[williankleber]

Selecionar tudo

TABle(XMLSequence(extract(VALUE(xmlNFE),'/infNFe/det/nItem/prod/','xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE_prod 

RETIRA O "VALUE(xmlNFE)" DESSA TABELA, TE PASSEI ERRADO! DESCULPE. É QUE É MEIO DIFICIL SEM UMA BASE PARA SIMULAR!

ASSIM O CÍDIGO FICARIA ASSIM:

Selecionar tudo

Select extractValue (value(xmlNFE),'/infNFe/@Id', 'xmlns="http://www.portalfiscal.inf.br/nfe"') NroNNF, 
extractValue (VALUE(xmlNFE), 'infNFe/det/nItem', 'xmlns="http://www.portalfiscal.inf.br/nfe"') item, 
extractValue (VALUE(xmlNFE_prod), 'prod/xProd', 'xmlns="http://www.portalfiscal.inf.br/nfe"') xprod 
from info_xml, 
TABle(XMLSequence(extract(conteudo,'/NFe/infNFe','xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE, 
TABle(XMLSequence(extract(xmlNFE,'/infNFe/det/nItem/prod/','xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE_prod 
Where id = 7 

TENTA aí

[Nelson]

Bom dia.
na linha :

Selecionar tudo

TABle(XMLSequence(extract(xmlNFE,'/infNFe/det/nItem/prod/','xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE_prod

deu :

Selecionar tudo

ORA-00904: "XMLNFE": invalid identifier

grato
Nelson

[jader.barros]

Selecionar tudo

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
   <soap:Body>
      <ns2:consultarContaContabilResponse xmlns:ns2="http://ws.ofm.com.br/">
         <return>
            <listaResultadoConsulta xsi:type="ns2:contaContabilDTO" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
               <cdRedConta>1</cdRedConta>
               <codigoConta>1</codigoConta>
               <grupoConta>1</grupoConta>
               <mascara>1</mascara>
               <nivelConta>1</nivelConta>
               <nomeConta>ATIVO</nomeConta>
               <pertenceAneel>N</pertenceAneel>
               <tipoConta>S</tipoConta>
               <tipoSaldoConta>D</tipoSaldoConta>
            </listaResultadoConsulta>
            <listaResultadoConsulta xsi:type="ns2:contaContabilDTO" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
               <cdRedConta>22938</cdRedConta>
               <codigoConta>11101300000200138</codigoConta>
               <contaSomar>111013</contaSomar>
               <grupoConta>1</grupoConta>
               <mascara>111.01.3.0.0.0.002.001.38</mascara>
               <nivelConta>6</nivelConta>
               <nomeConta>BB AG 4061-4 C/C 010-9 GARANTIA DE LEILÃO A-5</nomeConta>
               <pertenceAneel>N</pertenceAneel>
               <tipoConta>A</tipoConta>
               <tipoSaldoConta>D</tipoSaldoConta>
            </listaResultadoConsulta>
            <listaResultadoConsulta xsi:type="ns2:contaContabilDTO" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
               <cdRedConta>19309</cdRedConta>
               <codigoConta>11101300000210405</codigoConta>
               <contaSomar>111013</contaSomar>
               <grupoConta>1</grupoConta>
               <mascara>111.01.3.0.0.0.002.104.05</mascara>
               <nivelConta>6</nivelConta>
               <nomeConta>CEF AG GRUTA 301-6</nomeConta>
               <pertenceAneel>N</pertenceAneel>
               <tipoConta>A</tipoConta>
               <tipoSaldoConta>D</tipoSaldoConta>
            </listaResultadoConsulta>
            <listaResultadoConsulta xsi:type="ns2:contaContabilDTO" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
               <cdRedConta>22836</cdRedConta>
               <codigoConta>11101300000223703</codigoConta>
               <contaSomar>111013</contaSomar>
               <grupoConta>1</grupoConta>
               <mascara>111.01.3.0.0.0.002.237.03</mascara>
               <nivelConta>6</nivelConta>
               <nomeConta>BRADESCO AG 0895 TRIANON 07/09 CC 79742-1</nomeConta>
               <pertenceAneel>N</pertenceAneel>
               <tipoConta>A</tipoConta>
               <tipoSaldoConta>D</tipoSaldoConta>
            </listaResultadoConsulta>
            <resultadoMensagem>
               <mensagem>Operação Realizada com Sucesso.</mensagem>
               <tipoMensagem>INFO</tipoMensagem>
            </resultadoMensagem>
         </return>
      </ns2:consultarContaContabilResponse>
   </soap:Body>
</soap:Envelope>

[fsitja]

Umm.. ok?




Não entendi.

[bertosro]

Olá Mestres, Boa Noite.

Por favor podem me ajudar no codigo abaixo, MESTRE x DETALHE não retorna dados no detalhe.

Selecionar tudo

DECLARE
   v$xml       XMLTYPE;
   v$retorno01   LONG;
   v$retorno02   LONG;   
   v$xmlns     VARCHAR2(100) := 'xmlns="http://www.portalfiscal.inf.br/cte';
BEGIN
   v$xml :=
      XMLTYPE
         ('<?xml version="1.0" encoding="UTF-8"?><cteProc xmlns="http://www.portalfiscal.inf.br/cte" versao="1.03"><CTe xmlns="http://www.portalfiscal.inf.br/cte"><infCte versao="1.03" Id="CTe31110142278291001953570000000000370061280213"><ide><cUF>31</cUF><cCT>006128021</cCT><CFOP>5352</CFOP><natOp>PREST.SERV.TRANSP.ESTAB.INDUSTRIAL</natOp><forPag>2</forPag><mod>57</mod><serie>0</serie><nCT>37</nCT><dhEmi>2011-01-18T12:00:01</dhEmi><tpImp>2</tpImp><tpEmis>1</tpEmis><cDV>3</cDV><tpAmb>2</tpAmb><tpCTe>0</tpCTe><procEmi>0</procEmi><verProc>1.03</verProc><cMunEmi>3170206</cMunEmi><xMunEmi>UBERLANDIA</xMunEmi><UFEmi>MG</UFEmi><modal>01</modal><tpServ>0</tpServ><cMunIni>3170206</cMunIni><xMunIni>UBERLANDIA</xMunIni><UFIni>MG</UFIni><cMunFim>3170206</cMunFim><xMunFim>UBERLANDIA</xMunFim><UFFim>MG</UFFim><retira>1</retira><toma03><toma>0</toma></toma03></ide><emit><CNPJ>42278291001953</CNPJ><IE>0010439740045</IE><xNome>UBERLANDIA</xNome><xFant>UBERLANDIA</xFant><enderEmit><xLgr>RUA REPUBLICA PIRATINI, 1145 PARTE</xLgr><nro>0</nro><xBairro>BAIRRO N. SENHORA DA</xBairro><cMun>3170206</cMun><xMun>UBERLANDIA</xMun><CEP>38402028</CEP><UF>MG</UF></enderEmit></emit><rem><CNPJ>20730099000194</CNPJ><IE>ISENTO</IE><xNome>SADIA S.A.</xNome><xFant>SADIA S.A.</xFant><enderReme><xLgr>R SENADOR ATTILIO FONTANA, 86</xLgr><nro>0</nro><xBairro>CENTRO</xBairro><cMun>4204301</cMun><xMun>CONCORDIA</xMun><CEP>89700000</CEP><UF>SC</UF></enderReme><infNF><serie>U</serie><nDoc>37</nDoc><dEmi>2011-01-18</dEmi><vBC>284.09</vBC><vICMS>34.09</vICMS><vBCST>284.09</vBCST><vST>34.09</vST><vProd>80000.00</vProd><vNF>9001.00</vNF><nCFOP>5352</nCFOP><nPeso>30000.000</nPeso><locRet><CNPJ>20730099008845</CNPJ><xNome>SADIA S.A.</xNome><xLgr>AV. CORONEL JOSE TEOFILO CARNEIRO, 1001,</xLgr><nro>0</nro><xBairro>são JOSE</xBairro><cMun>3170206</cMun><xMun>UBERLANDIA</xMun><UF>MG</UF></locRet></infNF></rem><exped><CNPJ>42278291001953</CNPJ><IE>0010439740045</IE><xNome>UBERLANDIA</xNome><enderExped><xLgr>RUA REPUBLICA PIRATINI, 1145 PARTE</xLgr><nro>0</nro><xBairro>BAIRRO N. SENHORA DA</xBairro><cMun>3170206</cMun><xMun>UBERLANDIA</xMun><CEP>38402028</CEP><UF>MG</UF></enderExped></exped><receb><CNPJ>42278291001953</CNPJ><IE>0010439740045</IE><xNome>UBERLANDIA</xNome><enderReceb><xLgr>RUA REPUBLICA PIRATINI, 1145 PARTE</xLgr><nro>0</nro><xBairro>BAIRRO N. SENHORA DAS GRA</xBairro><cMun>3170206</cMun><xMun>UBERLANDIA</xMun><CEP>38402028</CEP><UF>MG</UF></enderReceb></receb><dest><CNPJ>20730099008845</CNPJ><IE>7022691561144</IE><xNome>SADIA S.A.</xNome><enderDest><xLgr>AV. CORONEL JOSE TEOFILO CARNEIRO, 1001,</xLgr><nro>0</nro><xBairro>são JOSE</xBairro><cMun>3170206</cMun><xMun>UBERLANDIA</xMun><CEP>38401999</CEP><UF>MG</UF></enderDest><locEnt><CNPJ>20730099008845</CNPJ><xNome>SADIA S.A.</xNome><xLgr>AV. CORONEL JOSE TEOFILO CARNEIRO, 1001,</xLgr><nro>0</nro><xBairro>são JOSE</xBairro><cMun>3170206</cMun><xMun>UBERLANDIA</xMun><UF>MG</UF></locEnt></dest><vPrest><vTPrest>284.09</vTPrest><vRec>284.09</vRec><Comp><xNome>FRETE PESO</xNome><vComp>250.00</vComp></Comp></vPrest><imp><ICMS><CST00><CST>00</CST><vBC>284.09</vBC><pICMS>12.00</pICMS><vICMS>34.09</vICMS></CST00></ICMS><infAdFisco>OPERACAO DE TRANSPORTE MULTIMODAL/INTERMODAL. TRANSBORDO PARA O MODAL RODOVIARIO EM UBERLANDIA-MG</infAdFisco></imp><infCTeNorm><infCarga><vMerc>80000.00</vMerc><proPred>DIVERSOS</proPred><xOutCat>CAIXA</xOutCat><infQ><cUnid>01</cUnid><tpMed>CAIXA</tpMed><qCarga>900.0000</qCarga></infQ></infCarga><contQt><nCont>GSTU8099053</nCont><lacContQt><nLacre>123</nLacre></lacContQt><dPrev>2011-01-18</dPrev></contQt><rodo><RNTRC>00000000000000</RNTRC><dPrev>2011-01-21</dPrev><lota>0</lota><valePed><respPg>0</respPg><disp><tpDisp>1</tpDisp><xEmp>EMPRESA CREDENCIADA</xEmp><dVig>2011-01-21</dVig><nDisp>10</nDisp></disp></valePed><veic><RENAVAM>000000000</RENAVAM><placa>KSZ7951</placa><tara>1</tara><capKG>1000</capKG><capM3>10</capM3><tpProp>T</tpProp><tpVeic>0</tpVeic><tpRod>00</tpRod><tpCar>00</tpCar><UF>RJ</UF></veic></rodo></infCTeNorm></infCte></CTe><protCTe versao="1.03"><infProt><tpAmb>2</tpAmb><verAplic>1.1</verAplic><chCTe>31110142278291001953570000000000370061280213</chCTe><dhRecbto>2011-01-18T08:57:21</dhRecbto><digVal></digVal><cStat>212</cStat><xMotivo>Rejeição: Data de emissão CT-e posterior a data de recebimento</xMotivo></infProt></protCTe></cteProc>'
         );

   SELECT EXTRACTVALUE (v$xml,'/cteProc/CTe/infCte/ide/cCT/text()',v$xmlns),
          EXTRACTVALUE (VALUE (DET), '/CTe/infCte/ide/cCT/text()') RET_DET
     INTO v$retorno01, v$retorno02
     FROM DUAL,
          TABLE (XMLSEQUENCE (EXTRACT (v$xml, '/cteProc', v$xmlns))) DET;
          
     dbms_output.put_line('01 '||v$retorno01);
     dbms_output.put_line('02 '||v$retorno02);     
END;

Se executar o codigo acima podem ver que o retorno02 retorna nulo.

[fsitja]

Olá,

Você poderia postar o que você precisa que seja retornado para esse exemplo?

[bertosro]

Boa Tarde.
Os dois v$retorno01 e v$retorno02 precisam retornar o mesmo conteudo.
O v$retorno01 eu acesso pelo XML completo já o v$retorno02 e pelo TABLE (XMLSEQUENCE (EXTRACT.

[fsitja]

Executei o seu código e retornou o seguinte:

Selecionar tudo

01 006128021
02 

Você quer que retorne isso abaixo??

Selecionar tudo

01 006128021
02 006128021

Ou entendi errado?

Se não for isso estou perdido, e acho que não sou o único

Daria pra postar exatamente o que você quer ver?

[leandromiranda87]

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,

[leandromiranda87]

Apesar de continuar a pesquisa, até agora não consegui resolver esse problema.

Alguém sabe como pode ser feito isso? Ou conhece uma outra forma?

Obrigado

[Trevisolli]

Brother, bom dia... beleza?

Vou dar uma olhada no teu XML e ver se consigo montar o ExtractValue (fiz isso há um tempo atrás rs...).
Agora, uma dúvida:

• Teus Directories estão funcionando legal?

Você chega à acessar o XML ?

[leandromiranda87]

Fala irmão,

Agradeço a atenção.

Está funcionando sim cara, consigo fazer todo o processo de identificação dos arquivos inseridos no diretório, a importação para a tabela XMLType e a transferência dos arquivos para outro diretório quando eles são inseridos na base.

Se faço uma seleção básica na tabela consigo abrir o campo com o conteúdo XML sem problemas também.

Existe algum outro problema que posso estar deixando passar??

Peguei um exemplo pronto na net que da certinho, mas nesse outro padrão não consegui fazer. Não sei se é o padrão do XML ou se é algum erro que estou cometendo.

Exemplo com resultado

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 custom_xml_received XT,
       TABLE(XMLSequence(Extract(XT.conteudo_arquivo , '/FAQ-LIST'))) "FAQ",            -- Master
       TABLE(XMLSequence(Extract(VALUE(FAQ) , '/FAQ-LIST/QUESTION'))) "QUE"            -- Detalhes
WHERE XT.nome_arquivo = 'contato005.XML'

Selecionar tudo

  <?xml version="1.0" ?> 
- <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>

[Trevisolli]

Brother, fiz um teste aqui e deu certo:

Selecionar tudo

declare 
 --
 vxml xmltype := xmltype('<?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>');
  vret varchar2(1000);
  vret1 varchar2(1000);
  
  -- 
begin

  SELECT extractvalue(value(AGENDA), '/contato/nome') nome
       , extractvalue(value(AGENDA), '/contato/email') email
    INTO vret 
       , vret1
    FROM TABLE(XMLSequence(Extract(vxml , '/agenda/contato'))) AGENDA;
    
  dbms_output.put_line('Retorno: '||nvl(vret,'-x-')||' ret1: '||vret1);

end;
 

Testa este que postei, se possível e, tenta fazer a adaptação ai.
Qualquer coisa, manda pra gente.

[leandromiranda87]

Maravilha irmão,

Infelizmente ainda não tive tempo pra terminar isso, mas fiz alguns testes básicos e deu certinho.

Muito obrigado,

Abraço.

[marlonpasquali]

Olá,
eu uso a rotina abaixo para ler a NFe:

Selecionar tudo

for c in (SELECT tabela_xml_extract.*
    FROM PCN_XML tabela,
         XMLTable(
            XMLNAMESPACES(DEFAULT 'http://www.portalfiscal.inf.br/nfe')
           ,'/nfeProc/NFe/infNFe'
           PASSING tabela.nota/*campo XML*/
           COLUMNS Nota      varchar2(15)    PATH '/infNFe/ide/nNF/text()',
                   modelo    varchar2(2)     PATH '/infNFe/ide/mod/text()',
                   serie     varchar2(2)     PATH '/infNFe/ide/serie/text()',
                   Emissao   varchar2(15)    PATH '/infNFe/ide/dEmi/text()',
                   Fornecedor  varchar2(18)  PATH '/infNFe/emit/CNPJ/text()',
                   BCNota      varchar2(15)  PATH '/infNFe/total/ICMSTot/vBC/text()',
                   IcmsNota    varchar2(15)  PATH '/infNFe/total/ICMSTot/vICMS/text()',
                   VlrProdutos varchar2(15)  PATH '/infNFe/total/ICMSTot/vProd/text()',
                   VlrFrete    varchar2(15)  PATH '/infNFe/total/ICMSTot/vFrete/text()',
                   ValorIPI    varchar2(15)  PATH '/infNFe/total/ICMSTot/vIPI/text()',
                   ValorPIS    varchar2(15)  PATH '/infNFe/total/ICMSTot/vPIS/text()',
                   ValorCOFINS varchar2(15)  PATH '/infNFe/total/ICMSTot/vCOFINS/text()',
                   ValorNota   varchar2(15)  PATH '/infNFe/total/ICMSTot/vNF/text()',
                   Adicionais  varchar2(2000) PATH '/infNFe/infAdic/infCpl/text()',
                   TipoFrete   varchar2(2)   PATH '/infNFe/transp/modFrete/text()',                   
                   Peso        varchar2(15)  PATH '/infNFe/transp/vol/pesoB/text()'
    ) tabela_xml_extract
where registro = ViRegistro) loop

  ----------*****-----

end loop;

Att,
Marlon

[shinobyghost]

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 post, mas deram erro...
Muito obrigado pela atenção de todos...

[Preazito]

Boa tarde a todos,

Estou com uma dúvida na criação de uma consulta no Banco de Dados ORACLE.
Na tabela (t_parameters) no banco tem um campo (table_value) que contém informações em XML, e gostaria de trazer o valor mais atual do link de acordo com a velocidade.
P.S.: O campo com a data de criação do XML é "create_date".
Abaixo como está guardado a informação XML no campo da tabela.

Selecionar tudo

<Table>
	<TableStructure>
		<Col Name="Largura_Banda_Kbps" Index="Y" Mandatory="N" Type="Text" DefaultValue="" />
	<Col Name="Terrestre_Valor_Mensal" Index="N" Mandatory="N" Type="Number" DefaultValue="" />
	<Col Name="Terrestre_Valor_Instalacao" Index="N" Mandatory="N" Type="Number" DefaultValue="" />
	<Col Name="Satélite_Valor_Mensal" Index="N" Mandatory="N" Type="Number" DefaultValue="" />
	<Col Name="Satélite_Valor_Instalacao" Index="N" Mandatory="N" Type="Number" DefaultValue="" />
	</TableStructure>
	<TableElements>
		<Elem>
			<Largura_Banda_Kbps>500000 Kbps</Largura_Banda_Kbps>
			<Terrestre_Valor_Mensal>114908.1</Terrestre_Valor_Mensal>
			<Terrestre_Valor_Instalacao>8448.15</Terrestre_Valor_Instalacao>
			<Satélite_Valor_Mensal>0</Satélite_Valor_Mensal>
			<Satélite_Valor_Instalacao>0</Satélite_Valor_Instalacao>
		</Elem>
		<Elem>
			<Largura_Banda_Kbps>250000 Kbps</Largura_Banda_Kbps>
			<Terrestre_Valor_Mensal>70495.29</Terrestre_Valor_Mensal>
			<Terrestre_Valor_Instalacao>8448.15</Terrestre_Valor_Instalacao>
			<Satélite_Valor_Mensal>0</Satélite_Valor_Mensal>
			<Satélite_Valor_Instalacao>0</Satélite_Valor_Instalacao>
		</Elem>
		<Elem>
			<Largura_Banda_Kbps>100000 Kbps</Largura_Banda_Kbps>
			<Terrestre_Valor_Mensal>20395.4</Terrestre_Valor_Mensal>
			<Terrestre_Valor_Instalacao>1570.55</Terrestre_Valor_Instalacao>
			<Satélite_Valor_Mensal>0</Satélite_Valor_Mensal>
			<Satélite_Valor_Instalacao>0</Satélite_Valor_Instalacao>
		</Elem>
		<Elem>
			<Largura_Banda_Kbps>68000 Kbps</Largura_Banda_Kbps>
			<Terrestre_Valor_Mensal>14512.71</Terrestre_Valor_Mensal>
			<Terrestre_Valor_Instalacao>1570.55</Terrestre_Valor_Instalacao>
			<Satélite_Valor_Mensal>0</Satélite_Valor_Mensal>
			<Satélite_Valor_Instalacao>0</Satélite_Valor_Instalacao>
		</Elem>
		<Elem>
			<Largura_Banda_Kbps>32000 Kbps</Largura_Banda_Kbps>
			<Terrestre_Valor_Mensal>12113.11</Terrestre_Valor_Mensal>
			<Terrestre_Valor_Instalacao>1570.55</Terrestre_Valor_Instalacao>
			<Satélite_Valor_Mensal>0</Satélite_Valor_Mensal>
			<Satélite_Valor_Instalacao>0</Satélite_Valor_Instalacao>
		</Elem>
		<Elem>
			<Largura_Banda_Kbps>16384 Kbps</Largura_Banda_Kbps>
			<Terrestre_Valor_Mensal>6759.65</Terrestre_Valor_Mensal>
			<Terrestre_Valor_Instalacao>1570.55</Terrestre_Valor_Instalacao>
			<Satélite_Valor_Mensal>0</Satélite_Valor_Mensal>
			<Satélite_Valor_Instalacao>0</Satélite_Valor_Instalacao>
		</Elem>
		<Elem>
			<Largura_Banda_Kbps>8192 Kbps</Largura_Banda_Kbps>
			<Terrestre_Valor_Mensal>4783.93</Terrestre_Valor_Mensal>
			<Terrestre_Valor_Instalacao>1570.55</Terrestre_Valor_Instalacao>
			<Satélite_Valor_Mensal>0</Satélite_Valor_Mensal>
			<Satélite_Valor_Instalacao>0</Satélite_Valor_Instalacao>
		</Elem>
		<Elem>
			<Largura_Banda_Kbps>4096 Kbps</Largura_Banda_Kbps>
			<Terrestre_Valor_Mensal>2414.06</Terrestre_Valor_Mensal>
			<Terrestre_Valor_Instalacao>1570.55</Terrestre_Valor_Instalacao>
			<Satélite_Valor_Mensal>0</Satélite_Valor_Mensal>
			<Satélite_Valor_Instalacao>0</Satélite_Valor_Instalacao>
		</Elem>
		<Elem>
			<Largura_Banda_Kbps>2048 Kbps</Largura_Banda_Kbps>
			<Terrestre_Valor_Mensal>1208.29</Terrestre_Valor_Mensal>
			<Terrestre_Valor_Instalacao>816.68</Terrestre_Valor_Instalacao>
			<Satélite_Valor_Mensal>13497.83</Satélite_Valor_Mensal>
			<Satélite_Valor_Instalacao>3726.65</Satélite_Valor_Instalacao>
		</Elem>
		<Elem>
			<Largura_Banda_Kbps>1024 Kbps</Largura_Banda_Kbps>
			<Terrestre_Valor_Mensal>823.24</Terrestre_Valor_Mensal>
			<Terrestre_Valor_Instalacao>816.68</Terrestre_Valor_Instalacao>
			<Satélite_Valor_Mensal>9145.01</Satélite_Valor_Mensal>
			<Satélite_Valor_Instalacao>3726.65</Satélite_Valor_Instalacao>
		</Elem>
		<Elem>
			<Largura_Banda_Kbps>512 Kbps</Largura_Banda_Kbps>
			<Terrestre_Valor_Mensal>641.77</Terrestre_Valor_Mensal>
			<Terrestre_Valor_Instalacao>816.68</Terrestre_Valor_Instalacao>
			<Satélite_Valor_Mensal>6909.25</Satélite_Valor_Mensal>
			<Satélite_Valor_Instalacao>3726.65</Satélite_Valor_Instalacao>
		</Elem>
	</TableElements>
</Table>

O select que eu criei está me dando erro e gostaria da ajuda de vocês da criação da consulta.
Segue abaixo a minha consulta que está dando erro:

Selecionar tudo

SELECT 
  EXTRACTVALUE(VALUE(ELEM), '/Elem/Terrestre_Valor_Mensal') VALOR
FROM
  t_parameters param,
  TABLE(XMLSEQUENCE(EXTRACT(param.table_value, '/Table/TableElements/Elem'))) ELEM
WHERE
  param.parameter_name = 'Lote 1/Item 1'
  AND param.table_value IS NOT NULL
  AND param.parameter_id = 1880

[dcp]

Eu sei que o post é bem antigo, mas alguém conseguiu solucionar a duvida final do nosso amigo Nelson ?
quando ele diz que quando eletento juntar a NRONNF com o PESO (por exemplo) o resultado fica duplicado. ??
estou com o mesmo problema.