Reading nested XML Data

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

Comments or Responses

Login to post response