Reading nested XML Data

Posted by Bandi under Sql Server category on | Points: 40 | Views : 289
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:


SET @xml=N'<Root>
    <stud id="1">
    <stud id="2">
    <stud id="3">
    <stud id="4">
    <stud id="5">
-- Select all records in tabular format from above XML string:
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)

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

Comments or Responses

Login to post response