SELECT data from tables but return the data in either JSON or XML format.
- [Narrator] When retrieving data from SQL Server you have seen that we can change the format of the individual rows by saying we would like to format or convert specific values into currencies, into numerical values with fractions and other similar style changes. It is also possible to retrieve data in a shape that's actually different from the way we store the data. I mean, SELECT * FROM Waterfalls shows us that we can execute what looks like a table.
It's stored in a similar way inside SQL Server so it is no surprise that we get columns and rows back when we're storing columns and rows. However, this is not the only way SQL Server can return data to you. In a lot of scenarios people will ask you for the data from SQL Server with specific formatting. And you will pay attention to date, time, to numbers, to currencies, to locals, to things like Unicode and all sorts of interesting challenges when it comes to the underlying data. You will also be asked to pay attention to the format of the output.
So in SQL Server Management Studio, please pay attention to the following options. Click on the Query menu and notice that you can change the way the results are displayed. The bottom, click on Results To and change it to Text. The first entry is Results to Text, where as currently we get the results as a Grip. So I changed the type of results I want. I'll click on Execute and you'll notice suddenly there is a text version of the display at the bottom of the screen.
It shows us 2,513 rows but it also shows us the text instead of a formatted column. And you'd have to scroll to the right to see the additional columns. The interesting part about this, I will press CTRL Home, the interesting part about this is that you can select the text and manipulate it with Notepad and other tools. However, this is essentially a text file and we may want more structure than just spaces and tabs. So what we can do is now that we have a Text Output, we can ask SQL Server to provide a specific format of text.
In this example, if you're a web developer you might have heard of JSON. And I will actually ask SQL Server to do the hard work by taking the Waterfalls table and providing me the text in JSON format. Click on Execute. I will click in the window at the bottom, place my cursor at the end of the text and then press the key combination CTRL Home. What you see is the Java Script Object Notation being used to represent all of the data from the Waterfalls table.
Here we have Country, which is the name of the column. Then we have Iceland, then we have Region, name of the column and the value South. And WaterfallName, the name of our column and Skogafoss the name of the waterfall. And the Curly Braces here shows us the start and the end of the record or the row in SQL Server database. But in this case, in the Java Script Object Notation. Followed by a comma an another record and another and so forth.
There are many options and you should study the Help file for the variations that are available to you. In this example we used FOR JSON AUTO and you can actually control much more of the structure of JSON by specifying FOR JSON PATH. But for that please look at the Help file. We also have the option to specify another format XML. Click on Execute, click on the bottom Results Panel, CTRL Home and now you'll notice we're using the XML notation to bracket to open the element, Country the attribute, Iceland for the value, Region column, value South, Waterfall name is the Column and Skogafoss as the value.
We close the element and we have essentially well-formed XML being returned from SQL Server in a format that web developers and others might prefer. To find additional information about the XLM and JSON formatting select the keyword Select, press F1, open the App file, and notice on the left hand side it has a section for SELECT FOR. Click on the FOR Clause and you'll see it has additional information about the FOR XML and FOR JSON formatting options.
Scroll down to see the Syntax. And you'll remember we used FOR XML AUTO so you can see the FOR keyword is being specified, followed by an XML definition. We can ask for Row Explicit Path, and of course Auto as we used it. Similarly, we scroll down further we'll see the results for the JSON notation. We can specify JSON AUTO or JSON PATH and there are additional options for PATH to control the structure of the Output.
- Creating a new Azure SQL database
- Using basic SQL statements such as SELECT and INSERT
- Using SQL statements to update, modify, and delete data
- Adding functions to your SELECT statement to modify how numbers are displayed
- Filtering and formatting results
- Saving and sharing your work by creating views
- Sharing access to your database with other users