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.

Exam Objectives

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:

  • RAW
  • AUTO
  • PATH

In this exercise, you will learn to work with different modes of FOR XML clause.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.