Here we have <stud> tags inside <Root> Section... If we would like to get the properties of each student we have to query as follows:
DECLARE @xml XML
SET @xml=N'<Root>
<stud id="1">
<fname>Manoj</fname>
<lname>Pandey</lname>
<class>10</class>
<marks>80.5</marks>
</stud>
<stud id="2">
<fname>Saurabh</fname>
<lname>Sharma</lname>
<class>11</class>
<marks>82.7</marks>
</stud>
<stud id="3">
<fname>Kanchan</fname>
<lname>Pandey</lname>
<class>10</class>
<marks>90.5</marks>
</stud>
<stud id="4">
<fname>Rajesh</fname>
<lname>Shah</lname>
<class>11</class>
<marks>70.3</marks>
</stud>
<stud id="5">
<fname>Kunal</fname>
<lname>Joshi</lname>
<class>12</class>
<marks>64.7</marks>
</stud>
</Root>';
-- Select all records in tabular format from above XML string:
SELECT
Tab.Col.value('@id','int') AS ID,
Tab.Col.value('fname[1]','nvarchar(20)') AS FirstName,
Tab.Col.value('lname[1]','nvarchar(20)') AS LastName,
Tab.Col.value('class[1]','int') AS class,
Tab.Col.value('marks[1]','float') AS Marks
FROM @xml.nodes('/Root/stud') Tab(Col)
OUTPUT:
ID FirstName LastName class Marks
1 Manoj Pandey 10 80.5
2 Saurabh Sharma 11 82.7
3 Kanchan Pandey 10 90.5
4 Rajesh Shah 11 70.3
5 Kunal Joshi 12 64.7