How to Get XML and Json format through SQL SERVER

How to Get XML and Json format through SQL SERVER.......

Here I will explain that how to get  sql data into xml and json format. As we know in SQL SERVER Xml data type exists. json is light waited as compare to xml and it is easy to use through JavaScript and jquery. so developer first choice to move json. some of the developers are using external function for converting sql data to json format. But we can do it by some modification of our sql query as shown below.
json is most same as the xml. suppose we have a table employee which has five columns id and name,gender,salary,doj(Date of Joining)

--Sql Query------------------

create database test

use test

-- CREATE TABLE WHICH NAME EMPLOYEE

create table Employee

(

EmpId int primary key,

Name varchar(50),

Gender varchar(50),

Salary int,

Doj date

)

-- insert  data-----

insert into Employee values (1,'Kush Tiwari','M',123456,'09/09/1987')

--how to get  sql data into xml  format

select * from employee for xml path('Node')

Result

<Node>

<EmpId>1</EmpId>

<Name>Kush Tiwari</Name>

<Gender>M</Gender>

<Salary>123456</Salary>

<Doj>1987-09-09</Doj>

</Node>

<Node>

<EmpId>2</EmpId>

<Name>Suresh Singh</Name>

<Gender>M</Gender>

<Salary>123456</Salary>

<Doj>1987-09-09</Doj>

</Node>

--how to get sql data into json format.............

select '[' + STUFF((

select

',

{

"EmpId":' + cast(EmpId as varchar(5))+ ',

"Name" :"' + Name + '"'+',

"Gender":"' + Gender + '"'+',

"Salary":' + cast(Salary as varchar(10)) +',

"Doj":"' + cast(Doj as varchar(20)) + '"'+',

}'

from employee

for xml path(''), type

).value('.', 'varchar(max)'), 1, 1, '') + ']'

Result

[{

"EmpId":1,

"Name" :"Kush Tiwari",

"Gender":"M",

"Salary":123456,

"Doj":"1987-09-09",

},

{

"EmpId":2,

"Name" :"Suresh Singh",

"Gender":"M",

"Salary":123456,

"Doj":"1987-09-09",

}]