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的區別比較](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以上版本運行。 ## 路徑表達式 ### 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" > <c> <d>3333</d> </c> </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" >  <c> <d>3333</d> </c> </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" >  <c> <d>3333</d> </c> </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> b1 <c>111</c> </a> <a> b2 <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")]') ~~~ 軸、節點測試和謂詞是軸步驟的要素，還有一般步驟，這個很少用，有興趣可以自行了解。 ## 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" } } } ] } } ~~~ ### SQL XML查詢解析在SQL Server中，解析經常使用這些方法：query(),nodes(),value(),openxml,有時也用到exist方法來判定條件。 #### 得到子實例片段得到實例片段非常簡單，使用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"]') ~~~ #### 得到元素節點文本得到元素文本值是最基本的操作，現在要獲得 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') ~~~ #### 得到屬性值屬性值在路徑表達式說過，可以使用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) ~~~ #### 構建結果集現在需要將訂單細節和其他信息都生成一個結果集，這個可能覺得很麻煩，其實也不難，只要充分理解路徑表達式，看看可以怎么做到？有兩種方法：? 第一種：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語言操作，請讀者自行嘗試。 #### 表列處理假如你的XML片段存在表中，如果要解析處理，只需要使用CROSS APPLY生成一個多列的結果，用nodes方法就可以了，如果遇到條件，并且與列結合，你可能會用到exist方法和sql:column()來處理，上面的結果已經較復雜，這里不需要演示了。有興趣可自己實戰一下。 ### SQL JSON查詢解析 JSON解析相對XML要簡單得多，沒有屬性值，沒有文本之類。會使用到的方法有：JSON_QUERY(),JSON_VALUE,ISJSON(),OPENJSON() 。 #### 得到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]') ~~~ #### 得到節點值得到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') ~~~ #### 得到結果集得到結果集，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' ) ~~~ #### 表列處理表列如果存放的是JSON格式數據，你只需要注意所有列數據需要用CROSS JOIN得到所要處理的JSON對象，其他的也沒有特別的。有興趣可以自己去測試。 ## 生成實例 ### SQL XML實例生成生成xml有多種方式，常見的有：常量直接賦值，FOR XML子句，大容量加載： #### 常量直接賦值聲明一個xml數據類型變量，直接給賦值，這個是最常見的：例如： ~~~ DECLARE @xml_sample xml SET @xml_sample=N'<a>111</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 ~~~ #### 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實例： ![](https://box.kancloud.cn/2016-07-22_5791a6c75c72f.jpg)? 將這個結果集放在一個表中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正確性驗證。 #### 大容量加載如果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) ~~~ ## SQL JSON實例生成與XML一樣，生成JSON有多種方式，但常見的有常量直接賦值，FOR JSON子句，大容量加載。 #### 常量直接賦值這個最常用，也是最簡單，但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) ~~~ #### 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對象： ![](https://box.kancloud.cn/2016-07-22_5791a6c75c72f.jpg) ~~~ { "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') ~~~ #### 大容量加載大容量加載也是通過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) ~~~ ## 實例更改 ### 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 ~~~ ### 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') ~~~ ## 索引 ### SQL XML的索引這里不介紹，請參考：[Indexes on XML Data Type Columns](https://technet.microsoft.com/en-us/library/ms191497(v=sql.105).aspx) ### SQL JSON的索引這里不介紹，請參考：[Index JSON data](https://msdn.microsoft.com/en-us/library/mt612798.aspx) ## 總結上面是SQL Server在XML和JSON方面的簡單應用，也是日常工作中經常遇到了，你不必要去了解復雜的xml,XQuery，也不必理解那些深奧難懂大概念，用最簡單的實例，處理工作總最需要的知識。希望對大家有用。