Lets play with XML

Below is a nested XML which contains Customer info and Order Details.I need to get the Order Details for each customer in the XML.One way to do it was using OPENXML and sp_xml_preparedocument.

With new enhancements for XML datatype from SQL 2005 and beyond it can be easily done by using Xquery.This is how it can be done.

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

select @xmldata

select x.i.value('../../custid[1]','varchar(40)') as custid,
x.i.value('../../custname[1]','varchar(40)')as custname,
x.i.value('@orderid','int')as orderid,
x.i.value('@orderdate','date')as orderdate
from @xmldata.nodes('/root/row/order/orderdetail')x(i)

The resultset for the above query is this.