Campos XML en SQL Server

Como usar campos XML en SQL Server

Los datos de tipo XML son muy efectivos cuando se manejan correctamente en las tablas. Se pueden usar campos de tipo XML para poder almacenar en la misma tabla datos similares a un Maestro – Detalle sin necesidad de otra tabla.



Uso de campos XML en SQL Server

Para el manejo de los datos en campos XML vamos a utilizar los datos de las tablas Empleados, Clientes, Productos, Ordenes y Detalle de Órdenes y crear nuetra base de datos.  Para que el código siguiente funciones deben asegurarse que esté instalada la base de datos Northwind.

El diagrama de la base de datos que se creará para el ejemplo se muestra en la siguente imagen.

El código para generar el diagrama es el siguiente:

Create database PruebaXML
go

Use PruebaXML
go

Clientes
Create table Clientes
(
ClientesCodigo nchar(5),
ClientesNombre nvarchar(100),
ClientesDireccion nvarchar(200)
constraint ClientesCodigoPK primary key (ClientesCodigo)
)
go
insert into Clientes
select C.CustomerID, C.CompanyName, C.Address from Northwind.dbo.Customers As C
go
Empleados
Create table Empleados
(
EmpleadosCodigo int,
EmpleadosNombre nvarchar(100),
EmpleadosDireccion nvarchar(200)
constraint EmpleadosCodigoPK primary key (EmpleadosCodigo)
)
go
insert into Empleados
select E.EmployeeID, E.LastName + Space(1) + E.FirstName,
E.Address from Northwind.dbo.Employees As E
go
Ordenes
Create table Ordenes
(
OrdenesNumero int,
ClientesCodigo nchar(5),
EmpleadosCodigo int,
OrdenesFecha DateTime,
OrdenesMontoTotal Numeric(9,2),
OrdenesDetalle XML
constraint OrdenesPK Primary key (OrdenesNumero),
constraint OrdenesClientesFK Foreign key (ClientesCodigo)
references Clientes(ClientesCodigo),
constraint OrdenesEmpleadosFK Foreign key (EmpleadosCodigo)
references Empleados(EmpleadosCodigo)
)
go
insert into Ordenes
select O.OrderID, O.CustomerID, O.EmployeeID, O.OrderDate,
Sum(D.Quantity*D.UnitPrice), ”
from Northwind.dbo.Orders As O
join Northwind.dbo.[Order Details] As D on O.OrderID = D.OrderID
Group by O.OrderID, O.CustomerID, O.EmployeeID, O.OrderDate
go
Productos
Create table Productos
(
ProductosCodigo int,
ProductosDescripcion nvarchar(100),
ProductosPrecio Numeric(9,2),
ProductosStock Numeric(9,2),
ProductosUnidad nvarchar(50),
constraint ProductosPK primary key (ProductosCodigo)
)
go
insert into Productos
select P.ProductID, P.ProductName, P.UnitPrice, P.UnitsInStock, P.QuantityPerUnit
from Northwind.dbo.Products As P
go
Detalle
Create table DetalleOrdenes
(
OrdenesNumero int,
ProductosCodigo int,
DetalleOrdenesPrecioVenta Numeric(9,2),
DetalleOrdenesCantidadVendida Numeric(9,2),
DetalleOrdenesImporte As DetalleOrdenesPrecioVenta*DetalleOrdenesCantidadVendida,
constraint DetalleOrdenesPK Primary key (OrdenesNumero,ProductosCodigo ),
constraint DetalleOrdenesFK Foreign key (OrdenesNumero)
references Ordenes(OrdenesNumero),
constraint DetalleProductosFK Foreign key (ProductosCodigo)
references Productos(ProductosCodigo)
)
go
insert into DetalleOrdenes
(OrdenesNumero, ProductosCodigo, DetalleOrdenesPrecioVenta, DetalleOrdenesCantidadVendida)
select D.OrderID, D.ProductID, D.UnitPrice, D.Quantity
from Northwind.dbo.[Order Details] As D
go

Vamos a incluir los registros de la tabla DetalleOrdenes en el campo OrdenesDetalle de la tabla Ordenes

Listado del detalle en formato XML

select D.OrdenesNumero, D.ProductosCodigo, D.DetalleOrdenesPrecioVenta,
D.DetalleOrdenesCantidadVendida, D.DetalleOrdenesImporte
from DetalleOrdenes As D for xml auto
go

Actualizar la tabla Ordenes con los datos de DetalleOrdenes

Update O set O.OrdenesDetalle =
(select D.OrdenesNumero, D.ProductosCodigo, D.DetalleOrdenesPrecioVenta,
D.DetalleOrdenesCantidadVendida, D.DetalleOrdenesImporte
from DetalleOrdenes As D
where O.OrdenesNumero = D.OrdenesNumero for xml auto)
from Ordenes As O
join DetalleOrdenes As D on O.OrdenesNumero = D.OrdenesNumero
go

Ver los datos
select * from Ordenes
go

Los registros se muestran como la siguiente imagen, note que hay un campo en formato XML.

Como convertir los datos XML en formato de Tabla

Para presentar los datos de un campo XML en el formato tabla se tendrá que usar variables  de tipo XML.

Por ejemplo, para ver el detalle de la orden 10248

Declare @DetalleOrden XML
Set @DetalleOrden = (select O.OrdenesDetalle from Ordenes As O where O.OrdenesNumero = 10248)
select
OrdenesNumero = Detalle.Columna.value (‘@OrdenesNumero’, ‘int’),
ProductosCodigo = Detalle.Columna.value (‘@ProductosCodigo’,’int’),
DetalleOrdenesPrecioVenta = Detalle.Columna.value (‘@DetalleOrdenesPrecioVenta’,’Numeric(9,2)’),
DetalleOrdenesCantidadVendida = Detalle.Columna.value (‘@DetalleOrdenesCantidadVendida’,’Numeric(9,2)’),
DetalleOrdenesImporte = Detalle.Columna.value (‘@DetalleOrdenesImporte’,’Numeric(9,2)’)
from @DetalleOrden.nodes(‘D’) As Detalle(Columna)
go

El resultado se muestra en formato de tabla como en la siguiente imagen

Como usar OpenXML en SQL Server

La funcionalidad de OpenXML permite extraer los datos de formato XML a un formato de tabla.

Usando los dependientes de un seguro en formato XML

Declare @Dependientes XML, @i int
Set @Dependientes =
‘<Dependientes>
<Dependiente Codigo=”5285″ Parentesco=”Esposa” Nombre =”Carla” FechaNacimiento=”01/02/1975″ />
<Dependiente Codigo=”7852″ Parentesco=”Hijo” Nombre =”Marco” FechaNacimiento=”24/02/1997″ />
<Dependiente Codigo=”4785″ Parentesco=”Hija” Nombre =”Liliana” FechaNacimiento=”10/02/1999″ />
</Dependientes>’
Execute sp_xml_preparedocument @i output, @Dependientes
Select * from OpenXML(@i, ‘/Dependientes/Dependiente’)
with (
Codigo int , Parentesco nvarchar(50) ,
Nombre nvarchar(50) , FechaNacimiento Date
)
go



El resultado se muestra en la siguiente imagen

Mostrando los datos de un archivo XML

Usando el archivo de Mascotas ubicado en D:\DatosXML que tiene la siguiente estructura

<Mascotas>
<Mascota>
<Codigo>869</Codigo>
<Nombre>Aristides</Nombre>
<Tipo>Gallo</Tipo>
<Fecha>15/09/2014</Fecha>
</Mascota>
<Mascota>
<Codigo>887</Codigo>
<Nombre>Flash</Nombre>
<Tipo>Tortuga</Tipo>
<Fecha>18/07/2015</Fecha>
</Mascota>
<Mascota>
<Codigo>9980</Codigo>
<Nombre>Pluto</Nombre>
<Tipo>Perro</Tipo>
<Fecha>18/07/2015</Fecha>
</Mascota>
</Mascotas>

Utilizando OpenRowSet para leer los datos en una variable

Declare @DatosMascotas XML, @i int
Select @DatosMascotas = M from OpenRowSet(Bulk ‘D:\DatosXML\Mascotas.XML’, Single_Blob) As Mascotas(M)
Execute sp_xml_preparedocument @i output, @DatosMascotas
Select * from OpenXML(@i, ‘/Mascotas/Mascota’,2)
with (
Codigo int ,
Nombre nvarchar(50) ,
Tipo nvarchar(50) ,
Fecha Date
)
go

El resultado se muestra en la siguiente imagen