SQL with Manoj

My previous post on XML discusses how to simply SELECT records from an XML string.

On this post I’m giving few more examples on how to SELECT records from an XML string. Here are some more examples to retrieve data in a relational tabular format by querying an XML string:

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

-- Select records as in Key-Value pair format:
SELECT
Tab.Col.value('../@id', 'int') AS RowID,
Tab.Col.value('local-name(.)', 'nvarchar(max)') AS ColName,
Tab.Col.value('text()[1]', 'nvarchar(max)') AS ColValue
FROM @xml.nodes('/*/*/*') AS Tab(Col)
GO

Output:-
RowID ColName ColValue
1     fname   Manoj
1     lname   Pandey
1     class   10
1     marks   80.5
2     fname   Saurabh
2     lname   Sharma
2     class   11
2     marks   82.7
3     fname   Kanchan
3     lname   Pandey
3     class   10
3     marks   90.5
4     fname   Rajesh
4     lname   Shah
4     class   11
4     marks   70.3
5     fname   Kunal
5     lname   Joshi
5     class   12
5     marks   64.7