I) OBJETIVOS
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
|
DOCENCIA UNIVERSITARIA > Sistemas de Bases de Datos II > Laboratorio de Sistemas de Bases de Datos II >