declare @x xml
set @x='<OrderList StoreAccountName="gameSdirect">
<Order currency="USD" id="gameSdirect-128093">
<Time>Tue Feb 9 16:41:35 2010 GMT</Time>
<IPAddress>11.11.11..11</IPAddress>
<Shipping>USPS Economy Mail</Shipping>

<Total>
<Line type="Subtotal" name="Subtotal">29.99</Line>
<Line type="Shipping" name="Shipping">0.00</Line>
<Line type="Tax" name="Tax">0.00</Line>
<Line type="Total" name="Total">29.99</Line>
</Total>
<Space-Id></Space-Id>
</Order>
</OrderList>'


select Login_User_Id,OrderID,Currency,IPAddress,ShippingType,
MAX(case when typeval='Subtotal' then value else null end) as Subtotal,
MAX(case when typeval='Shipping' then value else null end) as Shipping,
MAX(case when typeval='Tax' then value else null end) as Tax,
MAX(case when typeval='Total' then value else null end) as Total,
OrderTime,
GETDATE() AS Inserted_DateTime
FROM
(
select t.u.value('../@StoreAccountName','varchar(100)') as Login_User_Id,
t.u.value('@id','varchar(100)') as OrderID,
t.u.value('@currency','varchar(100)') as Currency,
t.u.value('./IPAddress[1]','varchar(100)') as IPAddress,
t.u.value('./Shipping[1]','varchar(100)') as ShippingType,
t.u.value('./Time[1]','varchar(100)') as OrderTime,
a.b.value('.','decimal(10,2)') as value,
a.b.value('@type','varchar(100)') as typeval
from @x.nodes ('/OrderList/Order') t (u)
cross apply u.nodes('./Total/Line')a(b)
)t
GROUP BY Login_User_Id,OrderID,Currency,IPAddress,ShippingType,OrderTime


Login_User_Id	OrderID	Currency	IPAddress	ShippingType	Subtotal	Shipping	Tax	Total	OrderTime	Inserted_DateTime
gameSdirect	gameSdirect-128093	USD	11.11.11..11	USPS Economy Mail	29.99	0.00	0.00	29.99	Tue Feb 9 16:41:35 2010 GMT	2010-02-16 23:40:03.957