Banner Hospedagem de Sites
04/04/2020

Gerando XML no Banco de Dados ORACLE

Gerando XML no Banco de Dados ORACLE, vem dentro de uma tendência que muitos profissionais se depararam principalmente em processos de migração entre sistemas.

Posto que, o Intercâmbio Eletrônico de Dados, do inglês Electronic Data Interchange (EDI), é uma das formas mais tradicionais encontradas para realizar esse processo.

Tendo como um dos meios a utilização, arquivos XML.

Exemplo

Por certo, um exemplo, está relacionado as implantações e migrações dos sistemas Sistema de Gestão Empresarial, ou Enterprise Resource Planning (ERPs).

Visto que, estes tem a necessidade da troca de dados estruturada com os sistemas legados.

Neste caso, este artigo, Gerando XML no Banco de Dados ORACLE pode auxiliar em muito nesse processo.

Arquivo padronizado XML

O XML (eXtensible Markup Language) é uma linguagem de marcação, recomendada pela World Wide Web Consortium (W3C), utilizada para a geração de documentos com dados organizados hierarquicamente.

Entretanto, nada mais é do que um texto formatado com tags (rótulos) de marcação para identificação dos dados, que são organizados de uma forma hierárquica, sendo muito semelhante a linguagem HTML.

Dentro da filosofia dos posts do Blog, que tem o intuito de abordar os tópicos de uma forma simples e prática, deixando para um segundo momento as nuances teóricas mais pesadas.

O objetivo e diferencial deste post será a geração de Arquivo XML por query SQL no Banco de Dados ORACLE usando apenas SELECT.

Com as opções do Oracle XML DB, que segue as funções do padrão do SQLX, disponível a partir da versão 9i Release 2.

Para aplicação deste estudo de caso é possível usar o ORACLE Live SQL, que é um serviço da Oracle destinado ao aprendizado de SQL e PL/SQL.

Ferramenta que dá  acesso a seus usuários a uma instância de uma base de dados Oracle (sem necessidade de instalação), inclusive com o modelo de exemplo HR (Human Resource) para testes.

Para usar o Oracle Live SQL é necessário criar uma conta no site da Oracle, leia mais sobre clicando aqui.

Primeiramente, é importante definir a estrutura hierárquica que será utilizada na geração do XML.

Neste caso, será uma listagem de funcionários extraída do modelo de exemplo do ORACLE sobre recursos humanos – denominado de HR, onde cada funcionário poderá apresentar as seguintes marcações:

  • id (identificador do funcionário);
  • seu nome (concatenação do first_name e last_name),
  • o nome do departamento que está alocado;
  • o nome do cargo;
  • e seu salário.

Portanto, a representação [valor] apenas indica que estes espaços serão substituídos por cada dado de um determinado funcionário.

A tabela Employees quando relacionada a tabela Departments por Inner Join apresenta 106 registros (ou 106 funcionários).

Assim, teremos no XML 106 divisões <funcionario> </funcionario> com seus respectivos dados.

Lembrando, que cada marcação tem que ter sua abertura e seu fechamento, ou abre tag / fecha tag, exemplo: abre nome: <nome>, fecha nome: </nome>.

Abaixo a ilustração do modelo que será utilizado para o arquivo XML:

   <?xml version="1.0"?> 
   <funcionarios> 
      <funcionario> 
         <id>[valor]</id> 
         <nome>[valor]</nome> 
         <departamento>[valor]</departamento> 
         <cargo>[valor]</cargo> 
         <salario>[valor]</salario> 
       </funcionario> 
       ... 
    </funcionarios>
1o. passo:

Iniciando o passo a passo para a geração de Arquivo XML por query SQL no Banco de Dados ORACLE:

Primeiramente é montada a query (consulta) que retorna os dados previstos sem se preocupar com o formato XML, abaixo o SELECT com JOINs que retornam os dados que serão utilizados:

SELECT employee_id as id, first_name || last_name as nome, 
       department_name as departamento, job_title as cargo, 
       salary as salario 
  FROM hr.employees emp 
 INNER JOIN hr.departments dept 
    ON emp.department_id = dept.department_id  
 INNER JOIN hr.jobs job 
    ON emp.job_id = job.job_id;

A execução deste SELECT retorna 106 registros:

ID NOME DEPARTAMENTO CARGO SALARIO 

200 Jennifer Whalen Administration Administration Assistant 4400 

202 PatFay Marketing Marketing Representative 6000 

201 Michael Hartstein Marketing Marketing Manager 13000 

114 DenRaphaely Purchasing Purchasing Manager 11000 

... 106 registros
2o. passo:

Adicionar à query SELECT o XMLELEMENT, responsável em adicionar as tags de abre e fecha para cada valor, entre aspas vem o título da tag e posteriormente separado pela vírgula vem o campo do respectivo valor.

O apelido das colunas pode ou não ser mantido, lembrando que este pode ter um nome diferente ao da tag, mas prevalecerá o nome da tag na geração do XML:

SELECT XMLELEMENT("id", employee_id) as id, 
       XMLELEMENT("nome", first_name || last_name) as nome,      
       XMLELEMENT("departamento", department_name) as departamento,    
       XMLELEMENT("cargo", job_title) as cargo, 
       XMLELEMENT("salario", salary) as salario 
  FROM hr.employees emp 
  INNER JOIN hr.departments dept 
    ON emp.department_id = dept.department_id 
  INNER JOIN hr.jobs job 
    ON emp.job_id = job.job_id;

A execução deste SELECT trará o seguinte resultado:

ID NOME DEPARTAMENTO CARGO SALARIO 

<id>200</id> <nome>JenniferWhalen</nome> <departamento>Administration</departamento> <cargo>Administration Assistant</cargo> <salario>4400</salario> 

<id>202</id> <nome>PatFay</nome> <departamento>Marketing</departamento> <cargo>Marketing Representative</cargo> <salario>6000</salario> 

<id>201</id> <nome>MichaelHartstein</nome> <departamento>Marketing</departamento> <cargo>Marketing Manager</cargo> <salario>13000</salario> 

<id>114</id> <nome>DenRaphaely</nome> <departamento>Purchasing</departamento> <cargo>Purchasing Manager</cargo> <salario>11000</salario> 

... 106 registros
3o. passo:

Para criar a tag que abre e fecha cada funcionário, será necessário envolver todos os valores em um XMLELEMENT, nomeando essa tag de funcionarios:

SELECT '"' || XMLELEMENT("funcionario", 
         XMLELEMENT("id", employee_id) as id, XMLELEMENT("nome", 
                    first_name || last_name) as nome, 
         XMLELEMENT("departamento", department_name) as departamento,      
         XMLELEMENT("cargo", job_title) as cargo, 
         XMLELEMENT("salario", salary) as salario ) as funcionario     
   FROM hr.employees emp 
   INNER JOIN hr.departments dept 
     ON emp.department_id = dept.department_id 
   INNER JOIN hr.jobs job 
     ON emp.job_id = job.job_id;

A concatenação de uma aspas (‘”‘ ||) no início do SELECT é um artifício para que o resultado continue sendo exibido como texto, desconsidere esta do resutado.

Agora o retorno do SELECT será dado em uma única coluna, pois no momento que é criado o XMLELEMENT envolvendo os valores, os mesmos são aglutinados. Mas, ainda cada registro é retornado em uma linha individual:

FUNCIONARIO 

<funcionario><id>200</id><nome>JenniferWhalen</nome><departamento>Administration</departamento><cargo>Administration Assistant</cargo><salario>4400</salario></funcionario> <funcionario><id>202</id><nome>PatFay</nome><departamento>Marketing</departamento><cargo>Marketing Representative</cargo><salario>6000</salario></funcionario> <funcionario><id>201</id><nome>MichaelHartstein</nome><departamento>Marketing</departamento><cargo>Marketing Manager</cargo><salario>13000</salario></funcionario> <funcionario><id>114</id><nome>DenRaphaely</nome><departamento>Purchasing</departamento><cargo>Purchasing Manager</cargo><salario>11000</salario></funcionario> 

... 106 registros
4o. passo:

Agora é necessário aglutinar todas as linhas em uma só linha, obviamente mantendo cada registro com o abre e fecha da tag funcionario:

SELECT '"' || XMLAGG( 
        XMLELEMENT("funcionario", XMLELEMENT("id", employee_id) as id, 
        XMLELEMENT("nome", first_name || last_name) as nome, 
        XMLELEMENT("departamento", department_name) as departamento, 
        XMLELEMENT("cargo", job_title) as cargo, 
        XMLELEMENT("salario", salary) as salario ) )as funcionario 
  FROM hr.employees emp 
  INNER JOIN hr.departments dept 
    ON emp.department_id = dept.department_id 
  INNER JOIN hr.jobs job 
    ON emp.job_id = job.job_id;

Agora o resultado da execução do SELECT será apenas uma linha contendo todos os funcionários listados em uma única coluna e linha:

FUNCIONARIO 

<funcionario><id>200</id><nome>JenniferWhalen</nome><departamento>Administration</departamento><cargo>Administration Assistant</cargo><salario>4400</salario></funcionario><funcionario><id>202</id><nome>PatFay</nome><departamento>Marketing</departamento><cargo>Marketing Representative</cargo><salario>6000</salario></funcionario><funcionario><id>201</id><nome>MichaelHartstein</nome><departamento>Marketing</departamento><cargo>Marketing Manager</cargo><salario>13000</salario></funcionario><funcionario><id>114</id><nome>DenRaphaely</nome><departamento>Purchasing</departamento><cargo>Purchasing Manager</cargo><salario>11000</salario></funcionario> ...

1 registro
5o. passo:

Por conseguinte, neste passo, é necessário adicionar um XMLELEMENT por fora do XMLAGG, para que seja criada a abertura e fechamento da tag de listagem de funcionários, no caso denominada de funcionarios:

SELECT '"' || XMLELEMENT("funcionarios", 
        XMLAGG( XMLELEMENT("funcionario", 
         XMLELEMENT("id", employee_id) as id, XMLELEMENT("nome", 
                    first_name || last_name) as nome, 
         XMLELEMENT("departamento", department_name) as departamento, 
         XMLELEMENT("cargo", job_title) as cargo, 
         XMLELEMENT("salario", salary) as salario ) ) )as funcionario 
  FROM hr.employees emp 
  INNER JOIN hr.departments dept 
    ON emp.department_id = dept.department_id 
  INNER JOIN hr.jobs job 
    ON emp.job_id = job.job_id;

O resultado da aplicação deste SELECT será idêntico ao anterior, acrescido da tag de abertura e fechamento da listagem funcionarios, em uma única linha e coluna:

FUNCIONARIO 

<funcionarios><funcionario><id>200</id><nome>JenniferWhalen</nome><departamento>Administration</departamento><cargo>Administration Assistant</cargo><salario>4400</salario></funcionario><funcionario><id>202</id><nome>PatFay</nome><departamento>Marketing</departamento><cargo>Marketing Representative</cargo><salario>6000</salario></funcionario><funcionario><id>201</id><nome>MichaelHartstein</nome><departamento>Marketing</departamento><cargo>Marketing Manager</cargo><salario>13000</salario></funcionario><funcionario><id>114</id><nome>DenRaphaely</nome><departamento>Purchasing</departamento><cargo>Purchasing Manager</cargo><salario>11000</salario></funcionario> ... </funcionarios> 

1 registro
6o. passo:

Agora, chegou o momento de fechar o arquivo colocando o cabeçalho do XML, a tag XMLROOT realiza esse processo, antes do seu fechamento é importante indicar o parâmetro VERSION, como ilustrado no SELECT a seguir:

SELECT '"' || XMLROOT( XMLELEMENT("funcionarios", 
        XMLAGG( XMLELEMENT("funcionario", 
         XMLELEMENT("id", employee_id) as id, XMLELEMENT("nome", 
                    first_name || last_name) as nome, 
         XMLELEMENT("departamento", department_name) as departamento,   
         XMLELEMENT("cargo", job_title) as cargo, 
         XMLELEMENT("salario", salary) as salario ) ) ) 
       , VERSION '1.0') as funcionario 
  FROM hr.employees emp 
  INNER JOIN hr.departments dept 
    ON emp.department_id = dept.department_id 
  INNER JOIN hr.jobs job 
    ON emp.job_id = job.job_id;

Mas, o resultado da execução deste último SELECT será acrescido cabeçalho do arquivo XML:

FUNCIONARIO 

<?xml version="1.0"?><funcionarios><funcionario><id>200</id><nome>JenniferWhalen</nome><departamento>Administration</departamento><cargo>Administration Assistant</cargo><salario>4400</salario></funcionario><funcionario><id>202</id><nome>PatFay</nome><departamento>Marketing</departamento><cargo>Marketing Representative</cargo><salario>6000</salario></funcionario><funcionario><id>201</id><nome>MichaelHartstein</nome><departamento>Marketing</departamento><cargo>Marketing Manager</cargo><salario>13000</salario></funcionario><funcionario><id>114</id><nome>DenRaphaely</nome><departamento>Purchasing</departamento><cargo>Purchasing Manager</cargo><salario>11000</salario></funcionario> ... </funcionarios> 

1 registro

Editando a coluna resultante no ORACLE SQL Developer será possível ver o arquivo XML no formato hierárquico visual, como ilustrado abaixo:

<?xml version="1.0"?> <funcionarios> <funcionario> <id>200</id> <nome>JenniferWhalen</nome> <departamento>Administration</departamento> <cargo>Administration Assistant</cargo> <salario>4400</salario> </funcionario> <funcionario> <id>202</id> <nome>PatFay</nome> <departamento>Marketing</departamento> <cargo>Marketing Representative</cargo> <salario>6000</salario> </funcionario> <funcionario> <id>201</id> <nome>MichaelHartstein</nome> <departamento>Marketing</departamento> <cargo>Marketing Manager</cargo> <salario>13000</salario> </funcionario> <funcionario> <id>114</id> <nome>DenRaphaely</nome> <departamento>Purchasing</departamento> <cargo>Purchasing Manager</cargo> <salario>11000</salario> </funcionario> <funcionario> ... </funcionarios>

1 registro
7o. passo:

Em outras palavras, é só copiar o conteúdo XML gerado para um editor comum de textos e salvar o arquivo com um nome com extensão XML.

Resumindo, o que em uma linguagem de programação tradicional, levaríamos horas para programar, fazendo diretamente a geração de Arquivo XML por query SQL fazemos em minutos.

Só para ilustrar, isso reflete na variável tempo de desenvolvimento que muitas vezes interfere diretamente na variável custo do projeto.

Contudo, a biblioteca apresentada contém mais recursos do que os que foram necessários para realizar esse  handson.

Em conclusão, para quem quiser se aprofundar, existe uma excelente documentação no próprio site da ORACLE.

Com isso, esperamos que o conteúdo tenha atendido às expectativas.

Clique aqui, para ler a postagem que explica os tipos de conteúdos que serão tratados no Blog.

Share

Luis Alexandre da Silva

Professor e Consultor de tecnologia em desenvolvimento de sistemas.Possui mestrado em Ciência da Computação pela UNESP (2016), especialização em Gestão Integrada de Pessoas e Sistemas de Informação pela FIB (2008) e graduação em Análise de Sistemas pela Universidade do Sagrado Coração (1997).Por fim, tem experiência em Gerenciamento de Projetos, Linguagens de Programação e Banco de Dados. Atuando principalmente nos seguintes temas: ensino, gerenciamento de projetos, ITIL, Desenvolvimento WEB e processos BPO.

Você pode querer ver também...