Advertisements
Home > XML > Query XML string in tabular format – SQL Server

Query XML string in tabular format – SQL Server


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
Advertisements
Categories: XML Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: