{"id":30356,"date":"2015-11-29T11:39:00","date_gmt":"2015-11-29T06:09:00","guid":{"rendered":"https:\/\/2thenew.today\/blog\/?p=30356"},"modified":"2016-02-09T10:51:56","modified_gmt":"2016-02-09T05:21:56","slug":"how-to-get-output-sql-server-as-a-json-string","status":"publish","type":"post","link":"https:\/\/2thenew.today\/blog\/how-to-get-output-sql-server-as-a-json-string\/","title":{"rendered":"How to get output from Sql server as a jSON String ?"},"content":{"rendered":"

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

1.Convert tabular format to xml .<\/p>\n

2.Generate json format from that xml by using custom select query as \u00a0in given below example step 3.<\/p>\n

This is step by step process to convert sql output to json format as:-<\/p>\n

1)<\/strong> Let\u2019s create Product list table in sql server and insert some record in this table<\/strong>.<\/p>\n

createtableProductList
\n(
\nId int identity(1,1) primary key,
\nProductName varchar(500),
\nPrice numeric(18,2),
\nQuantity int,
\nInserton datetime
\n)<\/p>\n

insert into ProductList(ProductName,Price,quantity,Inserton)
\nvalues(‘Digital camera’,20000,5,getdate())<\/p>\n

insert into ProductList(ProductName,Price,quantity,Inserton)
\nvalues(‘Laptop’,10000,5,getdate())<\/p>\n

insert into ProductList(ProductName,Price,quantity,Inserton)
\nvalues(‘Printer’,12000.00,5,getdate())<\/p>\n

insert into ProductList(ProductName,Price,quantity,Inserton)
\nvalues(‘Mobile Phone’,12000.00,5,getdate())<\/p>\n

insert into ProductList(ProductName,Price,quantity,Inserton)
\nvalues(‘Digital camera’,20000.00, 5,getdate())<\/p>\n

\"ShowProduct\"<\/p>\n

2)<\/strong> Convert sql table into \u00a0XML Format<\/strong><\/p>\n

Select * from ProductList for XML Path(‘xml path’)
\n\"XML\"<\/p>\n

3)<\/strong> Convert XML To JSon format string by using custom select query.<\/strong><\/p>\n

select'[‘+
\nstuff(
\n(
\nselect top 2 ‘,{“ProductName”: “‘+ProductName+'”,”Price”: “‘+convert(varchar,Price)+'”,”Quantity”: “‘+convert(varchar,quantity)+'”,”Inserton”: “‘+convert(varchar,Inserton,105)+'”}’
\nfrom ProductList for XML Path(”)
\n),1,1,”
\n)
\n+’]'[ProductDetail]<\/p>\n

\"JSON\"<\/p>\n","protected":false},"excerpt":{"rendered":"

We can convert tabular format to xml using sql query in sql server database ,but if we want to convert in json format \u00a0from database ,then we can face problem because database does not support \u00a0native JSON integration. There is a way through which we can generate output in json format by following these steps:- […]<\/p>\n","protected":false},"author":855,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":38,"footnotes":""},"categories":[1],"tags":[2867],"class_list":["post-30356","post","type-post","status-publish","format-standard","hentry","category-technology","tag-sql-to-xml-conversion"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t