Uso de XML en SQL Server 2008

---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

SQL TRANSACT: Uso XML en SQL Server 2008

Instrucciones para realizar consultas y devolverlas en formato XML, o extraer un valor de un XML