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 hands–on.
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.