XML no SQL… funciona q eh uma beleza….

SQL

declare @doc varchar(1000)
declare @idoc int

SET @doc =’
<ROOT>
<Customer CustomerID=”VINET” ContactName=”Paul Henriot”>
<Order OrderID=”10248″ CustomerID=”VINET” EmployeeID=”5″ OrderDate=”1996-07-04T00:00:00″>
<OrderDetail ProductID=”11″ Quantity=”12″/>
<OrderDetail ProductID=”42″ Quantity=”10″/>
</Order>
</Customer>
<Customer CustomerID=”LILAS” ContactName=”Carlos Gonzlez”>
<Order OrderID=”10283″ CustomerID=”LILAS” EmployeeID=”3″ OrderDate=”1996-08-16T00:00:00″>
<OrderDetail ProductID=”72″ Quantity=”3″/>
</Order>
</Customer>
</ROOT>’

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM OPENXML (@idoc, ‘/ROOT/Customer/Order/OrderDetail/@ProductID’)
WITH ( ProdID  int ‘.’,
Qty     int ‘../@Quantity’,
OID     int ‘../../@OrderID’,
Costu   varchar(20) ‘../../../@CustomerID’)

EXEC sp_xml_removedocument @idoc


Result

ProdID      Qty         OID         Costu
———– ———– ———– ——————–
11          12          10248       VINET
42          10          10248       VINET
72          3           10283       LILAS

(3 row(s) affected)