Arquivo XML por query SQL no Banco de Dados ORACLE

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

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 de arquivos XML.

Exemplo

Um exemplo, está relacionado as implantações e migrações dos sistemas Sistema de Gestão Empresarial, ou Enterprise Resource Planning (ERPs), onde há a necessidade da troca de dados estruturada com os sistemas legados.

Neste caso, a geração de Arquivo XML por query SQL no Banco de Dados ORACLE pode auxiliar em muito nesse processo.

Conceito do Blog

Como a filosofia dos posts do Blog DesComp – Descomplicando a Computação é mostrar o como fazer (prática) de uma forma simples e sem se preocupar no aprofundamento teórico em um primeiro momento, vamos ao handson.

XML SQL no ORACLEBram Naus

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 DesComp, 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.

Primeiramente, é importante definir a estrutura hierárquica que será utilizada na geração do XML, 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.

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 employees emp
INNER JOIN departments dept
ON emp.department_id = dept.department_id
INNER JOIN 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 employees emp
INNER JOIN departments dept
ON emp.department_id = dept.department_id
INNER JOIN 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 employees emp
INNER JOIN departments dept
ON emp.department_id = dept.department_id
INNER JOIN jobs job
ON emp.job_id = job.job_id;

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 employees emp
INNER JOIN departments dept
ON emp.department_id = dept.department_id
INNER JOIN 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:

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 employees emp
INNER JOIN departments dept
ON emp.department_id = dept.department_id
INNER JOIN 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:

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 employees emp
INNER JOIN departments dept
ON emp.department_id = dept.department_id
INNER JOIN 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.

Sobre tudo, 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.

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

Esperamos que o conteúdo tenha atendido às expectativas.

Professor e Consultor de TI, especialidades voltadas à Banco de Dados, Modelagem de Dados, Desenvolvimento de Sistemas voltados a WEB e Aprendizado de Máquina. Possui mestrado em Ciência da Computação, especialização em Gestão Integrada de Pessoas e Sistemas de Informação e graduação em Análise de Sistemas.