背景

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应用主要在下面几个方面：

  1. 路径表达式；
  2. 查询解析；
  3. 生成实例；
  4. 实例更改；
  5. 索引。 而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实例：

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对象：

xml

  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.aspx)

SQL JSON的索引

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

总结

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