XML en SQL Server
Uso XML en SQL Server 2008
Instrucciones para realizar consultas y devolverlas en formato XML, o extraer un valor de un XML
|
—xml
—For XML Raw , Auto, Explicit, Path
–consulta de las tablas customers y orders
–1.Ejemplo 1
Select Customers.CustomerID, Customers.CompanyName,
Orders.OrderID, Orders.OrderDate from Customers
inner join orders on
Customers.CustomerID=Orders.customerid
for xml raw
–2.Ejemplo 2
Select Customers.CustomerID, Customers.CompanyName,
Orders.OrderID, Orders.OrderDate from Customers
inner join orders on
Customers.CustomerID=Orders.customerid
for xml auto
———-For XML Explicit
–Informacion de customers y orders del customerid=’ALFKI’
Select 1 as Tag, null as parent,
customerid as [Cliente!1!customerid], contactname as [Cliente!1],
null as [Orden!2!orderid],
null as [Orden!2]
from customers as C where customerid=‘ALFKI’
union
Select 2 as Tag, 1 as Parent, C.customerid,
C.contactName, o.orderid, o.shipaddress from
Customers as C inner join orders as o
on C.CustomerID=o.CustomerID
where C.CustomerID=‘ALFKI’
For xml Explicit
–resultado xml Anterior
<C customerid=«ALFKI»>Maria Anders
<O orderid=«10643»>Obere Str. 57</O>
<O orderid=«10692»>Obere Str. 57</O>
<O orderid=«10702»>Obere Str. 57</O>
<O orderid=«10835»>Obere Str. 57</O>
<O orderid=«10952»>Obere Str. 57</O>
<O orderid=«11011»>Obere Str. 57</O>
</C>
—uso de for xml path
Select Customers.CustomerID, Customers.CompanyName,
Orders.OrderID, Orders.OrderDate from Customers
inner join orders on
Customers.CustomerID=Orders.customerid
for xml path (‘venta’)
———–uso de xml.query
declare @mydoc xml
set @mydoc=‘<root>
<productdescription productid=»1″ productname=»rood bike»>
<Features>
<warranty>1 año de garantia para partes</warranty>
<maintenance>3 años de mantenimiento</maintenance>
</Features>
</productdescription>
</root>’
Select @mydoc.query
(‘/root/productdescription/Features/maintenance’)
———–uso de xml.Value
declare @bicicleta varchar(100)
declare @mydoc xml
set @mydoc=‘<root>
<productdescription productid=»1″ productname=»rood bike»>
<Features>
<warranty>1 año de garantia para partes</warranty>
<maintenance>3 años de mantenimiento</maintenance>
</Features>
</productdescription>
</root>’
Set @bicicleta= @mydoc.value(
‘(/root/productdescription/@productname)[1]’, ‘varchar(100)’)
Select @bicicleta
|