lets play with XML

Here is Nested XML which contains data from 2 tables ,Customer and Order
I want to get data for this 2 tables from XML



declare @xmlData XML
set @XMLData =
'<root><row>
<custid>1</custid>
<custname>amish</custname>
<order>
<orderdetail orderid = "1" orderdate = "2008-01-01" />
<orderdetail orderid = "2" orderdate = "2008-01-02" />
</order>
</row>
<row>
<custid>2</custid>
<custname>nikita</custname>
<order>
<orderdetail orderid = "3" orderdate = "2008-01-03" />
<orderdetail orderid = "4" orderdate = "2008-01-04" />
</order>
</row>
</root>'

DECLARE @handle1 INT
DECLARE @handle2 INT

EXEC sp_xml_preparedocument @handle1 OUTPUT, @xmlData;
EXEC sp_xml_preparedocument @handle2 OUTPUT, @xmlData

SELECT * FROM OPENXML (@handle1, '/root/row', 2) WITH ( custid int, custname varchar(450))

SELECT * FROM OPENXML (@handle2, '/root/row/order/orderdetail')
WITH ( orderid int '@orderid',
orderdate varchar(20) '@orderdate',
custid int '../../custid')


EXEC sp_xml_removedocument @handle1
EXEC sp_xml_removedocument @handle2