SQL Server >> 关系运算符 OPENDATASOURCE \OPENJSON \OPENXML \OPENQUERY \OPENROWSET

OPENJSON

表值函数，解析JSON文本成集合。如果不指定WITH关键字，默认返回3个列，分别是key，value和type。参数2可以指定到具体的JSON节点。可以通过WITH关键字指定返回结果集合的列定义，WITH关键字即便是JOIN或者CROSS APPLY其他表的时候也是适用。

DECLARE @json NVARCHAR(MAX) = N'[  
  {  
    "Order": {  
      "Number":"SO43659",  
      "Date":"2011-05-31T00:00:00"  
    },  
    "AccountNumber":"AW29825",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":1  
    }  
  },  
  {  
    "Order": {  
      "Number":"SO43661",  
      "Date":"2011-06-01T00:00:00"  
    },  
    "AccountNumber":"AW73565",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":3  
    }  
  }
]'  
   
SELECT *
FROM OPENJSON ( @json )  
WITH (   
              Number   VARCHAR(200)   '$.Order.Number',  
              Date     DATETIME       '$.Order.Date',  
              Customer VARCHAR(200)   '$.AccountNumber',  
              Quantity INT            '$.Item.Quantity',  
              [Order]  NVARCHAR(MAX)  AS JSON  
 )

 结果

OPENXML

表值函数，解析XML成集合。需要传入文本作为参数给到sp_xml_preparedocument返回XML文档数据编号，OPENXML打开解析sp_xml_preparedocument准备好的XML数据。sp_xml_removedocument移除sp_xml_preparedocument生成的XML数据。这点有点像游标的操作方法。可以通过WITH关键字指定返回结果集合的列定义，WITH关键字即便是JOIN或者CROSS APPLY其他表的时候也是适用。

DECLARE @idoc INT, @doc VARCHAR(1000);   
SET @doc ='  
<ROOT>  
<Customer CustomerID="VINET" ContactName="Paul Henriot">  
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"   
           OrderDate="1996-07-04T00:00:00">  
      <OrderDetail ProductID="11" Quantity="12"/>  
      <OrderDetail ProductID="42" Quantity="10"/>  
   </Order>  
</Customer>  
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">v  
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"   
           OrderDate="1996-08-16T00:00:00">  
      <OrderDetail ProductID="72" Quantity="3"/>  
   </Order>  
</Customer>  
</ROOT>';   
  
--Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;   
  
-- SELECT stmt using OPENXML rowset provider  
SELECT *  
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)   
         WITH (OrderID       int         '../@OrderID',
               CustomerID  varchar(10) '../@CustomerID',
               OrderDate   datetime    '../@OrderDate',
               ProdID      int         '@ProductID',
               Qty         int         '@Quantity');

结果

OPENDATASOURCE

作用类似于linked server，只不过linked server是通过服务器的对象链接，更加简便。而OPENSOURCE是通过指定连接驱动再跟着数据库表对象。从这点看它的实用性可定不及linked server。

SELECT *  
FROM OPENDATASOURCE('SQLNCLI',  
    'Data Source=London\Payroll;Integrated Security=SSPI')  
    .AdventureWorks2012.HumanResources.Employee;

OPENROWSET

用处比较多的一个语句，可以用来打开连接到第三方数据源，这里的数据源不限于数据库（mysql，sql server），也可以是EXCEL、CSV、TXT。

打开平面数据文件

SELECT *
FROM OPENROWSET(BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt',
    FIRSTROW=2,
    FORMAT='CSV') AS cars;

打开EXCEL文件见另外随笔SQL Server ->> 自动创建表并从文件加载数据

打开SQL SERVER数据库连接。（这里显然实用性也是不及linked server，如果是经常访问的数据库，显然linked server的优势更大，使用更加简便。）

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
     'SELECT GroupName, Name, DepartmentID
      FROM AdventureWorks2012.HumanResources.Department
      ORDER BY GroupName, Name') AS a;

OPENQUERY

 在指定的链接服务器上执行指定的DML操作。客户以是INSERT\DELETE\UPDATE\SELECT。前提是服务器是 OLE DB 数据源。

参考：

OPENJSON

OPENXML

OPENSOURCE

OPENROWSET

OPENQUERY