Laboratorio 0009: Aplicaciones con modelos semiestructurados XML

publicado a la‎(s)‎ 14 ago 2012, 12:16 por Hernan Nina Hanco   [ actualizado el 4 jun 2013, 10:56 ]
I) OBJETIVOS
  • Utilizar un SGBD comercial u OpenSource escribir aplicaciones con modelos Semiestructurados.

II) MARCO CONCEPTUAL
Ver guía o presentaciones de la asignatura

III) PRÁCTICAS DE LABORATORIO

Ejemplos del uso de las Clausula FOR XML en SQL Server.
CREATE DATABASE TEST1
GO
USE TEST1
GO
-- CREATE TABLAS
CREATE TABLE cliente(
id_cliente int not null primary key,
nombres varchar(20),
)

CREATE TABLE venta (
id_venta int not null primary key,
fecha datetime,
id_cliente int not null foreign key references cliente(id_cliente)
)
CREATE TABLE detalle_venta (
id_detalle int not null primary key,
producto varchar(20),
cantidad int,
precio smallmoney,
id_venta int not null foreign key references venta(id_venta)
)
GO
-- Agregar datos
-- Clientes
INSERT INTO cliente(id_cliente, nombres) VALUES (1,'hernan');
INSERT INTO cliente(id_cliente, nombres) VALUES (2,'sonia');
-- Ventas
INSERT INTO venta(id_venta,fecha,id_cliente) VALUES (1,getdate(),1);
INSERT INTO venta(id_venta,fecha,id_cliente) VALUES (2,getdate(),1);
INSERT INTO venta(id_venta,fecha,id_cliente) VALUES (3,getdate(),1);
INSERT INTO venta(id_venta,fecha,id_cliente) VALUES (4,getdate(),2);

--Detalle Venta
INSERT INTO detalle_venta(id_detalle,producto,cantidad,precio,id_venta) VALUES (1,'p1',5,5,1);
INSERT INTO detalle_venta(id_detalle,producto,cantidad,precio,id_venta) VALUES (2,'p2',12,4,1);
INSERT INTO detalle_venta(id_detalle,producto,cantidad,precio,id_venta) VALUES (3,'p3',7,8,1);
INSERT INTO detalle_venta(id_detalle,producto,cantidad,precio,id_venta) VALUES (4,'p1',4,5,2);
INSERT INTO detalle_venta(id_detalle,producto,cantidad,precio,id_venta) VALUES (5,'p2',3,4,2);
INSERT INTO detalle_venta(id_detalle,producto,cantidad,precio,id_venta) VALUES (6,'p1',2,5,3);
INSERT INTO detalle_venta(id_detalle,producto,cantidad,precio,id_venta) VALUES (7,'p2',10,4,3);
INSERT INTO detalle_venta(id_detalle,producto,cantidad,precio,id_venta) VALUES (8,'p1',12,5,4);
INSERT INTO detalle_venta(id_detalle,producto,cantidad,precio,id_venta) VALUES (9,'p2',1,4,4);
INSERT INTO detalle_venta(id_detalle,producto,cantidad,precio,id_venta) VALUES (10,'p3',3,8,4);


/*Modo RAW: Cada fila de la consulta es un elemento XML*/
SELECT id_cliente, nombres 
FROM cliente
FOR XML RAW

GO
-- Resultado:
--<row id_cliente="1" nombres="hernan"/>
--<row id_cliente="2" nombres="sonia"/>

/*Modo RAW: cada columna de la consulta es un elemento XML*/
SELECT id_cliente, nombres 
FROM cliente
FOR XML RAW, ELEMENTS
GO
-- Resultado:
--<row>
-- <id_cliente>1</id_cliente>
-- <nombres>hernan</nombres>
--</row>
--<row><id_cliente>2</id_cliente>
-- <nombres>sonia</nombres>
--</row>

/*Modo RAW: Coloca etiquetas personalizadas en los elementos XML*/
SELECT id_cliente, nombres 
FROM cliente
FOR XML RAW('USUARIO'), root('DEUDORES'), elements
GO
-- Resultado:
--<DEUDORES>
-- <USUARIO id_cliente="1" nombres="hernan"/>
-- <USUARIO id_cliente="2" nombres="sonia"/>
--</DEUDORES>

/*Modo AUTO: Identico a RAW para una sola tabla*/
SELECT id_cliente, nombres 
FROM cliente
FOR XML AUTO
GO
-- Resultado:

--<cliente id_cliente="1" nombres="hernan"/>
--<cliente id_cliente="2" nombres="sonia"/>


/*Modo AUTO: Crea elementos XML anidados cuando hay un JOIN*/
-- Creacion de tablas para JOIN
-- Escribir la consulta para mostrar documentos XML
SELECT v.id_venta, v.fecha, d.id_detalle, d.producto, d.cantidad, d.precio
FROM venta v inner join detalle_venta d ON v.id_venta = d.id_venta
FOR XML AUTO
GO
-- Resultado:
--<v id_venta="1" fecha="2009-05-04T17:06:15.250">
-- <d id_detalle="1" producto="p1" cantidad="5" precio="5.0000"/>
-- <d id_detalle="2" producto="p2" cantidad="12" precio="4.0000"/>
-- <d id_detalle="3" producto="p3" cantidad="7" precio="8.0000"/>
--</v>
--<v id_venta="2" fecha="2009-05-04T17:06:15.250">
-- <d id_detalle="4" producto="p1" cantidad="4" precio="5.0000"/>
-- <d id_detalle="5" producto="p2" cantidad="3" precio="4.0000"/>
--</v>
--<v id_venta="3" fecha="2009-05-04T17:06:15.250">
-- <d id_detalle="6" producto="p1" cantidad="2" precio="5.0000"/>
-- <d id_detalle="7" producto="p2" cantidad="10" precio="4.0000"/>
--</v>
--<v id_venta="4" fecha="2009-05-04T17:06:15.250">
-- <d id_detalle="8" producto="p1" cantidad="12" precio="5.0000"/>
-- <d id_detalle="9" producto="p2" cantidad="1" precio="4.0000"/>
-- <d id_detalle="10" producto="p3" cantidad="3" precio="8.0000"/>
--</v>

/*Modo AUTO: Muestra cada columna como un elemento XML*/
SELECT v.id_venta, v.fecha, d.id_detalle, d.producto, d.cantidad, d.precio
FROM venta v inner join detalle_venta d ON v.id_venta = d.id_venta
FOR XML AUTO, ELEMENTS
GO
-- Resultado:
--<v><id_venta>1</id_venta><fecha>2009-05-04T17:06:15.250</fecha>
-- <d><id_detalle>1</id_detalle><producto>p1</producto><cantidad>5</cantidad><precio>5.0000</precio></d>
-- <d><id_detalle>2</id_detalle><producto>p2</producto><cantidad>12</cantidad><precio>4.0000</precio></d>
-- <d><id_detalle>3</id_detalle><producto>p3</producto><cantidad>7</cantidad><precio>8.0000</precio></d>
--</v>
--<v><id_venta>2</id_venta><fecha>2009-05-04T17:06:15.250</fecha>
-- <d><id_detalle>4</id_detalle><producto>p1</producto><cantidad>4</cantidad><precio>5.0000</precio></d>
-- <d><id_detalle>5</id_detalle><producto>p2</producto><cantidad>3</cantidad><precio>4.0000</precio></d>
--</v>
--<v><id_venta>3</id_venta><fecha>2009-05-04T17:06:15.250</fecha>
-- <d><id_detalle>6</id_detalle><producto>p1</producto><cantidad>2</cantidad><precio>5.0000</precio></d>
-- <d><id_detalle>7</id_detalle><producto>p2</producto><cantidad>10</cantidad><precio>4.0000</precio></d>
--</v>
--<v><id_venta>4</id_venta><fecha>2009-05-04T17:06:15.250</fecha>
-- <d><id_detalle>8</id_detalle><producto>p1</producto><cantidad>12</cantidad><precio>5.0000</precio></d>
-- <d><id_detalle>9</id_detalle><producto>p2</producto><cantidad>1</cantidad><precio>4.0000</precio></d>
-- <d><id_detalle>10</id_detalle><producto>p3</producto><cantidad>3</cantidad><precio>8.0000</precio></d>
--</v>

/*Modo AUTO: Muestra cada columna como un elemento XML*/
SELECT v.id_venta, v.fecha, d.id_detalle, d.producto, d.cantidad, d.precio
FROM venta v inner join detalle_venta d ON v.id_venta = d.id_venta
FOR XML AUTO, ELEMENTS, ROOT('Factura')
GO
--Resultado:
--<Factura>
-- <v><id_venta>1</id_venta><fecha>2009-05-04T17:06:15.250</fecha>
-- <d><id_detalle>1</id_detalle><producto>p1</producto><cantidad>5</cantidad><precio>5.0000</precio></d>
-- <d><id_detalle>2</id_detalle><producto>p2</producto><cantidad>12</cantidad><precio>4.0000</precio></d>
-- <d><id_detalle>3</id_detalle><producto>p3</producto><cantidad>7</cantidad><precio>8.0000</precio></d>
-- </v>
-- <v><id_venta>2</id_venta><fecha>2009-05-04T17:06:15.250</fecha>
-- <d><id_detalle>4</id_detalle><producto>p1</producto><cantidad>4</cantidad><precio>5.0000</precio></d>
-- <d><id_detalle>5</id_detalle><producto>p2</producto><cantidad>3</cantidad><precio>4.0000</precio></d>
-- </v>
-- <v><id_venta>3</id_venta><fecha>2009-05-04T17:06:15.250</fecha>
-- <d><id_detalle>6</id_detalle><producto>p1</producto><cantidad>2</cantidad><precio>5.0000</precio></d>
-- <d><id_detalle>7</id_detalle><producto>p2</producto><cantidad>10</cantidad><precio>4.0000</precio></d>
-- </v>
-- <v><id_venta>4</id_venta><fecha>2009-05-04T17:06:15.250</fecha>
-- <d><id_detalle>8</id_detalle><producto>p1</producto><cantidad>12</cantidad><precio>5.0000</precio></d>
-- <d><id_detalle>9</id_detalle><producto>p2</producto><cantidad>1</cantidad><precio>4.0000</precio></d>
-- <d><id_detalle>10</id_detalle><producto>p3</producto><cantidad>3</cantidad><precio>8.0000</precio></d>
-- </v>
--</Factura>

/*Modo EXPLICIT: Define el XML a traves de una tabla universal*/
SELECT 1 AS TAG, 
  null AS PARENT, 
  v.id_cliente [venta!1!IdVenta], 
       v.fecha [venta!1!Fecha!Element]
FROM venta v
FOR XML EXPLICIT
GO

-- Otro ejemplo
select 1 AS tag,
null AS PARENT,
cliente.id_cliente [cliente!1!Codigo!Element],
cliente.nombres [cliente!1!personal!Element],
null as [venta!2!NroVenta],
null as [venta!2!FechaVenta]
from cliente 
for XML EXPLICIT

select * from venta



USE AdventureWorks
/*Modo EXPLICIT: Define el XML a traves de una tabla universal*/
select 1 as Tag, null as Parent, 
     C.ContactID [Clientes!1!IdContacto], 
     C.ModifiedDate [Clientes!1!FechaModificacion], 
     C.FirstName [Clientes!1!Nombre!Element],
     C.LastName [Clientes!1!Apellidos!Element],
     C.EmailAddress [Clientes!1!Email!Element]
  from Person.Contact C
 for xml explicit

go
--Resultado:
--<venta IdVenta="1">
-- <Fecha>2009-05-04T17:06:15.250</Fecha>
--</venta>
--<venta IdVenta="1">
-- <Fecha>2009-05-04T17:06:15.250</Fecha>
--</venta>
--<venta IdVenta="1">
-- <Fecha>2009-05-04T17:06:15.250</Fecha>
--</venta>
--<venta IdVenta="2">
-- <Fecha>2009-05-04T17:06:15.250</Fecha>
--</venta>

/*Modo PATH: Define el formato XML a traves de XPATH*/
use TEST1
SELECT  v.id_venta "@IDVenta",
v.fecha "Categoria/fecha_ingreso"
FROM venta v
FOR XML PATH
GO
--Resultado:
--<row IDVenta="1">
-- <Categoria>
-- <fecha_ingreso>2009-05-04T17:06:15.250</fecha_ingreso>
-- </Categoria>
--</row>
--<row IDVenta="2">
-- <Categoria>
-- <fecha_ingreso>2009-05-04T17:06:15.250</fecha_ingreso>
-- </Categoria>
--</row>
--<row IDVenta="3">
-- <Categoria>
-- <fecha_ingreso>2009-05-04T17:06:15.250</fecha_ingreso>
-- </Categoria>
--</row>
--<row IDVenta="4">
-- <Categoria>
-- <fecha_ingreso>2009-05-04T17:06:15.250</fecha_ingreso>
-- </Categoria>
--</row>
/*Modo PATH: Define el formato XML dentro de un elemento*/
SELECT  v.id_cliente "@IDVenta",
v.fecha "Categoria/Fecha_ingreso"
FROM venta v
FOR XML PATH('Tienda'), ROOT('FACTURAS')
GO

-- Resultado:
--<FACTURAS>
-- <Tienda IDVenta="1">
-- <Categoria>
-- <Fecha_ingreso>2009-05-04T17:06:15.250</Fecha_ingreso>
-- </Categoria>
-- </Tienda>
-- <Tienda IDVenta="1">
-- <Categoria>
-- <Fecha_ingreso>2009-05-04T17:06:15.250</Fecha_ingreso>
-- </Categoria>
-- </Tienda>
-- <Tienda IDVenta="1">
-- <Categoria>
-- <Fecha_ingreso>2009-05-04T17:06:15.250</Fecha_ingreso>
-- </Categoria>
-- </Tienda>
-- <Tienda IDVenta="2">
-- <Categoria>
-- <Fecha_ingreso>2009-05-04T17:06:15.250</Fecha_ingreso>
-- </Categoria>
-- </Tienda>
--</FACTURAS>

SELECT c.id_cliente "@codigo", 
c.nombres "datospersonales", 
v.id_venta "venta/@numero_venta", 
v.fecha "venta/@fecha",
d.producto "producto", 
d.cantidad "cantidad", 
d.precio "precio"
FROM (cliente c inner join venta v ON (c.id_cliente = v.id_cliente))
inner join detalle_venta d ON (v.id_venta = d.id_venta)
order by c.id_cliente, v.id_venta

FOR XML PATH
GO

SELECT c.id_cliente "@codigo",
c.nombres "datos_personales", 
(SELECT v.id_venta "@nro_venta", 
v.fecha "@fecha_venta"
FROM venta v
WHERE v.id_cliente = c.id_cliente
FOR XML Path('venta'), TYPE
) "ventas"
FROM cliente c
FOR XML path('cliente')
GO

Esquemas de documentos XML en SQL Server

create database banco
go

-- 
use banco
go


-- crear un esquema de sql
-- drop xml schema collection esquema_banco2 as 
create xml schema collection esquema_banco3 as
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="banco" type="TipoBanco"/>
<xsd:element name="cuenta">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="nombre_sucursal" type="xsd:string"/>
<xsd:element name="saldo" type="xsd:decimal"/>
</xsd:sequence>
<xsd:attribute name="numero_cuenta" type="xsd:string"/>
<xsd:attribute name="titulares" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="cliente">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="nombre_cliente" type="xsd:string"/>
<xsd:element name="ciudad_cliente" type="xsd:string"/>
<xsd:element name="calle_cliente" type="xsd:string"/>
</xsd:sequence>
<xsd:attribute name="id_cliente" type="xsd:integer"/>
<xsd:attribute name="cuentas" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
<xsd:complexType name="TipoBanco">
<xsd:sequence>
<xsd:element ref="cuenta"   minOccurs="0" maxOccurs="unbounded"/>
<xsd:element ref="cliente"   minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>'

select * from sys.xml_schema_collections
-- crear una tabla en base al esquema
-- drop table bancos1

create table bancos3
(
id_banco int identity(1,1),
data_banco xml (esquema_banco3) null -- se almacena documentos xml con restricción del shema
)
go

insert into bancos3(data_banco) values (
'<banco>
<cuenta numero_cuenta="C-401" titulares="101 102">
<nombre_sucursal>Cusco</nombre_sucursal>
<saldo>459.80</saldo>
</cuenta>
<cuenta numero_cuenta="C-402" titulares="101 102">
<nombre_sucursal>Puno</nombre_sucursal>
<saldo>560.89</saldo>
</cuenta>
<cuenta numero_cuenta="C-403" titulares="101 102">
<nombre_sucursal>Cusco</nombre_sucursal>
<saldo>25900.89</saldo>
</cuenta>
<cliente id_cliente="101" cuentas="C-401 C-301">
<nombre_cliente>juan</nombre_cliente>
<ciudad_cliente>Cusco</ciudad_cliente>
<calle_cliente>alamos </calle_cliente>
</cliente>
<cliente id_cliente="102" cuentas="C-401 C-301">
<nombre_cliente>ana</nombre_cliente>
<ciudad_cliente>Cusco</ciudad_cliente>
<calle_cliente>Jose Olaya</calle_cliente>
</cliente>
<cliente id_cliente="103" cuentas="C-401 C-301">
<nombre_cliente>pedro</nombre_cliente>
<ciudad_cliente>Puno</ciudad_cliente>
<calle_cliente>San Jose</calle_cliente>
</cliente>
</banco>'
)
go
select * from bancos3

IV) Tarea
  1. Personalizar entre elementos y atributos el resultado de una consulta con la clausula FOR XML y modo PATH.
  2. Crear un esquema para el siguiente documento XML.
<part>
 <producto>
  <nombre> bicicleta </nombre>
   <infocomponente>
    <producto>
     <nombre> rueda </nombre>
     <infocomponente>
      <producto>
       <nombre> borde </nombre>
      </producto>
      <cantidad> 1 </cantidad>
     </infocomponente>
     <infocomponente>
      <producto>
       <nombre> rayos </nombre>
      </producto>
      <cantidad> 40 </cantidad>
     </infocomponente>
    <infocomponente>
     <producto>
      <nombre> neumático </nombre>
     </producto>
     <cantidad> 1 </cantidad>
    </infocomponente>
   </producto>
   <cantidad> 2 </cantidad>
  </infocomponente>
  <infocomponente>
   <producto>
    <nombre> freno </nombre>
   </producto>
   <cantidad> 2 </cantidad>
  </infocomponente>
  <infocomponente>
   <producto>
    <nombre> equipo </nombre>
   </producto>
   <cantidad> 3 </cantidad>
  </infocomponente>
  <infocomponente>
   <producto>
    <nombre> marco </nombre>
   </producto>
   <cantidad> 1 </cantidad>
  </infocomponente>
 </producto>

</part>



Comments