Publicar datos SQL como XML
Puede utilizar las funciones XMLElement(), XMLConcat(), XMLAgg() y XMLAttributes() dentro de una expresión SQL para transformar los resultados de una consulta de bases de datos en XML.
Estas funciones XML se denominan con frecuencia funciones de publicación porque el objetivo
es convertir los datos almacenados en una base de datos relacional en XML que pueda
ponerse a disposición de otras aplicaciones, por ejemplo, servicios
web. La función principal a este respecto es XMLElement(),
que adopta dos argumentos: el nombre del elemento XML para crear y el contenido
de ese elemento. La siguiente sentencia de selección, que no consulta en realidad
una base de datos, resalta el uso de la función XMLElement():
select XMLElement('Parent', 'Parent Text');
Esta sentencia crea el siguiente XML:
<Parent>Parent Text</Parent>
La salida de la función ' XMLElement() ' es un valor de tipo XML, que es la representación compilada de Netezza Performance Server del elemento XML. Por lo tanto, si ha escrito la sentencia de selección anterior,
el resultado sería el XML de nombre de tipo:
XMLELEMENT
-----------
XML
(1 row)
Para ver el elemento XML real creado por la llamada a XMLElement(),
tendrá que ajustar la llamada XMLElement() con la función XMLSerialize(). A continuación se ofrece un ejemplo:
select XMLSerialize(XMLElement('Parent', 'Parent Text'));
XMLSERIALIZE
------------------------------
<Parent>Parent Text</Parent>
(1 row)
La potencia real de la función XMLElement()
es que las llamadas a la función se pueden anidar para generar la estructura jerárquica
que necesitan los datos XML. A continuación se ofrece un ejemplo:
select
XMLElement('Parent',
XMLElement('Child', 'Child text'));
Esta consulta genera el siguiente XML:
<Parent>
<Child>Child text</Child>
</Parent>
Las funciones de publicación se pueden anidar según sea necesario hasta un límite de
10.000 llamadas anidadas. A continuación se ofrece un ejemplo:
select
XMLElement('Parent',
XMLElement('Child',
XMLElement('GrandChild', 'Grandchild text')));
Esta consulta genera el siguiente XML:
<Parent>
<Child>
<GrandChild>Grandchild text</GrandChild>
</Child>
</Parent>
A modo de ejemplo más realista, supongamos que existe una tabla DEPARTMENTS
con tres columnas: DEPTNO, DEPTNAME y DEPTLOC:
DEPTNO DEPTNAME DEPTLOC
------ ---------- ---------
10 MARKETING BOSTON
20 HR BOSTON
30 SALES NEW YORK
40 ENGINEERING NEW YORK
La siguiente consulta SQL sin formato enumera
todos los departamentos:
select * from departments;
Pero suponga que necesita devolver las cuatro filas de datos de departamento como XML, donde hay un nodo <Dept> para cada departamento y cada nodo <Dept> contiene tres nodos hijo (<Número>, <Name> y <Location>), tal como se muestra en el siguiente documento XML:
<Departments>
<Dept>
<Number>10</Number>
<Name>MARKETING</Name>
<Location>BOSTON</Location>
</Dept>
<Dept>
<Number>20</Number>
<Name>HR</Name>
<Location>BOSTON</Location>
</Dept>
<Dept>
<Number>30</Number>
<Name>SALES</Name>
<Location>NEW YORK</Location>
</Dept>
<Dept>
<Number>40</Number>
<Name>ENGINEERING</Name>
<Location>NEW YORK</Location>
</Dept>
</Departments>
Para crear este documento XML, utilice una sentencia SELECT
similar a esta:
select
XMLElement('Departments', XMLAGG(
XMLElement('Dept', XMLConcat(
XMLElement('Number', d.deptno),
XMLElement('Name', d.deptname),
XMLElement('Location', d.deptloc)))))
from departments d;
En cada una de las dos primeras llamadas XMLElement(), el contenido del elemento se crea mediante una llamada a la función XML anidada. Para crear un documento XML estructurado de forma jerárquica de nodos principales y secundarios, anide las llamadas XMLElement() dentro de una sentencia SQL.
La primera función ' XMLElement() ' de la consulta crea el nodo de nivel superior <Departamentos>:
XMLElement('Departments', XMLAgg (
El agregado XMLAgg() se utiliza para el segundo argumento, indicando que el contenido para el nodo <Departamentos> de nivel superior es un grupo de nodos agregados, lo que significa que estos nodos serán nodos hijos de un único nodo padre.
La segunda llamada XMLElement() ' establece <Dept> como el nombre de cada nodo hijo del nodo padre <Departments> y luego depende de las siguientes tres llamadas XMLElement() ' incrustadas para el contenido de cada nodo hijo <Dept>:
XMLElement('Dept', XMLConcat(
XMLElement('Number', d.deptno),
XMLElement('Name', d.deptname),
XMLElement('Location', d.deptloc)))))
Estas tres llamadas ' XMLElement() ' incrustadas crean tantos nodos hijos <Dept> como sean necesarios para envolver las filas de datos que se devuelven de la tabla DEPARTMENTS.
Es importante comprender el uso de la función de agregado XMLAgg(). Este agregado combina nodos hijo bajo su nodo padre, lo que en el ejemplo anterior significa que hay un único nodo padre <Departments> que contiene los cuatro nodos <Dept>. Sin la llamada agregada ' XMLAgg() ', el XML que se produce contiene cuatro nodos <Departments>, cada uno de los cuales contiene un único nodo <Dept>, lo que da como resultado un documento XML no válido, como se muestra aquí:
<Departments>
<Dept>
<Number>10</Number>
<Name>MARKETING</Name>
<Location>BOSTON</Location>
</Dept>
</Departments>
<Departments>
<Dept>
<Number>20</Number>
<Name>HR</Name>
<Location>BOSTON</Location>
</Dept>
</Departments>
<Departments>
<Dept>
<Number>30</Number>
<Name>SALES</Name>
<Location>NEW YORK</Location>
</Dept>
</Departments>
<Departments>
<Dept>
<Number>40</Number>
<Name>ENGINEERING</Name>
<Location>NEW YORK</Location>
</Dept>
</Departments>
Esto no es una sintaxis XML válida porque hay cuatro instancias del elemento del documento <Departments>. Esto demuestra lo importante que es utilizar la función IsValidXML() para asegurarse de que el XML creado con la biblioteca de funciones se puede analizar como XML. Además, si va a utilizar esquemas, también será responsable de elaborar XML con formato correcto (XML que se ajuste a la estructura especificada por el esquema).
Otro ejemplo: supongamos que desea obtener una lista de empleados por
departamento, con las siguientes etiquetas:
<EmployeesByDepartment>
<Dept DeptNo=“10“>
<Name>ACCOUNTING</Name>
<Location>NEW YORK</Location>
<Employees>
<Employee EmpNo=“7782“>
<Name>CLARK</Name>
<Job>MANAGER</Job>
<Manager>7839</Manager>
<Salary>2450</Salary>
</Employee>
<Employee EmpNo=“7839“>
<Name>KING</Name>
<Job>PRESIDENT</Job>
<Salary>5000</Salary>
</Employee>
...
</Employees>
</Dept>
...
<EmployeesByDepartment>
Para obtener empleados por departamento, se necesitan dos sentencias select. En primer lugar, cree una agrupación de empleados y después agrupe a los empleados
por departamento:
CREATE temp table emp_grouping AS
SELECT deptno, XMLElement ('Employees', XMLAGG (
XMLElement ('Employee', XMLAttributes ('EmpNo', empno),
XMLConcat (
xmlelement ('name', name),
xmlelement ('job', job),
xmlelement ('manager', mgr),
xmlelement ('salary', sal),
xmlelement ('comm', comm)))))
AS xml FROM emp INNER JOIN dept
ON emp.deptno = dept.deptno
GROUP BY deptno;
SELECT XMLElement('EmployeesByDepartment', XMLAGG(
XMLElement('Dept', XMLAttributes('DeptNo', deptno), XMLConcat(
XMLElement('Name', D.DNAME),
XMLElement('Location', D.LOC),
emp_grouping.xml))))
FROM dept INNER JOIN emp_grouping
ON dept.deptno = emp_grouping.deptno;