SQLServer中XML與JSON應用比較

石沫01發表於2016-08-03

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，也不必理解那些深奧難懂大概念，用最簡單的例項，處理工作總最需要的知識。希望對大家有用。

還在比較 JSON 和 XML？Stop！
2015-11-29
JSONXML
Xml 功能在erp report中應用的模式比較
2006-09-13
XML模式
XML 與 JSON 優劣對比
2019-02-06
XMLJSON
Rust與Go在區塊鏈中的應用比較 - definoobs
2022-11-07
RustGo區塊鏈
Oracle與SQL Server在企業應用中的比較(轉)
2007-08-16
OracleSQLServer
應用SQLServer For XML 生成XML避免在C# 拼字串
2014-07-09
SQLServerXMLC#字串
java中四種操作xml方式的比較
2010-03-25
JavaXML
就Tim Bray對JSON和XML的比較的幾點看法
2008-01-03
JSONXML
XML與JSON
2020-12-22
XMLJSON
探索多種資料格式：JSON、YAML、XML、CSV等資料格式詳解與比較
2024-03-28
JSONYAMLXML
json解析效能比較（gson與jackson）
2014-09-11
JSON
DDD中事件與命令比較
2024-02-25
事件
Protobuf 為啥比 JSON、XML 牛？
2021-11-20
JSONXML
原生移動應用框架React Native與Flutter比較
2018-10-17
框架React NativeFlutter
SqlServer索引的原理與應用
2014-04-18
SQLServer索引
SSD在SQLServer中的應用
2013-12-23
SQLServer
XML與JSON（在更）
2020-10-12
XMLJSON
mysql中count(1)與count(*)比較
2016-04-07
MySql
微服務中GraphQL與RESTful比較
2024-04-07
微服務REST
XML Schema和XML DTD的資料型別比較
2009-06-02
XML資料型別
Closure的應用和替代方案比較
2017-05-04
java中四種操作（DOM、SAX、JDOM、DOM4J）xml方式詳解與比較
2011-03-21
JavaXML
oracle 的臨時表解析及與其SQLSERVER的比較（轉載）
2007-04-18
OracleSQLServer
XML資料讀取方式效能比較
2012-08-04
XML
sqlserver在JAVA中的應用 (轉)
2007-08-16
SQLServerJava
action中json的應用
2014-04-24
JSON
JSON 與XML相比優點
2018-09-05
JSONXML
json與xml的區別
2012-10-22
JSONXML
Mysql中的Btree與Hash索引比較
2021-09-09
MySql索引
python比較json/dictionary的庫
2019-01-16
PythonJSON
iOS:原生應用 VS Flutter VS GICXMLLayout 比較
2019-01-25
iOSFlutterXML
MySQL中MyISAM引擎與InnoDB引擎效能比較
2010-07-22
MySql
Java中對HashMap的深度分析與比較
2007-08-24
JavaHashMap
XML與JSON學習歸納
2020-12-23
XMLJSON
Java幾種常用JSON庫效能比較
2019-04-19
JavaJSON
Jackson Gson Json.simple 比較
2014-11-20
JSON
Vue與React比較
2018-09-06
VueReact
【Redis與Memcached比較】
2018-02-27
Redis