Guruji Point - Code You Want To Write

  • Home
  • Asp.Net
  • WCF
  • SQL
  • Type Script
  • AngularJS
  • Tech News
  • Blog
  • Earn Online

Saturday, 15 July 2017

Return Table Data In XML AND JSON Format On SQL Server

 SQL     No comments   


Introduction
In this article we will learn how to retrieve the data into xml form or in other words you can say how to get table data into xml and Json format. Select table data into Json and xml form in SQL server. Simple sql server inbuilt function for return and display data into JSon and xml format.

Previous Updates
In previous articles we have learnt Showing Bind multiple dropdown list using single method call using c#. Read Excel file in C#  And Display in Grid. Read Excel in Windows Application project. Chart With Database In Asp.Net Using C#.  Insert Only Numeric Values In Asp TextBox Using Regex . Get TextBox , Dropdown, CheckBox  control Values In Aspx.cs Page From User Control Using C#.

SQL Server- Database Structure
Table
CREATE TABLE [dbo].[EmpDetail1](
       [ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL ,
       [Name] [varchar](50) NULL,
       [Address] [varchar](50) NULL,
       [Phone] [int] NULL
)
Insert  Script 
INSERT [dbo].[EmpDetail] ([Name], [Address], [Phone]) VALUES ('Nicks', 'IND', 009988776)
INSERT [dbo].[EmpDetail] ([Name], [Address], [Phone]) VALUES ('Gaurav', 'UK', 998877889)
INSERT [dbo].[EmpDetail] ([Name], [Address], [Phone]) VALUES ('Mike', 'USA', 996677665)
INSERT [dbo].[EmpDetail] ([Name], [Address], [Phone]) VALUES ('Maxi', 'RSA', 004455644)
INSERT [dbo].[EmpDetail] ([Name], [Address], [Phone]) VALUES ('Shiva', 'RA', 31265480)
Select Table Data In XML Format
You can achieve this by using single line statement. And this statement generates a XML of whole table records for you.
Many time you need to display or retrieve the data into XML forms When you are working with any Web Application and Web service project this any be your one of the most required thing.
I am sharing this code because i face the same type of   problem in front of me and when i search i didn't find any running solution to quick. In my scenario i have a Web Service project and i need to generate a soap request for that project.  So by using this i can able to generate the XML for that project. Few modifications over there but here is an sample for you.
select * from EmpDetail for xml path('Parent')
Output Of this statement -
<Parent>
  <ID>2</ID>
  <Name>Nicks</Name>
  <Address>USA</Address>
  <Phone>312654980</Phone>
</Parent>
<Parent>
  <ID>3</ID>
  <Name>Nicks</Name>
  <Address>USA</Address>
  <Phone>312654980</Phone>
</Parent>
In the above statement i used 'Parent' as Parent Node name for xml, you can replace this name as per your requirement.

Select Table Data In JSON Format
select '[' + STUFF((
select
',
{
"ID":' + cast(Id as varchar(5))+ ',
"Name" :"' + Name + '"'+',
"Address":"' + Address + '"'+',
"Phone":' + cast(Phone as varchar(10)) +',
}'

from EmpDetail
for xml path(''), type

).value('.', 'varchar(max)'), 1, 1, '') + ']'
Output of this statement -

[
{
"ID":1,
"Name" :"Nicks",
"Address":"IND",
"Phone":9988776,
},
{
"ID":2,
"Name" :"Gaurav",
"Address":"UK",
"Phone":998877889,
}]

  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Facebook Updates

Guruji Point

Categories

Comman table Expression in SQL Dynamic Grid Row IQueryable in c# Learn AngularJS Scope_Identity() Use of indexes comma seperated string in sql row_number() and dense_rank() salary table structure. while loop in sql why do we need cursor why tuple used

About Us

This blog is about the Programming ,Tech News and some intresting facts related Contents. Here you can find fundamental and expert level topic of programming languages very practically related to C# , Asp.Net ,Sql-Server, JavaScript, Jquery, WebServices And also Web-Api, WCF ,WPF, Angular Js.

Contact Us

Email Us - gurujipoints@gmail.com
Contact- 8077657477

Reach Us

Copyright © Guruji Point - Code You Want To Write