数据库内核月报－ 2016/07 - SQLServer · 特性分析 · XML与JSON应用比较 - 《数据库内核月报》

背景

SQL Server是一种强大的数据库引擎，不仅性能卓越，稳定，功能还很强大，SQL Server 2016中已经支持JSON。这让我想到以前工作中经常使用的SQL XML，也对比一下他们几个关键领域的应用方法。这两种SQL特性，在实际的工作中也是常用的功能，特别是JSON在最近发展非常火爆，SQL Server也不落后，很快就在SQL Server2016支持。

广义的XML与JSON

XML用于标记电子文件使其具有结构性的标记语言，可以用来标记数据、定义数据类型，是一种允许用户对自己的标记语言进行定义的源语言。 XML使用文档类型定义来组织数据；格式统一，跨平台，它早已成为业界公认的标准。JSON一种轻量级的数据交换格式，具有良好的可读和便于快速编写的特性。可在不同平台之间进行数据交换。JSON采用兼容性很高的、完全独立于语言文本格式。关于他的比较，这篇文介绍得非常全面：JSON与XML的区别比较

SQL XML与SQL JSON

使用 xml 数据类型，可以将 XML 文档和片段存储在 SQL Server 数据库中,可以创建 xml 类型的列和变量，并存储 XML 实例。可以选择性地将 XML 架构集合与 xml 数据类型的列、参数或变量进行关联。JSON是一种文本化的数据格式，与xml作为一种数据类型不同，JSON本身在SQL Server中只是一种字符串，用于存储非结构化的数据。根据以前的经验，在SQL Server T-SQL应用中，XML应用主要在下面几个方面：

路径表达式；
查询解析；
生成实例；
实例更改；
索引。而JSON其实也是类似的应用。下面将介绍SQL XML和SQL JSON具体在这几个方面的应用，请注意，下面的示例请在SQL Server 2016 RC3以上版本运行。

路径表达式

SQL XML路径表达式

xml数据类型自己是没有路径表达式，但SQL Server实现了XQuery语言，该语言支持SQL Server xml数据类型的操作。路径表达式是XQuery最重要的表达式之一，XQuery 路径表达式用于定位文档中的节点，如元素节点、属性节点和文本节点。言归正传，您需要理解以下概念：

相对路径表达式

相对路径表达式由一个或多个步骤组成，步骤间以单斜杠或双斜杠（/ 或 //）分隔。例如：


1. child::Features 是相对路径表达式，其中 Child 仅指上下文节点的子节点。

绝对路径表达式

child::Features 是相对路径表达式，其中 Child 仅指上下文节点的子节点。例如:
表达式 /child::ProductDescription 中的起始斜杠表示它是一个绝对路径表达式。因为表达式开始处的斜杠返回上下文节点的文档根节点。

轴

轴包含六个概念child,parent,attribute,seft,descendant-or-self,descendant，其中parent是逆向的，其余都是正向的。从名字上能够区分它们的用途。你甚至不必去了解其中含义，看看下面的示例就明白：


1. DECLARE
2. @xml_sample xml

4. SET @xml_sample=
5. N'<root>
6. <a e="111" >
7. <b>
8. <c>
9. <d>3333</d>
10. </c>
11. </b>
12. </a>
13. <f>222</f>
14. </root>'

16. SELECT
17. @xml_sample.query(N'/child::root/child::a') AS child ,
18. @xml_sample.query(N'/child::root/child::a/descendant::*') AS  descendant,
19. @xml_sample.query(N'/child::root/child::a/self::*') AS _self_ ,
20. @xml_sample.query(N'/child::root/child::a/parent::root/child::f') AS parent  ,
21. @xml_sample.query(N'/child::root/child::a/descendant-or-self::*') AS des_or_self ,
22. @xml_sample.value(N'(/child::root/child::a/attribute::e)[1]',N'int') AS attribute

上面使用了xml数据类型的操作方法query和value，应用路径表达式的轴节步骤得到不同结果，对比一下结果，就很清晰了。其中*表示以一个节点测试表示节点名称。

节点测试

节点测试是一个条件，并且是路径表达式中的轴步骤的第二个组件。在步骤中选定的所有节点都必须满足此条件，他有两种节点测试条件：

节点名
节点名包括属性节点名称和元素节点名称。
节点类型
节点类型包括comment()，node(),text(),processing-instruction() ，具体含义你也不必深入下去，有兴趣的可自查。

下面用一个示例来说明节点名和节点类型：


1. DECLARE
2. @xml_sample xml

4. SET @xml_sample=
5. N'<root>
6. <a e="111" >
7. <!-- my comment -->
8. <b>
9. <c>
10. <d>3333</d>
11. </c>
12. </b>
13. </a>
14. <f>222</f>
15. </root>'

17. SELECT
18. @xml_sample.query(N'/child::root/child::a') AS element_node ,
19. @xml_sample.value(N'(/child::root/child::a/attribute::e)[1]',N'int') AS attribute_node  ,
20. @xml_sample.value(N'(/child::root/child::f/child::text())[1]',N'int') AS  text_type ,
21. @xml_sample.query(N'/child::root/child::a/child::node()') AS node_type ,
22. @xml_sample.query(N'/child::root/child::a/child::comment()') AS comment_type

在实际应用中，节点测试用得最多的是节点名和text()类型，需要指出的是在处理大量的xml实例时，如果解析节点文本，不添加text()节点测试，性能会有所影响，可简单自测性能。

可能你会在写路径表达式的时候会感觉到很繁琐，那么，上面两个实例换种方式，就清晰了：


1. DECLARE
2. @xml_sample xml

4. SET @xml_sample=
5. N'<root>
6. <a e="111" >
7. <!-- my comment -->
8. <b>
9. <c>
10. <d>3333</d>
11. </c>
12. </b>
13. </a>
14. <f>222</f>
15. </root>'

17. SELECT
18. @xml_sample.query(N'/root/a') AS element_node ,
19. @xml_sample.value(N'(/root/a/@e)[1]',N'int') AS attribute_node  ,
20. @xml_sample.value(N'(/root/f/text())[1]',N'int') AS  text_type ,
21. @xml_sample.query(N'/root/a/node()') AS node_type ,
22. @xml_sample.query(N'/root/a/comment()') AS comment_type

24. SELECT
25. @xml_sample.query(N'/root/a') AS child ,
26. @xml_sample.query(N'/root/a/descendant::*') AS  descendant,
27. @xml_sample.query(N'/root/a/self::*') AS _self_ ,
28. @xml_sample.query(N'/root/a/../f') AS parent  ,
29. @xml_sample.query(N'/root/a/descendant-or-self::*') AS des_or_self ,
30. @xml_sample.value(N'(/root/a/@e)[1]',N'int') AS attribute

child被省略掉了，这是默认行为，你也不必写parent节点，直接用两点代替”..”，属性用@表示

谓词

谓词通过应用指定的测试来筛选节点序列。谓词表达式用方括号括起来并绑定到路径表达式中的最后一个节点。有点类似我们基础SQL中的谓词逻辑，比如WHERE条件，你可简单理解为一种条件关系，看下面的示例：


1. DECLARE
2. @xml_sample xml ,
3. @i int =2

5. SET @xml_sample = N'
6. <root>
7. <a>
8. <b>b1</b>
9. <c>111</c>
10. </a>
11. <a>
12. <b>b2</b>
13. <c>222</c>
14. </a>
15. </root>
16. '
17. SELECT
18. @xml_sample.query(N'/root/a[2]'),
19. @xml_sample.query(N'(/root/a/b)[1]'),
20. @xml_sample.query(N'/root/a/b[text()="b2"]'),
21. @xml_sample.query(N'/root/a[sql:variable("@i")]')

轴、节点测试和谓词是轴步骤的要素，还有一般步骤，这个很少用，有兴趣可以自行了解。

SQL JSON路径表达式

JSON中的路径表达式非常简单，你只需要理解下列两个核心概念就可以随心应手：

路径模式

JSON的路径模式有两种，一种是lax,另外一种是strict,默认的方式是lax。lax模式在路径表达式遇到错误时返回为空，而strict模式会抛出错误，请运行下列语句：


1. DECLARE
2. @json_sample varchar(500)=
3. N'{
4. "info":{
5. "type":1,
6. "address":{
7. "town":"Bristol",
8. "county":"Avon",
9. "country":"England"
10. },
11. "tags":["Sport", "Water polo"]
12. },
13. "type":"Basic"
14. }'

16. SELECT
17. JSON_VALUE(@json_sample,'$.type')
18. SELECT
19. JSON_QUERY(@json_sample,'$.type')
20. SELECT
21. JSON_QUERY(@json_sample,'lax $.type')
22. SELECT
23. JSON_QUERY(@json_sample,'strict $.type')

路径

JSON数据的上下文引用使用美元符号$表示，JSON中的各属性作为路径关键名称，比如 $.type，如果属性名称有空格，需要用双引号括起来。如果是数组，需要使用方括号表示位置。“.”表示对象的一个成员。例如：


1. DECLARE
2. @json_sample varchar(500)=
3. N'{"people":
4. [
5. { "name": "John", "surname": "Doe" },
6. { "name": "Jane", "surname": null, "active": true }
7. ]
8. } '

10. SELECT
11. JSON_VALUE(@json_sample,'$.people[0].name') ,
12. JSON_VALUE(@json_sample,'$.people[1].active'),
13. JSON_QUERY(@json_sample,'$.people[1]'),
14. JSON_QUERY(@json_sample,'$')

查询解析

现在我准备了两个实例，一个是xml，一个是JSON，他们表达的内容是一样的，以这个实例来对比一下查询解析功能。
xml :


1. <ROOT>
2. <Customers CustomerID="VINET" ContactName="Paul Henriot">
3. <Orders>
4. <EmployeeID>5</EmployeeID>
5. <OrderDate>1996-07-04</OrderDate>
6. <Order_Details>
7. <OrderID>10248</OrderID>
8. <ProductID>11</ProductID>
9. <Quantity>12</Quantity>
10. </Order_Details>
11. <Order_Details>
12. <OrderID>10248</OrderID>
13. <ProductID>42000</ProductID>
14. <Quantity>11111</Quantity>
15. </Order_Details>
16. </Orders>
17. </Customers>
18. <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
19. <Orders>
20. <EmployeeID>3</EmployeeID>
21. <OrderDate>1996-07-06</OrderDate>
22. <Order_Details>
23. <OrderID>10283</OrderID>
24. <ProductID>22</ProductID>
25. <Quantity>3</Quantity>
26. </Order_Details>
27. </Orders>
28. </Customers>
29. </ROOT>

JSON :


1. {
2. "ROOT": {
3. "Customers": [
4. {
5. "CustomerID": "VINET",
6. "ContactName": "Paul Henriot",
7. "Orders": {
8. "EmployeeID": "5",
9. "OrderDate": "1996-07-04",
10. "Order_Details": [
11. {
12. "OrderID": "10248",
13. "ProductID": "11",
14. "Quantity": "12"
15. },
16. {
17. "OrderID": "10248",
18. "ProductID": "42000",
19. "Quantity": "11111"
20. }
21. ]
22. }
23. },
24. {
25. "CustomerID": "LILAS",
26. "ContactName": "Carlos Gonzlez",
27. "Orders": {
28. "EmployeeID": "3",
29. "OrderDate": "1996-07-06",
30. "Order_Details": {
31. "OrderID": "10283",
32. "ProductID": "22",
33. "Quantity": "3"
34. }
35. }
36. }
37. ]
38. }
39. }

SQL XML查询解析

在SQL Server中，解析经常使用这些方法：query(),nodes(),value(),openxml,有时也用到exist方法来判定条件。

得到子实例片段

得到实例片段非常简单，使用query方法就好。例如得到CustomerID=“LILAS”的片段Customers信息：


1. DECLARE
2. @xml_sample xml

4. SET @xml_sample =N'
5. <ROOT>
6. <Customers CustomerID="VINET" ContactName="Paul Henriot">
7. <Orders>
8. <EmployeeID>5</EmployeeID>
9. <OrderDate>1996-07-04</OrderDate>
10. <Order_Details>
11. <OrderID>10248</OrderID>
12. <ProductID>11</ProductID>
13. <Quantity>12</Quantity>
14. </Order_Details>
15. <Order_Details>
16. <OrderID>10248</OrderID>
17. <ProductID>42000</ProductID>
18. <Quantity>11111</Quantity>
19. </Order_Details>
20. </Orders>
21. </Customers>
22. <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
23. <Orders>
24. <EmployeeID>3</EmployeeID>
25. <OrderDate>1996-07-06</OrderDate>
26. <Order_Details>
27. <OrderID>10283</OrderID>
28. <ProductID>22</ProductID>
29. <Quantity>3</Quantity>
30. </Order_Details>
31. </Orders>
32. </Customers>
33. </ROOT>
34. '

36. SELECT
37. @xml_sample.query(N'/ROOT/Customers[@CustomerID="LILAS"]')

得到元素节点文本

得到元素文本值是最基本的操作，现在要获得 CustomerID=”VINET” 的EmployeeID：


1. DECLARE
2. @xml_sample xml

4. SET @xml_sample =N'
5. <ROOT>
6. <Customers CustomerID="VINET" ContactName="Paul Henriot">
7. <Orders>
8. <EmployeeID>5</EmployeeID>
9. <OrderDate>1996-07-04</OrderDate>
10. <Order_Details>
11. <OrderID>10248</OrderID>
12. <ProductID>11</ProductID>
13. <Quantity>12</Quantity>
14. </Order_Details>
15. <Order_Details>
16. <OrderID>10248</OrderID>
17. <ProductID>42000</ProductID>
18. <Quantity>11111</Quantity>
19. </Order_Details>
20. </Orders>
21. </Customers>
22. <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
23. <Orders>
24. <EmployeeID>3</EmployeeID>
25. <OrderDate>1996-07-06</OrderDate>
26. <Order_Details>
27. <OrderID>10283</OrderID>
28. <ProductID>22</ProductID>
29. <Quantity>3</Quantity>
30. </Order_Details>
31. </Orders>
32. </Customers>
33. </ROOT>
34. '

36. SELECT
37. @xml_sample.value(N'(/ROOT/Customers[@CustomerID="VINET"]/Orders/EmployeeID/text())[1]',N'int')

得到属性值

属性值在路径表达式说过，可以使用attribute或者@标识。比如要得到ContactName:


1. DECLARE
2. @xml_sample xml

4. SET @xml_sample =N'
5. <ROOT>
6. <Customers CustomerID="VINET" ContactName="Paul Henriot">
7. <Orders>
8. <EmployeeID>5</EmployeeID>
9. <OrderDate>1996-07-04</OrderDate>
10. <Order_Details>
11. <OrderID>10248</OrderID>
12. <ProductID>11</ProductID>
13. <Quantity>12</Quantity>
14. </Order_Details>
15. <Order_Details>
16. <OrderID>10248</OrderID>
17. <ProductID>42000</ProductID>
18. <Quantity>11111</Quantity>
19. </Order_Details>
20. </Orders>
21. </Customers>
22. <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
23. <Orders>
24. <EmployeeID>3</EmployeeID>
25. <OrderDate>1996-07-06</OrderDate>
26. <Order_Details>
27. <OrderID>10283</OrderID>
28. <ProductID>22</ProductID>
29. <Quantity>3</Quantity>
30. </Order_Details>
31. </Orders>
32. </Customers>
33. </ROOT>
34. '

36. SELECT
37. CustomerID=T.c.value(N'@CustomerID',N'varchar(50)'),
38. ContactName=T.c.value(N'@ContactName',N'varchar(50)')
39. FROM @xml_sample.nodes(N'ROOT/Customers') T(c)

构建结果集

现在需要将订单细节和其他信息都生成一个结果集，这个可能觉得很麻烦，其实也不难，只要充分理解路径表达式，看看可以怎么做到？有两种方法：
第一种：nodes()方法


1. DECLARE
2. @xml_sample xml

4. SET @xml_sample =N'
5. <ROOT>
6. <Customers CustomerID="VINET" ContactName="Paul Henriot">
7. <Orders>
8. <EmployeeID>5</EmployeeID>
9. <OrderDate>1996-07-04</OrderDate>
10. <Order_Details>
11. <OrderID>10248</OrderID>
12. <ProductID>11</ProductID>
13. <Quantity>12</Quantity>
14. </Order_Details>
15. <Order_Details>
16. <OrderID>10248</OrderID>
17. <ProductID>42000</ProductID>
18. <Quantity>11111</Quantity>
19. </Order_Details>
20. </Orders>
21. </Customers>
22. <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
23. <Orders>
24. <EmployeeID>3</EmployeeID>
25. <OrderDate>1996-07-06</OrderDate>
26. <Order_Details>
27. <OrderID>10283</OrderID>
28. <ProductID>22</ProductID>
29. <Quantity>3</Quantity>
30. </Order_Details>
31. </Orders>
32. </Customers>
33. </ROOT>
34. '

36. SELECT
37. CustomerID=T.c.value(N'../../@CustomerID',N'varchar(50)'),
38. ContactName=T.c.value(N'../../@ContactName',N'varchar(50)'),
39. EmployeeID=T.c.value(N'(../EmployeeID/text())[1]',N'int'),
40. OrderDate=T.c.value(N'(../OrderDate/text())[1]',N'datetime'),
41. OrderID=T.c.value(N'(OrderID/text())[1]',N'int'),
42. ProductID=T.c.value(N'(ProductID/text())[1]',N'int'),
43. Quantity=T.c.value(N'(Quantity/text())[1]',N'int')
44. FROM @xml_sample.nodes(N'ROOT/Customers/Orders/Order_Details') T(c)

第二种：openxml 方法


1. DECLARE
2. @idoc int,
3. @doc varchar(4000)

5. SET @doc=N'
6. <ROOT>
7. <Customers CustomerID="VINET" ContactName="Paul Henriot">
8. <Orders>
9. <EmployeeID>5</EmployeeID>
10. <OrderDate>1996-07-04</OrderDate>
11. <Order_Details>
12. <OrderID>10248</OrderID>
13. <ProductID>11</ProductID>
14. <Quantity>12</Quantity>
15. </Order_Details>
16. <Order_Details>
17. <OrderID>10248</OrderID>
18. <ProductID>42000</ProductID>
19. <Quantity>11111</Quantity>
20. </Order_Details>
21. </Orders>
22. </Customers>
23. <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
24. <Orders>
25. <EmployeeID>3</EmployeeID>
26. <OrderDate>1996-07-06</OrderDate>
27. <Order_Details>
28. <OrderID>10283</OrderID>
29. <ProductID>22</ProductID>
30. <Quantity>3</Quantity>
31. </Order_Details>
32. </Orders>
33. </Customers>
34. </ROOT>
35. '

37. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

39. SELECT
40. *
41. FROM  OPENXML (@idoc, '/ROOT/Customers/Orders/Order_Details')
42. WITH (
43. CustomerID varchar(50)  '../../@CustomerID',
44. ContactName varchar(50)  '../../@ContactName',
45. EmployeeID  int  '(../EmployeeID/text())[1]',
46. OrderDate datetime '(../OrderDate/text())[1]',
47. OrderID int '(OrderID/text())[1]',
48. ProductID int '(ProductID/text())[1]',
49. Quantity int '(Quantity/text())[1]'

51. )

53. EXEC sp_xml_removedocument @idoc;

如果你对XQuery感兴趣，你还可以从过这种方式来处理，不过这种方式会比较复杂一点,SQL Server是支持XQuery语言操作，请读者自行尝试。

表列处理

假如你的XML片段存在表中，如果要解析处理，只需要使用CROSS APPLY生成一个多列的结果，用nodes方法就可以了，如果遇到条件，并且与列结合，你可能会用到exist方法和sql:column()来处理，上面的结果已经较复杂，这里不需要演示了。有兴趣可自己实战一下。

SQL JSON查询解析

JSON解析相对XML要简单得多，没有属性值，没有文本之类。会使用到的方法有：JSON_QUERY(),JSON_VALUE,ISJSON(),OPENJSON() 。

得到JSON片段

得到实例片段，使用JSON_QUERY方法。例如CustomerID=“LILAS”的片段Customers信息，这里没有xml那么强大，可以通过谓词来过滤，至少现在没有看到这个功能。因此只能指定简单的数组值。


1. DECLARE
2. @json_sample varchar(5000)

4. SET @json_sample=N'{
5. "ROOT": {
6. "Customers": [
7. {
8. "CustomerID": "VINET",
9. "ContactName": "Paul Henriot",
10. "Orders": {
11. "EmployeeID": "5",
12. "OrderDate": "1996-07-04",
13. "Order_Details": [
14. {
15. "OrderID": "10248",
16. "ProductID": "11",
17. "Quantity": "12"
18. },
19. {
20. "OrderID": "10248",
21. "ProductID": "42000",
22. "Quantity": "11111"
23. }
24. ]
25. }
26. },
27. {
28. "CustomerID": "LILAS",
29. "ContactName": "Carlos Gonzlez",
30. "Orders": {
31. "EmployeeID": "3",
32. "OrderDate": "1996-07-06",
33. "Order_Details": {
34. "OrderID": "10283",
35. "ProductID": "22",
36. "Quantity": "3"
37. }
38. }
39. }
40. ]
41. }
42. }
43. '

45. SELECT
46. JSON_QUERY(@json_sample,N'$.ROOT.Customers[1]')

得到节点值

得到JSON的某个值，使用JSON_VALUE方法就好:


1. DECLARE
2. @json_sample varchar(5000)

4. SET @json_sample=N'{
5. "ROOT": {
6. "Customers": [
7. {
8. "CustomerID": "VINET",
9. "ContactName": "Paul Henriot",
10. "Orders": {
11. "EmployeeID": "5",
12. "OrderDate": "1996-07-04",
13. "Order_Details": [
14. {
15. "OrderID": "10248",
16. "ProductID": "11",
17. "Quantity": "12"
18. },
19. {
20. "OrderID": "10248",
21. "ProductID": "42000",
22. "Quantity": "11111"
23. }
24. ]
25. }
26. },
27. {
28. "CustomerID": "LILAS",
29. "ContactName": "Carlos Gonzlez",
30. "Orders": {
31. "EmployeeID": "3",
32. "OrderDate": "1996-07-06",
33. "Order_Details": {
34. "OrderID": "10283",
35. "ProductID": "22",
36. "Quantity": "3"
37. }
38. }
39. }
40. ]
41. }
42. }
43. '

45. SELECT
46. JSON_VALUE(@json_sample,N'$.ROOT.Customers[1].Orders.EmployeeID')

得到结果集

得到结果集，JSON只提供一种方法OPENJSON，没有丰富的路径表达式，因此解析会比较麻烦,下面示例演示与xml生成一样的结果集：


1. DECLARE
2. @json_sample varchar(5000)

4. SET @json_sample=N'{
5. "ROOT": {
6. "Customers": [
7. {
8. "CustomerID": "VINET",
9. "ContactName": "Paul Henriot",
10. "Orders": {
11. "EmployeeID": "5",
12. "OrderDate": "1996-07-04",
13. "Order_Details": [
14. {
15. "OrderID": "10248",
16. "ProductID": "11",
17. "Quantity": "12"
18. },
19. {
20. "OrderID": "10248",
21. "ProductID": "42000",
22. "Quantity": "11111"
23. }
24. ]
25. }
26. },
27. {
28. "CustomerID": "LILAS",
29. "ContactName": "Carlos Gonzlez",
30. "Orders": {
31. "EmployeeID": "3",
32. "OrderDate": "1996-07-06",
33. "Order_Details": {
34. "OrderID": "10283",
35. "ProductID": "22",
36. "Quantity": "3"
37. }
38. }
39. }
40. ]
41. }
42. }
43. '
44. ;WITH CustomersJ
45. AS
46. (
47. SELECT
48. T.*
49. FROM OPENJSON(@json_sample,N'$.ROOT.Customers')
50. WITH (
51. CustomerID varchar(50)  N'$.CustomerID' ,
52. ContactName varchar(50) N'$.ContactName',
53. EmployeeID int N'$.Orders.EmployeeID',
54. OrderDate datetime N'$.Orders.OrderDate',
55. Orders nvarchar(max) AS JSON
56. )  T
57. )

59. SELECT
60. CJ.CustomerID,CJ.ContactName,CJ.EmployeeID,
61. CJ.OrderDate,O.*
62. FROM CustomersJ  CJ
63. CROSS APPLY OPENJSON(CJ.Orders,N'$.Order_Details')
64. WITH (
65. OrderID int N'$.OrderID' ,
66. ProductID int N'$.ProductID',
67. Quantity int N'$.Quantity'
68. )

表列处理

表列如果存放的是JSON格式数据，你只需要注意所有列数据需要用CROSS JOIN得到所要处理的JSON对象，其他的也没有特别的。有兴趣可以自己去测试。

生成实例

SQL XML实例生成

生成xml有多种方式，常见的有：常量直接赋值，FOR XML子句，大容量加载：

常量直接赋值

声明一个xml数据类型变量，直接给赋值，这个是最常见的：例如：


1. DECLARE
2. @xml_sample xml

4. SET @xml_sample=N'<a><b>111</b></a>'

如何验证这是一个可用的xml,很简单，执行一下这个语句，变量是xml，如果你的赋值不是xml, 检查是通不过去的，这个有自检查机制保证，如果不是合规的，就会抛错：


1. Msg 9436, Level 16, State 1, Line 6
2. XML parsing: line 1, character 16, end tag does not match start tag

FOR XML子句

FOR XML子句可以将表内数据直接生成XML实例，FOR XML子句有四种方式：FOR XML AUTO,FOR XML PATH,FOR XMLEXPLICIT,FOR XML RAW。

RAW 模式
将为 SELECT 语句所返回行集中的每行生成一个元素。
AUTO 模式
将基于指定 SELECT 语句的方式来使用试探性方法在 XML 结果中生成嵌套。您对生成的 XML 的形状具有最低限度的控制能力。除了 AUTO 模式的试探性方法生成的 XML 形状之外，还可以编写 FOR XML 查询来生成 XML 层次结构。
EXPLICIT 模式允许对 XML 的形状进行更多控制。您可以随意混合属性和元素来确定 XML 的形状。由于执行查询而生成的结果行集需要具有特定的格式。此行集格式随后将映射为 XML 形状。使用 EXPLICIT 模式能够随意混合属性和元素、创建包装和嵌套的复杂属性、创建用空格分隔的值（例如 OrderID 属性可能具有一列排序顺序 ID 值）以及混合内容。
PATH 模式与嵌套 FOR XML 查询功能一起以较简单的方式提供了 EXPLICIT 模式的灵活性。

实际上，用的最多的是auto和path模式，就我个人习惯，一直用path模式。下面一个示例，看看如何构建一个复杂的xml，请注意构造时属性，元素以及文本的方法：
将下列列表（其实是上面的示例结果）生成一个xml实例：

将这个结果集放在一个表中tb_xml_sample。然后需构造为：


1. <ROOT>
2. <Customers CustomerID="VINET" ContactName="Paul Henriot">
3. <Orders>
4. <EmployeeID>5</EmployeeID>
5. <OrderDate>1996-07-04</OrderDate>
6. <Order_Details>
7. <OrderID>10248</OrderID>
8. <ProductID>11</ProductID>
9. <Quantity>12</Quantity>
10. </Order_Details>
11. <Order_Details>
12. <OrderID>10248</OrderID>
13. <ProductID>42000</ProductID>
14. <Quantity>11111</Quantity>
15. </Order_Details>
16. </Orders>
17. </Customers>
18. <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
19. <Orders>
20. <EmployeeID>3</EmployeeID>
21. <OrderDate>1996-07-06</OrderDate>
22. <Order_Details>
23. <OrderID>10283</OrderID>
24. <ProductID>22</ProductID>
25. <Quantity>3</Quantity>
26. </Order_Details>
27. </Orders>
28. </Customers>
29. </ROOT>

下面实现：


1. SELECT
2. CustomerID N'@CustomerID',
3. ContactName N'@ContactName',
4. EmployeeID N'Orders/EmployeeID',
5. OrderDate N'Orders/OrderDate',
6. Orders= (
7. SELECT
8. OrderID ,ProductID,Quantity
9. FROM tb_xml_sample I1
10. WHERE I1.EmployeeID=O1.EmployeeID
11. AND I1.OrderDate=O1.OrderDate
12. FOR XML PATH(N'Order_Details'),TYPE
13. )
14. FROM tb_xml_sample O1
15. GROUP BY CustomerID,ContactName,EmployeeID,OrderDate
16. ORDER BY 1 DESC
17. FOR XML PATH(N'Customers'),ROOT(N'ROOT')

关于PATH(N’’),ROOT(N’’),TYPE等指令，请G一下就明白，主要是生成层次结构及XML正确性验证。

大容量加载

如果xml是一个文件，也可以通过OPENROWSET将文件读取到SQL Server中，如果事先不知道 XML 文档的编码方式，并且数据在转换到 XML 之前被作为字符串或二进制数据而不是 XML 数据来传递，则建议将数据作为 varbinary 处理。
例如，上面的XML保存到文件，然后在SQL Server中读出来：


1. SELECT
2. xml_sample=CAST(CAST(T.c AS varbinary(max)) AS XML) ,
3. T.c
4. FROM OPENROWSET(BULK 'C:\xml_sample.xml', SINGLE_BLOB) T(c)

SQL JSON实例生成

与XML一样，生成JSON有多种方式，但常见的有常量直接赋值，FOR JSON子句，大容量加载。

常量直接赋值

这个最常用，也是最简单，但JSON在SQL Server是没有类型存在的，要验证JSON是否是合规的，使用ISJOIN判断,ISJOIN为1表示是合规的，为0表示不合规：


1. DECLARE
2. @json_sample nvarchar(500)

4. SET @json_sample=N'{
5. "Order":{
6. "OrderID":1222 ,
7. "OrderDate": "2016-07-08 00:00:000" ,
8. "OrderSalary": 1000
9. }
10. }
11. '
12. SELECT ISJSON(@json_sample)

14. SET @json_sample=N'
15. "Order":{
16. "OrderID":1222 ,
17. "OrderDate": "2016-07-08 00:00:000" ,
18. "OrderSalary": 1000
19. }
20. '
21. SELECT ISJSON(@json_sample)

FOR JSON子句

FOR JSON 有两种模式，一种是FOR JSON AUTO,另一种是FOR JSON PATH,FOR JSON有几个重要的参数：ROOT(JSON数据的根节点），INCLUDE_NULL_VALUES （处理空间节点时如何显示），WITHOUT_ARRAY_WRAPPER（是否使用方括号将对象包起来）。

FOR JSON AUTO模式
FOR JSON子句在使用PATH模式时，可以控制JSON格式的输出，可以创建复杂的JSON对象。
FOR JSON PATH模式
FOR JSON子句在使用AUTO模式时，JSON输出的格式是查询语句自动完成，因此不能灵活控制JSON的格式。

同样地，我们以查询分析语句那示例来构造JSON对象：


1. {
2. "ROOT": {
3. "Customers": [
4. {
5. "CustomerID": "VINET",
6. "ContactName": "Paul Henriot",
7. "Orders": {
8. "EmployeeID": "5",
9. "OrderDate": "1996-07-04",
10. "Order_Details": [
11. {
12. "OrderID": "10248",
13. "ProductID": "11",
14. "Quantity": "12"
15. },
16. {
17. "OrderID": "10248",
18. "ProductID": "42000",
19. "Quantity": "11111"
20. }
21. ]
22. }
23. },
24. {
25. "CustomerID": "LILAS",
26. "ContactName": "Carlos Gonzlez",
27. "Orders": {
28. "EmployeeID": "3",
29. "OrderDate": "1996-07-06",
30. "Order_Details": {
31. "OrderID": "10283",
32. "ProductID": "22",
33. "Quantity": "3"
34. }
35. }
36. }
37. ]
38. }
39. }

JSON对象生成，依然用这个表tb_xml_sample：


1. SELECT
2. DISTINCT
3. CustomerID N'CustomerID' ,
4. ContactName N'ContactName',
5. EmployeeID N'Orders.EmployeeID',
6. OrderDate  N'Orders.OrderDate',
7. Orders=
8. (   SELECT
9. OrderID N'OrderID',
10. ProductID N'ProductID',
11. Quantity N'Quantity'
12. FROM tb_xml_sample I
13. WHERE I.EmployeeID=O.EmployeeID
14. AND I.OrderDate=O.OrderDate
15. FOR JSON PATH,ROOT(N'Order_Details')
16. )
17. FROM tb_xml_sample O
18. ORDER BY 1 DESC
19. FOR JSON PATH,ROOT(N'ROOT')

21. 这段其实运行是有错误的，Property 'Orders' cannot be generated in JSON output due to a conflict with another column name or alias。而实际上，根据XML来看，这个地方出错是不应该的，我需要将EmployeeID/OrderDate 放在Orders下面是很正常的需求。目前的SQL JSON确实做不到（SQL Server 2016 RC3）。那么只有将EmployeeID/OrderDate与ContactName/CustomerID 同一层次位置 ,要完全构造成与上面的一样，目前看来存在问题，这个地方需要继续跟进下去，或许有更多的办法或产品更新 。

23. SELECT
24. DISTINCT
25. CustomerID N'CustomerID' ,
26. ContactName N'ContactName',
27. EmployeeID N'EmployeeID',
28. OrderDate  N'OrderDate',
29. Orders=
30. (   SELECT
31. OrderID N'OrderID',
32. ProductID N'ProductID',
33. Quantity N'Quantity'
34. FROM tb_xml_sample I
35. WHERE I.EmployeeID=O.EmployeeID
36. AND I.OrderDate=O.OrderDate
37. FOR JSON PATH,ROOT(N'Order_Details')
38. )
39. FROM tb_xml_sample O
40. ORDER BY 1 DESC
41. FOR JSON PATH,ROOT(N'ROOT')

大容量加载

大容量加载也是通过OPENROWSET ,与XML一样：


1. SELECT
2. xml_sample=CAST(CAST(T.c AS varbinary(max)) AS varchar(max)) ,
3. T.c
4. FROM OPENROWSET(BULK 'C:\json_sample.json', SINGLE_BLOB) T(c)

实例更改

SQL XML更改实例

实例修改在实际应用中会很少，这里的修改包括DML_XML,使用modify方法，包括删除，更新和插入。下列一个示例表示所有信息：

插入 insert
可以插入xml片段，可以插入属性，文本，注释，指令，CDATA部分数据，请看下面示例：


1. DECLARE
2. @xml_sample xml
3. SET @xml_sample=N'
4. <root>
5. <name>yang</name>
6. <sex>man</sex>
7. <other></other>
8. <hobby>
9. <item>football</item>
10. <item>playgames</item>
11. </hobby>
12. </root>

14. --insert as first
15. SET @xml_sample.modify(N'
16. insert <firstname>ay15</firstname>
17. as first
18. into (/root)[1]
19. ')
20. SELECT @xml_sample

22. --insret as last
23. SET @xml_sample.modify(N'
24. insert <lastname>l.p</lastname>
25. as last
26. into (/root)[1]
27. ')
28. SELECT @xml_sample

30. --insert attribute
31. SET @xml_sample.modify(N'
32. insert
33. (
34. attribute age {"50"},
35. attribute nation {"china"}
36. )
37. into (/root)[1]
38. ')
39. SELECT @xml_sample

41. --insert text value: as first
42. SET @xml_sample.modify(N'
43. insert
44. text{"this text |"}
45. as first
46. into (/root/sex)[1]
47. ')
48. SELECT @xml_sample

50. --insert text value: as last
51. SET @xml_sample.modify(N'
52. insert
53. text{"|||this text "}
54. as last
55. into (/root/sex)[1]
56. ')
57. SELECT @xml_sample

删除 delete
删除 XML 实例的节点。这个非常的简单：


1. DECLARE
2. @xml_sample xml
3. SET @xml_sample=N'
4. <root>
5. <name>yang</name>
6. <sex>man</sex>
7. <other></other>
8. <hobby>
9. <item>football</item>
10. <item>playgames</item>
11. </hobby>
12. </root>
13. '
14. --insert as first
15. SET @xml_sample.modify(N'
16. delete (/root/hobby/item)[2]
17. ')
18. SELECT @xml_sample

替代 replace value of 在文档中更新节点的值。


1. DECLARE
2. @xml_sample xml
3. SET @xml_sample=N'
4. <root age="50">
5. <name>yang</name>
6. <sex>man</sex>
7. <other></other>
8. <hobby>
9. <item>football</item>
10. <item>playgames</item>
11. </hobby>
12. </root>
13. '
14. --update text value
15. SET @xml_sample.modify(N'
16. replace value of (/root/name/text())[1]
17. with "zhao"
18. ')
19. SELECT @xml_sample

21. --update attribute value
22. --update text value
23. SET @xml_sample.modify(N'
24. replace value of (/root/@age)[1]
25. with "10"
26. ')
27. SELECT @xml_sample

SQL JSON更改实例

JSON的对象修改使用JSON_MODIFY方法，同样具有更新，插入，删除等操作，里面列举一示例：
语法：JSON_MODIFY ( expression , path , newValue )，特别留意path中可以指定lax,strict这个上面已经说过。另外还append关键字，表示追加一个新的值到数组中。


1. DECLARE
2. @json_sample varchar(500)
3. SET @json_sample='{
4. "name":"yang",
5. "sex": "man",
6. "hobby":[
7. "football",
8. "playgames"
9. ]
10. }'

12. SELECT
13. JSON_MODIFY(@json_sample,'$.name','zhao') ,  --update
14. JSON_MODIFY(@json_sample,'$.lastname','ay15') ,  --insert
15. JSON_MODIFY(@json_sample,'$.sex',null),  --delete
16. JSON_MODIFY(@json_sample,'append $.hobby','running') --add array element

19. SELECT
20. JSON_MODIFY(
21. JSON_MODIFY(
22. JSON_MODIFY(
23. JSON_MODIFY(@json_sample
24. ,'$.name','zhao'),
25. '$.lastname','ay15') ,
26. '$.sex',null),
27. 'append $.hobby','running')

索引

SQL XML的索引

这里不介绍，请参考：Indexes on XML Data Type Columns

SQL JSON的索引

这里不介绍，请参考：Index JSON data

总结

上面是SQL Server在XML和JSON方面的简单应用，也是日常工作中经常遇到了，你不必要去了解复杂的xml,XQuery，也不必理解那些深奥难懂大概念，用最简单的实例，处理工作总最需要的知识。希望对大家有用。

原文：http://mysql.taobao.org/monthly/2016/07/06/