Can you return data in JSON format from a web service in SQL Server?

The short answer is yes provided we run our queries in SQL Server 2016.

We have seen in an earlier post using ODATA service to generate a report from Power BI.

These were some example OData services that were considered in the previous mentioned link.

Northwind traders here:
http://services.odata.org/northwind/northwind.svc
http://services.odata.org/V4/Northwind/Northwind.svc

Adventure Works data here:
http://services.odata.org/AdventureWorksV3/AdventureWorks.svc

SQL Server 2016 supports JSON and it is possible to run a query in SQL Server Management Studio to return data from a ODATA service by running a query fashioned a shown.
=======
SELECT 'http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity' AS '@odata.context',  
ProductID, Name as ProductName  
FROM Production.Product 
WHERE ProductID<400 br="">FOR JSON AUTO 
=======
This retrieves the following result:

Note that the size of text returned is limited by the settings.

How to access OData service with LINQ?

Open Data Protocol (OData) relates to creation and use of RESTful APIs. OData uses URIs to identify resources on the Internet. The generic syntax for accessing the root of such service is http://host/Service. OData is built upon HTTP, ATOMPub and JSON.

An example of such a resource is the Northwind Service:

http://services.odata.org/northwind/northwind.svc/

LINQ, short for Language Integrated Query, provides an object oriented approach to not only querying relational databases but also any kind of source such as XML, Collection of objects, etc.

Want to know more about LINQ, go here.
Accessing OData with LinqPad.

Launch LinqPad (version used here is v4.55.03) and click on Add Connection link shown here:

OData_02.png

Choose Data Context window opens.

OData_03.png

Click WCF Data Services 5.5 (OData 3) and Click Next.
 In the WCF Data Conneciton 5.5 window type in the URI as shown (you have seen what this is earlier). Leave username and password blank. You can get to the XML or the JSON formatted resources. Remembering this connection is OK for the next time you come here.

Hit Test with Default(XML) checked. Your connection gets populated as shown.

OData_04.png

In the Query pane, Click on Connection and choose the option shown. As to query language you have a number of options.

Odata_05.png

I have just chosen SQL as the language to query. It looks like the driver does not support SQL.

Odata_06.png

Change the language option to C# Expression. Query for Employees table contents as shown:

Odata_07.png

Here is another select statement choosing two columns from Customers table:

OData_08.png

You can easily query OData using C#, but this interface does not support SQL.

You can easily connect to OData using PowerBI, review this post:
http://hodentek.blogspot.com/2015/09/poweer-bi-using-data-from-odata-web.html

What is the difference between MS Query and Power Query?

As the name suggests Power Query is more powerful than MS Query. MS Query handles only ODBC data sources but Power Query can handle lot more.

Some details here:
http://hodentek.blogspot.com/2013/12/what-is-microsoft-power-query.html

Both MS Query and Power Query can be accessed in Microsoft EXCEL.

MS Query and Power Query in MS EXCEL 2010 Professional Plus and x32 bit

You have to install the EXCEL add-in for POWER Query from here:
http://www.microsoft.com/en-us/download/details.aspx?id=39379


Here is the ribbon tab for Power Query: