Querying JSON, XML, and Temporal Data with T-SQL
The most common scenario with SQL Server is querying structured data. But many do not know that it also allows storing and querying semi-structured data using XML and JSON; as well as obtaining values from any point in time with temporal tables.
What you'll learn
Querying structured data is the most common scenario with SQL Server. However, there are cases where the schema is not known up front or more flexibility is needed. Additionally, sometimes it is required to know the value of a particular field at a specific point in time. In this course, Querying JSON, XML, and Temporal Data with T-SQL, you’ll gain the ability to use JSON and XML to store, modify, and query semi-structured data. First, you’ll learn how to work with JSON in SQL Server. Next, you’ll discover how to work with XML. Finally, you’ll explore how to retrieve values at any point in time with temporal tables. When you’re finished with this course, you’ll have the skills and knowledge of JSON, XML, and temporal tables needed to work with semi-structured data and historic data in SQL Server. Software required: Microsoft SQL Server.
Table of contents
- Version Check 0m
- Querying JSON in Microsoft SQL Server 4m
- What Is JSON? What About JSON in SQL Server? 6m
- Querying JSON Documents with T-SQL 5m
- Converting Data into JSON with FOR JSON PATH and AUTO 6m
- Using SQL JSON Functions: JSON_VALUE, JSON_QUERY, JSON_MODIFY, and IS_JSON 3m
- Parsing and Importing JSON with OPENROWSET and OPENJSON 7m
- Handling Missing Properties with LAX and STRICT Mode 5m
- Takeaway 2m
- Querying XML Data in Microsoft SQL Server with T-SQL 2m
- What is XML? How Do I Build Good XML? 6m
- Selecting Data from XML: OPENXML and the query and value Methods 11m
- Getting to Know the XPath Syntax and XQuery Language 9m
- Providing Rowset Views with OPENXML and the nodes Method 7m
- Updating XML with modify and the XML Data Modification Language 4m
- Filtering XML Data with Contains and Exist 4m
- Takeaway 2m
About the author
Xavier has a two passions. He loves the challenge of learning about complex topics and then sharing this newfound knowledge with others. The topics he is currently focused on are AI, ML, Generative AI, Search and Big Data. He is also an entrepreneur, project manager, technical author, trainer, and holds a few certifications with Cloudera, Microsoft, and the Scrum Alliance, along with having been awarted Microsoft MVP. He has spent a great deal of his career working on cutting-edge projects.... more