Objetivos
- Conocer la función LISTAGG.
- Conocer la función XMLAGG.
- Conocer sus similitudes y diferencias.
- Ver ejemplos útiles.
Función LISTAGG
La función agregada LISTAGG de Oracle permite concatenar los valores de múltiples filas en una sola cadena de texto, separándolos mediante un delimitador especificado y respetando un orden definido. En esencia, se utiliza para transformar varios registros relacionados en una única representación textual dentro de una sola fila.
Syntaxis:
LISTAGG (measure_expr [, 'delimiter'] [ON OVERFLOW {ERROR | TRUNCATE}])
WITHIN GROUP (ORDER BY order_by_clause)
[OVER (query_partition_clause)]En la sintaxis:
- measure_expr: Expresión o columna cuyos valores serán concatenados.
- delimiter: Cadena opcional que separa los valores concatenados.
- ON OVERFLOW: Define el comportamiento cuando el resultado excede el tamaño permitido:
- ERROR: Genera un error (comportamiento por defecto).
- TRUNCATE: Trunca el resultado evitando el error.
- WITHIN GROUP (ORDER BY …): Especifica el orden en que se concatenan los valores.
- OVER (query_partition_clause): Convierte LISTAGG en función analítica, aplicándola por particiones.
La cláusula ON OVERFLOW permite controlar la ocurrencia de la excepción ORA-01489, que se produce cuando la cadena resultante supera el límite máximo permitido para el tipo de dato retornado. Mediante esta cláusula es posible evitar el error y, en su lugar, truncar el resultado generado por la función.
Ejemplo:
SELECT
e.department_id AS departamento
, COUNT(e.first_name) AS cantidad_empleados
, LISTAGG(
e.first_name,
', '
) WITHIN GROUP( ORDER BY e.first_name) AS empleados_x_dept
FROM hr.employees e
GROUP BY e.department_id
ORDER BY cantidad_empleados DESC;
La consulta retorna el listado de empleados agrupados por el departamento al que pertenecen. Para cada departamento se muestra la cantidad total de empleados y una lista concatenada con sus nombres, ordenados alfabéticamente y separados por comas mediante la función LISTAGG.
SELECT
empleo
, COUNT(1) AS cantidad
, LISTAGG(detalle_empleado, ';' ON OVERFLOW TRUNCATE '**')
WITHIN GROUP (ORDER BY empleo) AS detalle_empleado
FROM
(
SELECT
j.job_title AS empleo,
'|Código: ' ||e.employee_id||'|'||
'Nombre: ' ||e.first_name||' '||e.last_name||'|'||
'Correo: ' ||e.email||'|'||
'Telefono: ' ||e.phone_number||'|'||
'Salario: ' ||e.salary||'|'||
'Departamento: ' ||d.department_name||'|'||
'Dirección: ' ||l.street_address||', '||l.city||', '||
l.state_province||', '||l.postal_code||', '||c.country_name
AS detalle_empleado
FROM
hr.employees e
LEFT JOIN hr.departments d
ON (d.department_id = e.department_id)
LEFT JOIN hr.jobs j
ON (j.job_id = e.job_id)
LEFT JOIN hr.locations l
ON (l.location_id = d.location_id)
LEFT JOIN hr.countries c
ON (c.country_id = l.country_id)
)
GROUP BY empleo
ORDER BY cantidad DESC
;En la consulta anterior se combinan múltiples tablas del esquema HR para obtener información detallada de los empleados, incluyendo datos personales, laborales y de ubicación. Posteriormente, los registros son agrupados por tipo de empleo, calculando la cantidad de empleados por puesto y generando una cadena concatenada con todos sus detalles mediante la función LISTAGG.
Nótese el uso de la cláusula ON OVERFLOW TRUNCATE ‘‘**, la cual permite controlar el tamaño del resultado generado por LISTAGG. Si esta cláusula se omitiera y la cadena concatenada excediera el límite máximo permitido, se produciría la excepción ORA-01489: result of string concatenation is too long.
Gracias a esta opción, en lugar de generarse un error, el resultado es truncado automáticamente, agregando el indicador especificado ('**') al final de la cadena.
Función XMLAGG
La función XMLAGG es una función agregada de Oracle que permite combinar múltiples valores XML provenientes de varias filas en un único documento XML. Su uso más común consiste en agregar fragmentos XML generados por la función XMLELEMENT, permitiendo consolidar información relacionada dentro de una sola estructura XML.
A diferencia de LISTAGG, que retorna una cadena de texto, XMLAGG produce un resultado de tipo XMLType, ofreciendo mayor flexibilidad para manejar grandes volúmenes de datos concatenados sin las limitaciones de tamaño tradicionales de VARCHAR2.
Sintaxis
XMLAGG ( value_expression [ORDER BY order_by_clause] )En la Sintaxis:
- value_expression: Expresión XML que será agregada. Normalmente se construye mediante la función
XMLELEMENT. - ORDER BY: Define el orden en que los nodos XML serán concatenados dentro del resultado final.
El resultado de XMLAGG es un único objeto XML que contiene la combinación de todos los elementos generados por cada fila procesada.
Ejemplo:
SELECT
e.department_id AS departamento,
XMLAGG(
XMLELEMENT(
"empleado",
e.first_name || ' ' || e.last_name
)
ORDER BY e.first_name
) AS empleados_xml
FROM hr.employees e
GROUP BY e.department_id
ORDER BY departamento;
La anterior consulta agrupa los empleados por departamento y genera un documento XML con los nombres de los empleados pertenecientes a cada uno.
- XMLELEMENT crea un nodo XML por cada empleado.
- XMLAGG agrupa todos los nodos dentro de un solo resultado XML por departamento.
- El orden alfabético se controla mediante
ORDER BY.
SELECT
empleo,
COUNT(1) AS cantidad,
XMLAGG(
XMLELEMENT(
"empleado",
XMLELEMENT("codigo", employee_id),
XMLELEMENT("nombre", nombre),
XMLELEMENT("correo", email),
XMLELEMENT("salario", salary),
XMLELEMENT("departamento", department_name)
)
ORDER BY nombre
) AS detalle_empleados_xml
FROM
(
SELECT
j.job_title AS empleo,
e.employee_id,
e.first_name || ' ' || e.last_name AS nombre,
e.email,
e.salary,
d.department_name
FROM hr.employees e
LEFT JOIN hr.departments d
ON d.department_id = e.department_id
LEFT JOIN hr.jobs j
ON j.job_id = e.job_id
)
GROUP BY empleo
ORDER BY cantidad DESC;
Siguiendo el ejemplo LISTAGG, generamos un XML con toda la información del empleado agrupada por empleo.
Diferencia entre LISTAGG y XMLAGG
| Función | Tipo de salida | Límite de tamaño | Uso principal |
|---|---|---|---|
| LISTAGG | Texto (VARCHAR2/CLOB) | Limitado | Listas concatenadas |
| XMLAGG | XMLType | Mucho mayor | Estructuras XML complejas |
¿Cuándo usar LISTAGG vs XMLAGG?
Aunque ambas funciones permiten consolidar múltiples filas en un solo resultado, cada una está diseñada para escenarios distintos. Elegir correctamente entre LISTAGG y XMLAGG depende principalmente del tipo de salida requerida y del volumen de información a procesar.
Use LISTAGG cuando:
- Cuando necesitas texto plano.
- Se requiera una lista simple separada por delimitadores.
- El resultado será mostrado directamente al usuario.
- Se necesite una representación compacta en formato texto.
- El tamaño del resultado sea relativamente pequeño o controlado mediante
ON OVERFLOW.
Use XMLAGG cuando:
- Se necesite generar datos estructurados.
- El volumen concatenado pueda ser grande.
- Se requiera evitar limitaciones de tamaño de VARCHAR2.
- Los datos serán exportados o integrados con otros sistemas.
- Se necesite transformar posteriormente a XML, JSON u otros formatos.
Ejemplo:
<empleado>
<nombre>Steven King</nombre>
<salario>24000</salario>
</empleado>Comparación rápida:
| Característica | LISTAGG | XMLAGG |
|---|---|---|
| Tipo de resultado | Texto | XMLType |
| Formato | Plano | Estructurado |
| Control de orden | Sí | Sí |
| Límite de tamaño | Limitado | Mucho mayor |
| Manejo de overflow | ON OVERFLOW | No requerido |
| Complejidad | Baja | Media |
La función LISTAGG ofrece una solución simple y directa para concatenar valores en formato textual, mientras que XMLAGG proporciona una alternativa más robusta y flexible para construir estructuras complejas y manejar grandes volúmenes de información.
En términos prácticos, LISTAGG es ideal para presentación de datos, mientras que XMLAGG está orientada a procesamiento e integración estructurada.