Select For XML Path SQL Server

Uso de For XML Path en SQL Server

Una consulta Select (Ver Select) se puede mostrar en formato XML (Ver Campos XML), utilizando la opción FOR XML, la cual tiene varios modos, uno de estos es el modo PATH.

Presentando los registros usando For XML Path

  • Al usar esta opción se pueden extraer los datos de una consulta en formato XML, el que es muy necesario en muchas aplicaciones.
  • Los nombres de campos o los alias de la consulta se van a convertir en etiquetas XML
  • Proporciona una forma sencilla de mostrar los elementos de un campo de una tabla separados por un caracter que generalmente es la coma.
  • El modo PATH de la opción For XML es una forma sencilla de introducir un anidamiento adicional para representar propiedades complejas.

Ejercicios

Ejercicio 1

Usando la base de datos Northwind
use Northwind
go
Listar los proveedores (Suppliers) en formato XML
Select * from Suppliers for xml path
go
El resultado se muestra en la siguiente imagen
Note que es tiene formato XML

Al pulsar clic sobre el archivo se muestra el contenido de la tabla Suppliers (Proveedores) en formato XML, para cada registro de la tabla la opción For XML Path le incluye la etiqueta row.
El resultado se muestra en la siguiente imagen

Ejercicio 2

Mostrar las categorías
Select
C.CategoryID As ‘Código’,
C.CategoryName As ‘Categoría’,
C.Description As ‘Descripción’
from Categories As C for xml path
go
Al pulsar clic en el enlace para abrir el archivo XML se muestran etiquetas XML de acuerdo a los alias utilizados en la búsqueda
El resultado se muestra en la siguiente imagen

Ejercicio 3

Mostrar los empleados, para cada registro se ha especificado la etiqueta Empleado, utilizando la cláusula Path.
Select
E.EmployeeID As ‘Código’,
E.LastName As ‘Apellido’,
E.FirstName As ‘Nombre’,
E.Country As ‘País’
from Employees As E for xml path(‘Empleado‘)
go
Al pulsar clic en el enlace para abrir el archivo XML se muestran etiquetas XML de acuerdo a los alias utilizados en la búsqueda, note que cada empleado tiene la etiqueta XML especificada en la cláusula Path. Si dentro de los paréntesis en la cláusula Path se especifica una cadena de caracteres vacia Path(»), los registros no se separan.
El resultado se muestra en la siguiente imagen

Ejercicio 4

Mostrar los productos, el código del productos se va a especificar como una propiedad de la etiqueta Producto.
select
P.ProductID As ‘@Código’,
P.ProductName As ‘Descripción’,
P.UnitPrice As ‘Precio’,
P.UnitsInStock As ‘Stock’
From Products As P
for xml path (‘Producto’)
go
El resultado se muestra en la siguiente imagen

En el listado anterior se puede incluir un nivel superior para agrupar los productos usando la cláusula Root
select
P.ProductID As ‘@Código’,
P.ProductName As ‘Descripción’,
P.UnitPrice As ‘Precio’,
P.UnitsInStock As ‘Stock’
From Products As P
for xml path (‘Producto’), root (‘Productos’)
go
El resultado se muestra en la siguiente imagen

Ejercicio 5

Este ejercicio va a mostrar como usar la opción For XML Path para generar un conjunto de valores separados por coma con los datos de una columna. El diagrama siguiente muestra la programación de cursos de capacitación de un sistema para un centro de certificación.

Create database Capacitacion
go
use Capacitacion
go
Create table Cursos
(
Codigo nchar(5),
Descripcion nvarchar(50) not null,
Ambiente nvarchar(50),
HoraInicio Time,
CantidadHoras Numeric(9,2),
DiaSemana nvarchar(10),
constraint CursosPK primary key (Codigo)
)
go
Insertar los cursos
insert into Cursos values
(‘SS005′,’SQL Server DBA’,’Aula DBA’,’8:00′,4,’Lunes’),
(‘SS151′,’SQL Server DBA’,’Aula DBA’,’8:00′,4,’Miércoles’),
(‘SS645′,’SQL Server DBA’,’Aula DBA’,’8:00′,4,’Viernes’),
(‘SS890′,’SQL Server DBA’,’Aula DBA’,’8:00′,4,’Sábado’),
(‘OE045′,’Experto MOS 2019′,’Aula Office’,’4:00′,3,’Martes’),
(‘OE109′,’Experto MOS 2019′,’Aula Office’,’4:00′,3,’Jueves’),
(‘OE456′,’Experto MOS 2019′,’Aula Office’,’4:00′,3,’Sábado’)
go

El listado de los cursos es como sigue
select * from Cursos
go
El resultado se muestra en la siguiente imagen

Se desea mostrar las ofertas de los cursos y los días de la semana separados por coma.
Para esto se va a usar una subconsulta (Ver SubConsultas) de los días de la semana separados por coma. La función Stuff quita la coma al inicio del resultado (Ver funciones de texto)
select Stuff((select ‘,’+ C.DiaSemana
from Cursos As C for xml path(»)),1,1,») As ‘Día’
go
El resultado se muestra en la siguiente imagen

Ahora vamos a listar los cursos con los días de la semana
select distinct
C.Descripcion, C.Ambiente,
C.HoraInicio ,C.CantidadHoras,
(select Stuff((select ‘,’+DiaSemana from Cursos As D
where D.Descripcion = C.Descripcion
for xml path(»)),1,1,»)) As ‘Días’
from Cursos As C
go
El resultado se muestra en la siguiente imagen
Note que los días de la semana que se ofertan los cursos aparecen separados por coma.

Ejercicio 6

Usando la base de datos Northwind
use Northwind
go
Listar los empleados y las órdenes generadas. (Ver Joins)
Select Distinct
E.LastName As ‘Apellidos’, E.FirstName As ‘Nombre’,
Stuff((Select ‘,’+ trim(Str(O.OrderID)) from Orders As O
where O.EmployeeID = E.EmployeeID
For XML path(»)),1,1,») As ‘Ordenes’
from Employees As E
join Orders As O on E.EmployeeID = O.EmployeeID
go
El resultado se muestra en la siguiente imagen

Ejercicio 7

Listar los Proveedores y los productos que provee
Select Distinct
S.CompanyName As ‘Proveedor’, S.Country As ‘País’,
Stuff((Select ‘,’+ P.ProductName from Products As P
where P.SupplierID= S.SupplierID
For XML path(»)),1,1,») As ‘Productos’
from Suppliers As S
join Products As P on S.SupplierID= P.SupplierID
order by Proveedor
go
El resultado se muestra en la siguiente imagen

Esto se puede mostrar también usando la función String_agg de SQL Server 2017 (Ver Funciones Trim, Concat_ws y String_agg)
Select Distinct
S.CompanyName As ‘Proveedor’, S.Country As ‘País’,
String_agg(P.ProductName,’,’)
within group (order by P.ProductName asc)
As ‘Productos’
from Suppliers As S
join Products As P on S.SupplierID= P.SupplierID
Group by S.CompanyName, S.Country
order by Proveedor
go
El resultado es idéntico al de la orden anterior.

Si compara los costos usando el plan de ejecución estimado puede notar que para este ejemplo es algo más rápido el uso de String_agg.

Costo usando For XML Path
Costo usando String_agg