How to get output from Sql server as a jSON String ?

We can convert tabular format to xml using sql query in sql server database ,but if we want to convert in json format  from database ,then we can face problem because database does not support  native JSON integration. There is a way through which we can generate output in json format by following these steps:-

1.Convert tabular format to xml .

2.Generate json format from that xml by using custom select query as  in given below example step 3.

This is step by step process to convert sql output to json format as:-

1) Let’s create Product list table in sql server and insert some record in this table.

createtableProductList
(
Id int identity(1,1) primary key,
ProductName varchar(500),
Price numeric(18,2),
Quantity int,
Inserton datetime
)

insert into ProductList(ProductName,Price,quantity,Inserton)
values(‘Digital camera’,20000,5,getdate())

insert into ProductList(ProductName,Price,quantity,Inserton)
values(‘Laptop’,10000,5,getdate())

insert into ProductList(ProductName,Price,quantity,Inserton)
values(‘Printer’,12000.00,5,getdate())

insert into ProductList(ProductName,Price,quantity,Inserton)
values(‘Mobile Phone’,12000.00,5,getdate())

insert into ProductList(ProductName,Price,quantity,Inserton)
values(‘Digital camera’,20000.00, 5,getdate())

2) Convert sql table into  XML Format

Select * from ProductList for XML Path(‘xml path’)

3) Convert XML To JSon format string by using custom select query.

select'[‘+
stuff(
(
select top 2 ‘,{“ProductName”: “‘+ProductName+'”,”Price”: “‘+convert(varchar,Price)+'”,”Quantity”: “‘+convert(varchar,quantity)+'”,”Inserton”: “‘+convert(varchar,Inserton,105)+'”}’
from ProductList for XML Path(”)
),1,1,”
)
+’]'[ProductDetail]