Ler XML com Oracle á partir de uma URL
Escrito por Pedroneto, em Oracle - 11/02/2012
1
Depois de muito pesquisar na internet com o Thiago Henrique (que trabalha comigo na Micromap), conseguimos criar uma SQL no Oracle para ler um XML à partir de uma URL, sem precisar estar armazenado no servidor.
Com esta função, não precisa necessariamente ser XML o arquivo, a SQL poderá ler qualquer retorno, podendo ser texto por exemplo.
Vamos utilizar como exemplo um XML simples, segue o conteúdo:
-
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
-
<cliente>
-
<nome>PEDRO NETO</nome>
-
<idade>21 anos</idade>
-
<cidade>santa cruz do rio pardo</cidade>
-
<empresas>
-
<empresa>
-
<nome>Micromap</nome>
-
<cidade>Santa Cruz do Rio Pardo</cidade>
-
</empresa>
-
<empresa>
-
<nome>Vietra</nome>
-
<cidade>Florianópolis</cidade>
-
</empresa>
-
<empresa>
-
<nome>Google</nome>
-
<cidade>Mountain View</cidade>
-
</empresa>
-
</empresas>
-
</cliente>
vamos começar pelo mais simples.
Para retornar o XML inteiro, seja para gravar em uma tabela, ou para ler com uma programação server-side (PHP, Java, ASP), utilize a seguinte select:
-
SELECT
-
HTTPURIType('http://www.site.com.br/teste.xml').getCLOB() retorno
-
FROM dual
no exemplo acima, o campo “retorno”, vai retornar o XML completo da página como um texto, que no nosso caso seria:
-
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><cliente><nome>PEDRO NETO</nome> …
caso você colocasse o endereço www.google.com.br retornaria o código fonte html do google…
Explicação da SQL:
HTTPURIType: esta função le o endereço colocado, e o transforma em um HTTP URI TYPE, à partir daí, ele disponibilizará diversas funções, como podendo ver o tipo de documento, pegar o conteúdo do arquivo, etc…
getCLOB: esta função retornará o conteúdo (HTML, XML) do endereço setado…
FROM dual: é usado quando não tem necessidade de extrair os dados de uma tabela específica, mais informações…
Pegando um nó/tag do XML
mantendo o XML usado no exemplo acima, vamos pegar o conteúdo do nó “nome”, para retornar o nome do cliente.
Logo depois, neste mesmo post, estarei explicando uma forma melhor de estar retornando o valor de um nó do XML, mas para fins de aprendizado, segue abaixo um outro exemplo:
-
SELECT
-
EXTRACTVALUE(
-
sys.xmltype.createXML(
-
HTTPURIType( 'http://www.site.com.br/teste.xml' ).getClob()
-
)
-
, '/cliente/nome'
-
) nomeCliente
-
FROM dual
na sql acima, o campo “nomeCliente” retornaria: PEDRO NETO
Explicação da SQL:
EXTRACTVALUE: esta função, irá extractar o nó selecionado no segundo parâmetro. No primeiro parâmetro é colocado o conteúdo XML.
Pegando vários nós (loop) do XML
Como disse acima, há uma forma melhor de pegar os valores do XML, mas novamente, para fins de aprendizado, segue abaixo um exemplo menos prático.
A função EXTRACTVALUE, que usamos no último exemplo, extrai o valor de apenas um nó simples (filho), caso a solicitação seja em um nó pai (como o nó “empresas”, por exemplo), dará erro, então usaremos o EXTRACT, como o seguinte exemplo:
-
SELECT
-
EXTRACT(
-
sys.xmltype.createXML(
-
HTTPURIType( 'http://www.site.com.br/teste.xml' ).getClob()
-
), '/cliente/nome') nomeCliente
-
FROM dual
Melhor Solução
Agora explicarei um exemplo muito mais prático, que descobri enquanto escrevia este post.
Iremos tratar um XML, como se fosse uma tabela do oracle. Não criaremos tabelas, não criaremos tabelas temporárias, nem nada, mas no exemplo a seguir, a sql tratará o XML como se fosse realmente uma tabela, só que sem ela existir.
Segue abaixo:
-
SELECT
-
EXTRACTVALUE(column_value, '/empresa/nome') empresa,
-
EXTRACTVALUE(column_value, '/empresa/cidade') cidade
-
FROM
-
TABLE(
-
XMLSequence(
-
sys.xmltype.createXML(
-
HTTPURITYPE( 'http://micromap.com.br/portalcidadao/teste.xml' ).getClob()
-
).extract('/cliente/empresas/empresa')
-
)
-
) t
nesse caso, ele retornaria várias linhas com o nome de cada empresa e o nome da cidade.
| Empresa | Cidade |
|---|---|
| Micromap | Santa Cruz do Rio Pardo |
| Vietra | Florianópolis |
| Mountain View |
Explicação da SQL:
XMLSequence: Toma como entrada uma instância XMLType e retorna um array dos nós de nível superior na XMLType.
TABLE: Trata o XML como se fosse uma tabela do oracle
Problemas com acentos – Atualização 16/02/2012
O Yuri Menom (trabalha comigo na Micromap), levantou uma questão importante: Acentos.
O Banco aqui da empresa está com a codificação UTF-16, e um xml geralmente é UTF-8. Utilizaremos então a função CONVERT do ORACLE (função que serve para converter a codificação da string).
Mas ao usar somente o CONVERT para utf16, repare que ficam caracteres vazios entre cada letra. Esses caracteres são nulos (não são espaços, são nulos), então teremos que usar o REPLACE também. Segue o código utilizando os exemplos anteriores:
-
SELECT
-
EXTRACTVALUE(column_value, '/empresa/nome') empresa,
-
REPLACE(
-
CONVERT(
-
EXTRACTVALUE(column_value, '/empresa/cidade')
-
,'AL16UTF16'
-
,'UTF8'
-
),
-
CHR(0)
-
,''
-
) cidade
-
FROM
-
TABLE(
-
XMLSequence(
-
sys.xmltype.createXML(
-
HTTPURITYPE( 'http://micromap.com.br/portalcidadao/teste.xml' ).getClob()
-
).extract('/cliente/empresas/empresa')
-
)
-
) t
Repare que tivemos que usar o CHR(0) no REPLACE, pois, é um caracter nulo que queremos retirar, e não o caracter espaço (que seria CHR(32) )
Explicação da SQL:
CONVERT: ( string, codificação destino, codificação origem ) – Função usada para converter a codificação da string.
REPLACE: ( string, string à ser pesquisada, string substituida ) – Função para substituir uma string/caracter por outra.
CHR: ( número do caracter da tabela ASCII) – Função que retorna o caracter relacionado ao número da tabela ASCII que coloca em seu parâmetro.
Enfim,
espero ter ajudado,
qualquer dúvida ou sugestão, comentem…
Desenvolvedor Web, com criação de interface rica e com fácil usabilidade.
