The Working with Non-relational Data module provides you with the instructions and devices to develop your hands on skills in the following topics.
- Working with JSON functions and clauses
- Working with FOR XML queries
Lab time: It will take approximately 60 minutes to complete this lab.
Seven exam objectives are covered in this lab:
- Demonstrate the use of JSONVALUE and JSONQUERY functions to extract values and objects from JSON text
- Demonstrate the use of ISJSON function to test whether a string contains a valid JSON
- Convert JSON collections to a rowset using the OPENJSON function
- Convert SQL Server data to JSON using the FOR JSON clause
- Retrieve results of a SQL query as XML using the FOR XML clause (use RAW, AUTO, EXPLICIT, and PATH)
- Demonstrate the use of TYPE Directive in FOR XML Queries
- Query the results of a FOR XML query using the xml methods query() and value()
Exercise 1 - Working with JSON Functions and Clauses
JSON is a popular format adopted for textual data. It is used to exchange data in mobile applications and store unstructured data in log files and non-SQL databases. SQL Server provides many built-in functions and clauses to work with JSON data that is stored in the databases.
In this exercise, you will learn to use different built-in JSON functions and clauses.
Exercise 2 - Working with FOR XML Queries
The FOR XML clause can be used in Transact-SQL statements to retrieve the database data in XML format. You need to consider the following rules while using the FOR XML clause in Transact-SQL statements:
- It can be used in an individual SELECT statement query only.
- It cannot be used in subqueries that involve SELECT statements.
- It can be used in subqueries that involve INSERT, UPDATE, and DELETE statements.
There are four modes that can be used with FOR XML clause. These are as follows:
In this exercise, you will learn to work with different modes of FOR XML clause.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.