Maybe something like:
declare @iDoc integer
declare @myXml varchar(2000)
set @myXml =
'<Customer CustomerID=''ALFKI''>
<Order OrderDate=''1/1/1972'' Freight=''3''>
<Detail ProductID=''1'' Price=''4'' Quantity=''10'' Discount=''0''/>
<Detail ProductID=''2'' Price=''5'' Quantity=''2'' Discount=''0''/>
</Order>
<Order OrderDate=''1/1/1972'' Freight=''3''>
<Detail ProductID=''9'' Price=''4'' Quantity=''10'' Discount=''0''/>
<Detail ProductID=''9'' Price=''5'' Quantity=''2'' Discount=''0''/>
</Order>
<Order OrderDate=''1/1/1972'' Freight=''3''>
<Detail ProductID=''10'' Price=''4'' Quantity=''10'' Discount=''0''/>
<Detail ProductID=''12'' Price=''5'' Quantity=''2'' Discount=''0''/>
</Order>
<Order OrderDate=''1/1/1972'' Freight=''3''>
<Detail ProductID=''1'' Price=''4'' Quantity=''10'' Discount=''0''/>
<Detail ProductID=''24'' Price=''5'' Quantity=''2'' Discount=''0''/>
</Order>
</Customer>'
exec sp_xml_prepareDocument @idoc output, @myXml
select CustomerID,
OrderDate,
Freight,
ProductID,
Price,
Quantity,
Discount
from openxml (@iDoc, '/Customer/Order/Detail')
with ( CustomerID varchar(10) '../../@CustomerID',
OrderDate varchar(12) '../@OrderDate',
Freight varchar(11) '../@Freight',
ProductID varchar(11) './@ProductID',
Price varchar(11) './@Price',
Quantity varchar(11) './@Quantity',
Discount varchar(11) './@Discount'
)
exec sp_xml_removeDocument @idoc
/*
CustomerID OrderDate Freight ProductID Price Quantity Discount
---------- ------------ ----------- ----------- ----------- ----------- -----------
ALFKI 1/1/1972 3 1 4 10 0
ALFKI 1/1/1972 3 2 5 2 0
ALFKI 1/1/1972 3 9 4 10 0
ALFKI 1/1/1972 3 9 5 2 0
ALFKI 1/1/1972 3 10 4 10 0
ALFKI 1/1/1972 3 12 5 2 0
ALFKI 1/1/1972 3 1 4 10 0
ALFKI 1/1/1972 3 24 5 2 0
*/