在SQL Server 2016中存储JSON格式的数据

In this article, I continue to review the exciting features available in SQL Server 2016. One such feature is the long awaited T-SQL support for JSON formatted data. In this article we take a look at how JSON support will impact data warehouse solutions.

在本文中，我将继续回顾SQL Server 2016中可用的激动人心的功能。这样的功能之一是期待已久的T-SQL对JSON格式数据的支持。 在本文中，我们将研究JSON支持如何影响数据仓库解决方案。

背景 (Background)

Since the advent of EXtensible Markup Language (XML) many modern web applications have focused on providing data that is both human-readable and machine-readable. From a relational database perspective, SQL Server kept up with these modern web applications by providing support for XML data in a form of an XML data type and several functions that could be used to parse, query and manipulate XML formatted data.

自从可扩展标记语言（XML）出现以来，许多现代的Web应用程序都集中于提供人类可读和机器可读的数据。 从关系数据库的角度来看，SQL Server通过以XML数据类型的形式提供对XML数据的支持以及一些可用于解析，查询和操作XML格式的数据的功能，从而与这些现代Web应用程序保持同步。

As a result of being supported in SQL Server, data warehouse solutions based off SQL Server were then able to source XML-based OLTP data into a data mart. To illustrate this point, let’s take a look at the XML representation of our fictitious Fruit Sales data shown in Figure 1.

由于受到SQL Server支持，因此基于SQL Server的数据仓库解决方案便能够将基于XML的OLTP数据提供给数据集市。 为了说明这一点，让我们看一下图1所示的虚拟Fruit Sales数据的XML表示。

To process this data in data warehouse, we would first have to convert it into relational format of rows and columns using T-SQL XML built-in functions such as the nodes() function. Figure 2 shows a script that makes use of the nodes() function to convert the sample data shown in Figure 1.

要在数据仓库中处理此数据，我们首先必须使用T-SQL XML内置函数（例如， nodes（）函数）将其转换为行和列的关系格式。 图2显示了一个脚本，该脚本利用node（）函数来转换图1所示的样本数据。