Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

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

Dec 13, 2012
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 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 RSS subscribe by email Subscribe by Email

4 comments :

Unknown said...

nice

rama subramaniyan said...

superb

Unknown said...

awsum man :) keep posting these kind f stuffs

ammukarthi said...

how to create XML file from the data's in datatable using vb.net

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.