SQL Sever- Reading XML Data File using SQL Query Example | Read XML Data Type in SQL Server

Introduction:

Here I will explain how to write a query to read xml data file or read xml data type with simple example using SQL Server.

Description:

In previous articles I explained Pass XML file as a parameter to stored procedure, read xml node values and bind data to gridview, how to insert and read data from xml in asp.net, Create online poll system with percentage graphs, Bind xml data to dropdownlist/gridview in asp.net . Now I will explain how to write a query to read xml data in SQL Server.

In situation I got requirement like read data from xml file and send that xml file as parameter to store procedure. My XML File Name as “Sample.xml” and that would contains data like this

<?xml version="1.0" encoding="utf-8" ?> <users>           <user>                    <FirstName>Suresh</FirstName>                    <LastName>Dasari</LastName>                    <UserName>SureshDasari</UserName>                    <Job>Team Leader</Job>           </user>           <user>                    <FirstName>Mahesh</FirstName>                    <LastName>Dasari</LastName>                    <UserName>MaheshDasari</UserName>                    <Job>Software Developer</Job>           </user>           <user>                    <FirstName>Madhav</FirstName>                    <LastName>Yemineni</LastName>                    <UserName>MadhavYemineni</UserName>                    <Job>Business Analyst</Job>           </user> </users>

To read this xml file I written query like as shown below

DECLARE @XMLdata XML SET @XMLdata='<users>           <user>                    <FirstName>Suresh</FirstName>                    <LastName>Dasari</LastName>                    <UserName>SureshDasari</UserName>                    <Job>Team Leader</Job>           </user>           <user>                    <FirstName>Mahesh</FirstName>                    <LastName>Dasari</LastName>                    <UserName>MaheshDasari</UserName>                    <Job>Software Developer</Job>           </user>           <user>                    <FirstName>Madhav</FirstName>                     <LastName>Yemineni</LastName>                    <UserName>MadhavYemineni</UserName>                    <Job>Business Analyst</Job>           </user> </users>' SELECT t.value('(FirstName/text())[1]','varchar(50)')AS FirstName , t.value('(LastName/text())[1]','varchar(50)')AS LastName, t.value('(UserName/text())[1]','varchar(50)')AS UserName, t.value('(Job/text())[1]','varchar(50)')AS Job FROM @XMLdata .nodes('/users/user')AS TempTable(t)

Once we run above query we will get output like as shown below

Output

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email