Leer campos XML en SQL Server

Como usar campos XML en SQL Server

Los datos de tipo XML son muy efectivos cuando se guardan 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

Se va a crear una nueva base de datos, luego crear tablas con los datos de Northwind, para esto necesitamos los datos de Clientes, Empleados, Productos, Ordenes y Detalle. La información de la tabla Detalle va a ser almacenada en un campo XML en la tabla Ordenes.

Codigo para crear la Base de datos y las tablas

Create database PruebaXML
go

Use PruebaXML
go

Tabla 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

Tabla 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, note que en la tabla Órdenes el detalle de la orden se encuentra en un campo XML
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

Resaltado en negrita el campo XML, inicialmente no se va a insertar datos, luego se actualizará.
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

Tabla 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

Tabla 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

El diagrama generado en las líneas previas es como se ve en la imagen siguiente

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 que contiene el detalle de cada orden.

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

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

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