XML en SQL Server

  1. Uso XML en SQL Server

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

Convertir un XML a una tabla


declare @mydoc xmlset @mydoc='<Orders> <Order OrderID=”13000″ CustomerID=”ALFKI” OrderDate=”2006-09-20Z”EmployeeID=”2″></Order> <Order OrderID=”13001″ CustomerID=”VINET” OrderDate=”2006-09-20Z”EmployeeID=”1″></Order></Orders>’

SELECT OrderID    = T.Item.value(‘@OrderID’, ‘int’),      CustomerID = T.Item.value(‘@CustomerID’, ‘nchar(5)’),      OrderDate  = T.Item.value(‘@OrderDate’,  ‘datetime’),      EmployeeId = T.Item.value(‘@EmployeeID’, ‘smallint’)FROM   @mydoc.nodes(‘Orders/Order’) AS T(Item)

Convertir un XML a una Tabla con OpenXML


DECLARE @xml_text VARCHAR(4000), @i INT

SELECT @xml_text = ‘<root><person LastName=”White” FirstName=”Johnson”/><person LastName=”Green” FirstName=”Marjorie”/><person LastName=”Carson” FirstName=”Cheryl”/></root>’

EXEC sp_xml_preparedocument @i OUTPUT, @xml_text
SELECT * FROM   
OPENXML(@i, ‘/root/person’) 
WITH (LastName nvarchar(50),   FirstName nvarchar(50))

Leave a Reply

Your email address will not be published. Required fields are marked *