开发者社区> 石沫> 正文

SQLServer · 特性分析 · SQL Server中XML与JSON应用比较

简介: SQL Server是一种强大的数据库引擎,不仅性能卓越,稳定,功能还很强大,SQL Server 2016中已经支持JSON。这让我想到以前工作中经常使用的SQL XML,也对比一下他们几个关键领域的应用方法。这两种SQL特性,在实际的工作中也是常用的功能。
+关注继续查看

title: SQLServer · 特性分析 · SQL Server中XML与JSON应用比较

author: 石沫

背景

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

广义的XML与JSON

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

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以上版本运行。

路径表达式

1. SQL XML路径表达式

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

相对路径表达式

相对路径表达式由一个或多个步骤组成,步骤间以单斜杠或双斜杠(/ 或 //)分隔。例如:

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

绝对路径表达式

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

表达式 /child::ProductDescription 中的起始斜杠表示它是一个绝对路径表达式。 因为表达式开始处的斜杠返回上下文节点的文档根节点。

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

DECLARE
    @xml_sample xml 

SET @xml_sample=
    N'<root>
        <a e="111" >
            <b>
                <c>
                    <d>3333</d>
                </c>
            </b>
        </a>
        <f>222</f>
    </root>'

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

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

节点测试

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

- 节点名

节点名包括属性节点名称和元素节点名称。

- 节点类型

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

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

DECLARE
    @xml_sample xml 

SET @xml_sample=
    N'<root>
        <a e="111" >
            <!-- my comment --> 
            <b>
                <c>
                    <d>3333</d>
                </c>
            </b>
        </a>
        <f>222</f>
    </root>'

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

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

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

DECLARE
    @xml_sample xml 

SET @xml_sample=
    N'<root>
        <a e="111" >
            <!-- my comment --> 
            <b>
                <c>
                    <d>3333</d>
                </c>
            </b>
        </a>
        <f>222</f>
    </root>'

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

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

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

谓词

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

DECLARE 
    @xml_sample xml ,
    @i int =2

SET @xml_sample = N'
        <root>
          <a>
            <b>b1</b>
            <c>111</c>
          </a>
          <a>
            <b>b2</b>
            <c>222</c>
          </a>
        </root>
        '
SELECT 
    @xml_sample.query(N'/root/a[2]'),    
    @xml_sample.query(N'(/root/a/b)[1]'),
    @xml_sample.query(N'/root/a/b[text()="b2"]'),
    @xml_sample.query(N'/root/a[sql:variable("@i")]')

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

2. SQL JSON路径表达式

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

路径模式

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

DECLARE  
    @json_sample varchar(500)=
        N'{  
            "info":{    
              "type":1,  
              "address":{    
                "town":"Bristol",  
                "county":"Avon",  
                "country":"England"  
              },  
              "tags":["Sport", "Water polo"]  
           },  
           "type":"Basic"  
        }' 

SELECT 
    JSON_VALUE(@json_sample,'$.type')
SELECT 
    JSON_QUERY(@json_sample,'$.type')
SELECT 
    JSON_QUERY(@json_sample,'lax $.type')
SELECT 
    JSON_QUERY(@json_sample,'strict $.type')

路径

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

DECLARE  
    @json_sample varchar(500)=
        N'{"people":  
                  [  
                    { "name": "John", "surname": "Doe" },  
                    { "name": "Jane", "surname": null, "active": true }  
                  ]  
        } '

SELECT 
    JSON_VALUE(@json_sample,'$.people[0].name') ,
    JSON_VALUE(@json_sample,'$.people[1].active'),
    JSON_QUERY(@json_sample,'$.people[1]'),
    JSON_QUERY(@json_sample,'$')

查询解析

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

<ROOT>
        <Customers CustomerID="VINET" ContactName="Paul Henriot">
            <Orders>
                <EmployeeID>5</EmployeeID>
                <OrderDate>1996-07-04</OrderDate>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>11</ProductID>
                    <Quantity>12</Quantity>
                </Order_Details>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>42000</ProductID>
                    <Quantity>11111</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
        <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
            <Orders>
                <EmployeeID>3</EmployeeID>
                <OrderDate>1996-07-06</OrderDate>
                <Order_Details>
                    <OrderID>10283</OrderID>
                    <ProductID>22</ProductID>
                    <Quantity>3</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
    </ROOT>

JSON :

{
  "ROOT": {
    "Customers": [
      {
        "CustomerID": "VINET",
        "ContactName": "Paul Henriot",
        "Orders": {
          "EmployeeID": "5",
          "OrderDate": "1996-07-04",
          "Order_Details": [
            {
              "OrderID": "10248",
              "ProductID": "11",
              "Quantity": "12"
            },
            {
              "OrderID": "10248",
              "ProductID": "42000",
              "Quantity": "11111"
            }
          ]
        }
      },
      {
        "CustomerID": "LILAS",
        "ContactName": "Carlos Gonzlez",
        "Orders": {
          "EmployeeID": "3",
          "OrderDate": "1996-07-06",
          "Order_Details": {
            "OrderID": "10283",
            "ProductID": "22",
            "Quantity": "3"
          }
        }
      }
    ]
  }
}

1. SQL XML查询解析

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

1.1 得到子实例片段

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


DECLARE 
    @xml_sample xml

SET @xml_sample =N'
    <ROOT>
        <Customers CustomerID="VINET" ContactName="Paul Henriot">
            <Orders>
                <EmployeeID>5</EmployeeID>
                <OrderDate>1996-07-04</OrderDate>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>11</ProductID>
                    <Quantity>12</Quantity>
                </Order_Details>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>42000</ProductID>
                    <Quantity>11111</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
        <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
            <Orders>
                <EmployeeID>3</EmployeeID>
                <OrderDate>1996-07-06</OrderDate>
                <Order_Details>
                    <OrderID>10283</OrderID>
                    <ProductID>22</ProductID>
                    <Quantity>3</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
    </ROOT>
'

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

1.2 得到元素节点文本

得到元素文本值是最基本的操,现在要获得 CustomerID="VINET" 的EmployeeID:

DECLARE 
    @xml_sample xml

SET @xml_sample =N'
    <ROOT>
        <Customers CustomerID="VINET" ContactName="Paul Henriot">
            <Orders>
                <EmployeeID>5</EmployeeID>
                <OrderDate>1996-07-04</OrderDate>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>11</ProductID>
                    <Quantity>12</Quantity>
                </Order_Details>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>42000</ProductID>
                    <Quantity>11111</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
        <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
            <Orders>
                <EmployeeID>3</EmployeeID>
                <OrderDate>1996-07-06</OrderDate>
                <Order_Details>
                    <OrderID>10283</OrderID>
                    <ProductID>22</ProductID>
                    <Quantity>3</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
    </ROOT>
'

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

1.3 得到属性值

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

DECLARE 
    @xml_sample xml

SET @xml_sample =N'
    <ROOT>
        <Customers CustomerID="VINET" ContactName="Paul Henriot">
            <Orders>
                <EmployeeID>5</EmployeeID>
                <OrderDate>1996-07-04</OrderDate>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>11</ProductID>
                    <Quantity>12</Quantity>
                </Order_Details>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>42000</ProductID>
                    <Quantity>11111</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
        <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
            <Orders>
                <EmployeeID>3</EmployeeID>
                <OrderDate>1996-07-06</OrderDate>
                <Order_Details>
                    <OrderID>10283</OrderID>
                    <ProductID>22</ProductID>
                    <Quantity>3</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
    </ROOT>
'

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

1.4 构建结果集

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

DECLARE 
    @xml_sample xml

SET @xml_sample =N'
    <ROOT>
        <Customers CustomerID="VINET" ContactName="Paul Henriot">
            <Orders>
                <EmployeeID>5</EmployeeID>
                <OrderDate>1996-07-04</OrderDate>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>11</ProductID>
                    <Quantity>12</Quantity>
                </Order_Details>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>42000</ProductID>
                    <Quantity>11111</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
        <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
            <Orders>
                <EmployeeID>3</EmployeeID>
                <OrderDate>1996-07-06</OrderDate>
                <Order_Details>
                    <OrderID>10283</OrderID>
                    <ProductID>22</ProductID>
                    <Quantity>3</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
    </ROOT>
'

SELECT 
    CustomerID=T.c.value(N'../../@CustomerID',N'varchar(50)'),
    ContactName=T.c.value(N'../../@ContactName',N'varchar(50)'),
    EmployeeID=T.c.value(N'(../EmployeeID/text())[1]',N'int'),
    OrderDate=T.c.value(N'(../OrderDate/text())[1]',N'datetime'),
    OrderID=T.c.value(N'(OrderID/text())[1]',N'int'),
    ProductID=T.c.value(N'(ProductID/text())[1]',N'int'),
    Quantity=T.c.value(N'(Quantity/text())[1]',N'int')
FROM @xml_sample.nodes(N'ROOT/Customers/Orders/Order_Details') T(c)

第二种:openxml 方法

DECLARE 
    @idoc int, 
    @doc varchar(4000)

SET @doc=N'
    <ROOT>
        <Customers CustomerID="VINET" ContactName="Paul Henriot">
            <Orders>
                <EmployeeID>5</EmployeeID>
                <OrderDate>1996-07-04</OrderDate>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>11</ProductID>
                    <Quantity>12</Quantity>
                </Order_Details>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>42000</ProductID>
                    <Quantity>11111</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
        <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
            <Orders>
                <EmployeeID>3</EmployeeID>
                <OrderDate>1996-07-06</OrderDate>
                <Order_Details>
                    <OrderID>10283</OrderID>
                    <ProductID>22</ProductID>
                    <Quantity>3</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
    </ROOT>
'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc; 

SELECT    
    *
FROM  OPENXML (@idoc, '/ROOT/Customers/Orders/Order_Details') 
WITH (
    CustomerID varchar(50)  '../../@CustomerID',
    ContactName varchar(50)  '../../@ContactName',
    EmployeeID  int  '(../EmployeeID/text())[1]',
    OrderDate datetime '(../OrderDate/text())[1]',
    OrderID int '(OrderID/text())[1]',
    ProductID int '(ProductID/text())[1]',
    Quantity int '(Quantity/text())[1]'

)

EXEC sp_xml_removedocument @idoc; 

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

1.5 表列处理

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

2. SQL JSON查询解析

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

2.1 得到JSON片段

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


DECLARE 
    @json_sample varchar(5000)

SET @json_sample=N'{
  "ROOT": {
    "Customers": [
      {
        "CustomerID": "VINET",
        "ContactName": "Paul Henriot",
        "Orders": {
          "EmployeeID": "5",
          "OrderDate": "1996-07-04",
          "Order_Details": [
            {
              "OrderID": "10248",
              "ProductID": "11",
              "Quantity": "12"
            },
            {
              "OrderID": "10248",
              "ProductID": "42000",
              "Quantity": "11111"
            }
          ]
        }
      },
      {
        "CustomerID": "LILAS",
        "ContactName": "Carlos Gonzlez",
        "Orders": {
          "EmployeeID": "3",
          "OrderDate": "1996-07-06",
          "Order_Details": {
            "OrderID": "10283",
            "ProductID": "22",
            "Quantity": "3"
          }
        }
      }
    ]
  }
}
'

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

2.2 得到节点值

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

DECLARE 
    @json_sample varchar(5000)

SET @json_sample=N'{
  "ROOT": {
    "Customers": [
      {
        "CustomerID": "VINET",
        "ContactName": "Paul Henriot",
        "Orders": {
          "EmployeeID": "5",
          "OrderDate": "1996-07-04",
          "Order_Details": [
            {
              "OrderID": "10248",
              "ProductID": "11",
              "Quantity": "12"
            },
            {
              "OrderID": "10248",
              "ProductID": "42000",
              "Quantity": "11111"
            }
          ]
        }
      },
      {
        "CustomerID": "LILAS",
        "ContactName": "Carlos Gonzlez",
        "Orders": {
          "EmployeeID": "3",
          "OrderDate": "1996-07-06",
          "Order_Details": {
            "OrderID": "10283",
            "ProductID": "22",
            "Quantity": "3"
          }
        }
      }
    ]
  }
}
'

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

2.3 得到结果集

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

DECLARE 
    @json_sample varchar(5000)

SET @json_sample=N'{
  "ROOT": {
    "Customers": [
      {
        "CustomerID": "VINET",
        "ContactName": "Paul Henriot",
        "Orders": {
          "EmployeeID": "5",
          "OrderDate": "1996-07-04",
          "Order_Details": [
            {
              "OrderID": "10248",
              "ProductID": "11",
              "Quantity": "12"
            },
            {
              "OrderID": "10248",
              "ProductID": "42000",
              "Quantity": "11111"
            }
          ]
        }
      },
      {
        "CustomerID": "LILAS",
        "ContactName": "Carlos Gonzlez",
        "Orders": {
          "EmployeeID": "3",
          "OrderDate": "1996-07-06",
          "Order_Details": {
            "OrderID": "10283",
            "ProductID": "22",
            "Quantity": "3"
          }
        }
      }
    ]
  }
}
'
;WITH CustomersJ
AS
(
    SELECT 
        T.* 
    FROM OPENJSON(@json_sample,N'$.ROOT.Customers') 
    WITH (
        CustomerID varchar(50)  N'$.CustomerID' ,
        ContactName varchar(50) N'$.ContactName',
        EmployeeID int N'$.Orders.EmployeeID',
        OrderDate datetime N'$.Orders.OrderDate',
        Orders nvarchar(max) AS JSON
    )  T
)

SELECT 
    CJ.CustomerID,CJ.ContactName,CJ.EmployeeID,
    CJ.OrderDate,O.*
FROM CustomersJ  CJ
CROSS APPLY OPENJSON(CJ.Orders,N'$.Order_Details')
WITH (
    OrderID int N'$.OrderID' ,
    ProductID int N'$.ProductID',
    Quantity int N'$.Quantity'
) O

2.4 表列处理

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

生成实例

1. SQL XML实例生成

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

1.1 常量直接赋值

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

DECLARE 
    @xml_sample xml

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

如何验证这是一个可用的xml,很简单,执行一下这个语句,变量是xml,如果你的赋值不是xml, 检查是通不过去的,这个有自检查机制保证,如果不是合规的,就会抛错:
Msg 9436, Level 16, State 1, Line 6
XML parsing: line 1, character 16, end tag does not match start tag

1.2 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。然后需构造为:

<ROOT>
        <Customers CustomerID="VINET" ContactName="Paul Henriot">
            <Orders>
                <EmployeeID>5</EmployeeID>
                <OrderDate>1996-07-04</OrderDate>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>11</ProductID>
                    <Quantity>12</Quantity>
                </Order_Details>
                <Order_Details>
                    <OrderID>10248</OrderID>
                    <ProductID>42000</ProductID>
                    <Quantity>11111</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
        <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
            <Orders>
                <EmployeeID>3</EmployeeID>
                <OrderDate>1996-07-06</OrderDate>
                <Order_Details>
                    <OrderID>10283</OrderID>
                    <ProductID>22</ProductID>
                    <Quantity>3</Quantity>
                </Order_Details>
            </Orders>
        </Customers>
    </ROOT>

下面实现:

SELECT 
    CustomerID N'@CustomerID',
    ContactName N'@ContactName',
    EmployeeID N'Orders/EmployeeID',
    OrderDate N'Orders/OrderDate',
    Orders= (
        SELECT 
            OrderID ,ProductID,Quantity
        FROM tb_xml_sample I1
        WHERE I1.EmployeeID=O1.EmployeeID
        AND I1.OrderDate=O1.OrderDate
        FOR XML PATH(N'Order_Details'),TYPE
    )
FROM tb_xml_sample O1
GROUP BY CustomerID,ContactName,EmployeeID,OrderDate
ORDER BY 1 DESC
FOR XML PATH(N'Customers'),ROOT(N'ROOT')

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

1.3 大容量加载

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

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

2. SQL JSON实例生成

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

2.1 常量直接赋值

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

DECLARE 
    @json_sample nvarchar(500)

SET @json_sample=N'{
"Order":{
    "OrderID":1222 ,
    "OrderDate": "2016-07-08 00:00:000" ,
    "OrderSalary": 1000
    }
}
'
SELECT ISJSON(@json_sample)

SET @json_sample=N'
"Order":{
    "OrderID":1222 ,
    "OrderDate": "2016-07-08 00:00:000" ,
    "OrderSalary": 1000
}
'
SELECT ISJSON(@json_sample)

2.2 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

{
  "ROOT": {
    "Customers": [
      {
        "CustomerID": "VINET",
        "ContactName": "Paul Henriot",
        "Orders": {
          "EmployeeID": "5",
          "OrderDate": "1996-07-04",
          "Order_Details": [
            {
              "OrderID": "10248",
              "ProductID": "11",
              "Quantity": "12"
            },
            {
              "OrderID": "10248",
              "ProductID": "42000",
              "Quantity": "11111"
            }
          ]
        }
      },
      {
        "CustomerID": "LILAS",
        "ContactName": "Carlos Gonzlez",
        "Orders": {
          "EmployeeID": "3",
          "OrderDate": "1996-07-06",
          "Order_Details": {
            "OrderID": "10283",
            "ProductID": "22",
            "Quantity": "3"
          }
        }
      }
    ]
  }
}

JSON对象生成,依然用这个表tb_xml_sample:

SELECT 
    DISTINCT
        CustomerID N'CustomerID' ,
        ContactName N'ContactName',
        EmployeeID N'Orders.EmployeeID',
        OrderDate  N'Orders.OrderDate', 
        Orders=
        (   SELECT 
                OrderID N'OrderID',
                ProductID N'ProductID',
                Quantity N'Quantity'
            FROM tb_xml_sample I
            WHERE I.EmployeeID=O.EmployeeID
            AND I.OrderDate=O.OrderDate
            FOR JSON PATH,ROOT(N'Order_Details')
        ) 
FROM tb_xml_sample O
ORDER BY 1 DESC
FOR JSON PATH,ROOT(N'ROOT')

这段其实运行是有错误的,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 同一层次位置 ,要完全构造成与上面的一样,目前看来存在问题,这个地方需要继续跟进下去,或许有更多的办法或产品更新 。
  
SELECT 
    DISTINCT
        CustomerID N'CustomerID' ,
        ContactName N'ContactName',
        EmployeeID N'EmployeeID',
        OrderDate  N'OrderDate', 
        Orders=
        (   SELECT 
                OrderID N'OrderID',
                ProductID N'ProductID',
                Quantity N'Quantity'
            FROM tb_xml_sample I
            WHERE I.EmployeeID=O.EmployeeID
            AND I.OrderDate=O.OrderDate
            FOR JSON PATH,ROOT(N'Order_Details')
        ) 
FROM tb_xml_sample O
ORDER BY 1 DESC
FOR JSON PATH,ROOT(N'ROOT')

2.3 大容量加载

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

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

实例更改

1. SQL XML更改实例

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

- 插入 insert

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

DECLARE 
    @xml_sample xml
SET @xml_sample=N'
    <root>
        <name>yang</name>
        <sex>man</sex>
        <other></other>
        <hobby>    
            <item>football</item>
            <item>playgames</item>
        </hobby>
    </root>
'
--insert as first
SET @xml_sample.modify(N'
        insert <firstname>ay15</firstname>
        as first 
        into (/root)[1]
    ')
SELECT @xml_sample

--insret as last
SET @xml_sample.modify(N'
        insert <lastname>l.p</lastname>
        as last 
        into (/root)[1]
    ')
SELECT @xml_sample

--insert attribute
SET @xml_sample.modify(N'
        insert 
            (
                attribute age {"50"},
                attribute nation {"china"}
            )
        into (/root)[1]
    ')
SELECT @xml_sample

--insert text value: as first
SET @xml_sample.modify(N'
        insert 
            text{"this text |"}
        as first 
        into (/root/sex)[1]
    ')
SELECT @xml_sample

--insert text value: as last
SET @xml_sample.modify(N'
        insert 
            text{"|||this text "}
        as last 
        into (/root/sex)[1]
    ')
SELECT @xml_sample

- 删除 delete

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

DECLARE 
    @xml_sample xml
SET @xml_sample=N'
    <root>
        <name>yang</name>
        <sex>man</sex>
        <other></other>
        <hobby>    
            <item>football</item>
            <item>playgames</item>
        </hobby>
    </root>
'
--insert as first
SET @xml_sample.modify(N'
    delete (/root/hobby/item)[2]
    ')
SELECT @xml_sample

- 替代 replace value of

在文档中更新节点的值。

DECLARE 
    @xml_sample xml
SET @xml_sample=N'
    <root age="50">
        <name>yang</name>
        <sex>man</sex>
        <other></other>
        <hobby>    
            <item>football</item>
            <item>playgames</item>
        </hobby>
    </root>
'
--update text value
SET @xml_sample.modify(N'
    replace value of (/root/name/text())[1]
    with "zhao"
    ')
SELECT @xml_sample

--update attribute value
--update text value
SET @xml_sample.modify(N'
    replace value of (/root/@age)[1]
    with "10"
    ')
SELECT @xml_sample

2. SQL JSON更改实例

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

DECLARE 
    @json_sample varchar(500)
SET @json_sample='{
    "name":"yang",
    "sex": "man",
    "hobby":[
        "football",
        "playgames"
    ]
}'

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


SELECT 
    JSON_MODIFY(
        JSON_MODIFY(
            JSON_MODIFY(
                JSON_MODIFY(@json_sample
                    ,'$.name','zhao'), 
                        '$.lastname','ay15') ,
                        '    $.sex',null),
                                'append $.hobby','running')
            

索引

1. SQL XML的索引

这里不介绍,请参考:https://technet.microsoft.com/en-us/library/ms191497(v=sql.105).aspx

2. SQL JSON的索引

这里不介绍,请参考:https://msdn.microsoft.com/en-us/library/mt612798.aspx

总结

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
应用统计学与R语言实现学习笔记(十二)——主成分分析
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/ESA_DSQ/article/details/78062883 Chapter 12 Priciple Component Analysis 本篇是第十二章,内容是主成分分析。
1021 0
应用MaxCompute实现变压器局部放电相位分析
应用MaxCompute实现变压器局部放电相位分析 1 引言 随着智能电网建设的不断推进,智能化电力一次设备和常规电力设备的在线监测都得到了较大发展并成为趋势,监测数据日益庞大,电力设备在线监测系统在数据存储和处理方面面临巨大的技术挑战。
6156 0
Redis不同数据类型命令使用及应用场景
Redis不同数据类型命令使用及应用场景
2243 0
Web系统下Office文档的处理 之OpenXml应用分类资料
本文对常见的处理参考和工具提供一个简要的参考。 对于实际的解决方案参考如下地址(有很多的分类知识) http://blogs.msdn.com/b/ericwhite/archive/2008/10/20/eric-white-s-blog-s-table-of-contents.
1090 0
SQLServer · 最佳实践 · 透明数据加密在SQLServer的应用
背景 作为云计算的服务提供者,我们在向用户提供优秀的服务能力时会遇到一个合规的问题。在数据库领域,数据是极其敏感和珍贵的,保护好数据,就如保护好企业的生命线。因此,需要采取一些预防措施来帮助保护数据库的安全,如设计一个安全系统、加密机密资产以及在数据库服务器的周围构建防火墙。但是,如果遇到物理介质
2126 0
+关注
石沫
长期在电子商务行业从事SQL Server的设计,开发与维护,拥有10年的相关经验,擅长数据库的架构与设计,擅长数据库的性能优化,擅长数据库的自动化和智能化运维,从2014年开始, 在云计算领域坚持奋斗, 阿里云SQL Server系列产品的设计与规划者
13
文章
15
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
相关文档: 云数据库 OceanBase 版 可信账本数据库 云原生关系型数据库 PolarDB PostgreSQL引擎
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载