天天看點
傳回

delphi使用MS Sql Server資料庫的分布式查詢delphi使用MS Sql Server資料庫的分布式查詢

目錄

delphi使用MS Sql Server資料庫的分布式查詢

一、連結伺服器

1.1、簡單的連結伺服器

1.2、含多資料庫架構的全連結伺服器

1.3、連結伺服器的理論與總結

                文法：

                參數：

                本系統存儲過程的傳回代碼值：

                結果集：

                說明：

                權限：

                案例：

                                A. 使用用于 SQL Server 的 Microsoft OLE DB 提供程式

                                                使用用于 SQL Server 的 OLE DB 建立連結伺服器

                                                在 SQL Server 的執行個體上建立連結伺服器

                                B. 使用用于 Jet 的 Microsoft OLE DB 提供程式

                                C. 使用用于 Oracle 的 Microsoft OLE DB 提供程式

                                D. 将 data_source 參數與用于 ODBC 的 Microsoft OLE DB 提供程式一起使用

                                E. 将 provider_string 參數與用于 ODBC 的 Microsoft OLE DB 提供程式一起使用

                                F. 在 Excel 電子表格上使用用于 Jet 的 Microsoft OLE DB 提供程式

                                G. 使用用于檢索服務的 Microsoft OLE DB 提供程式

                                H. 使用用于 Jet 的 Microsoft OLE DB 提供程式通路文本檔案

                                I. 使用用于 DB2 的 Microsoft OLE DB 提供程式

二、分布式資料查詢

2.1、開放資料源

                2.1.1、OpenDataSource函數

                                2.1.1.1、使用ACE.OLEDB資料引擎

                                2.1.1.2、使用Jet.OLEDB資料引擎

2.2、開放XML

                2.2.1、使用 OPENXML 編寫 XML

                                2.2.1.1、使用sp_xml_preparedocument

                                2.2.1.2、使用sp_xml_removedocument

                2.2.2、使用 OPENXML

                2.2.3、示例

                                A.使用帶有 OPENXML 的 SELECT 語句。

                                B. 為列和 XML 特性之間的映射指定 ColPattern

                                C. 獲得邊緣表格式的結果

三、依賴性-運作“連結伺服器”和“分布式查詢”有依賴性嗎？

3.1、“連結伺服器”和“分布式查詢”互相之間并無直接的依賴性

                3.1.1、“連結伺服器”和“分布式查詢”，各自獨立，兩者之間無必須的依賴性

                3.1.2、“分布式查詢”當涉及到資料源無法直接通路時：才需要先建立和調用“連結伺服器”

3.2、“連結伺服器”自身的依賴性

                3.2.1、MSDTC

                                3.2.1.1、MSDTC  : Distributed Transaction Coordinator （微軟分布式傳輸協調器）

                                3.2.1.2、MSDTC指向C:\Windows\System32\msdtc.exe

                                         3.2.1.3、MSDTC的易懂解釋：

                                         3.2.1.4、如何在 Windows Server 2012/2012R2 中配置 MSDTC，令其使用特定端口： 

                                         3.2.1.5、啟停MSDTC         

                                         3.2.1.6、MSDTC配置      

3.3、“分布式查詢”的依賴性

                3.3.1、OpenDataSource函數

                3.3.2、OpenRowSet函數

                3.3.3、OpenQuery函數

                3.3.4、OpenXML函數

                3.3.5、如果在對上述函數引用的内部參數中，需要指向無法直接通路的資料源或資料庫對象，則需要連結伺服器來配合調用。

                 3.3.6、如果你的應用依賴的Sql Server在用戶端一體，對安全性有特别要求，請使用高版本的MS Sql Server，它們和作業系統一起都内置了強密碼的安全政策。

本部落格相關：

喜歡的話，就在下面點個贊、收藏就好了，友善看下次的分享：

delphi使用MS Sql Server資料庫的分布式查詢

一、連結伺服器

1.1、簡單的連結伺服器 

EXEC sp_addlinkedserver   '119.29.5.138' , 'SQL Server' 
-- :建立連接配接伺服器  第一步建立連接配接  IP方式來控制  （需要驗證使用者名、密碼的參數）
go
-- :檢視連結伺服器資訊  [測試連接配接成功]
select *
from Carveout.dbo.sdd02020  -- 多資料庫架構
go

        又如：

1.2、含多資料庫架構的全連結伺服器 

EXEC sp_addlinkedserver   '81.69.222.207' , 'SQL Server' --  （需要驗證使用者名、密碼的參數）
go
select top 10 * from [81.69.222.207].Carveout.dbo.sdf00504  --全連結架構名，隻适用于Sql Server 2005及其以上
go

        架構是形成單個命名空間的資料庫實體的集合。命名空間是一個集合，其中每個元素的名稱都是唯一的。 例如，為了避免名稱沖突，同一架構中不能有兩個同名的資料庫對象。兩個表隻有在位于不同的架構中時才可以同名，比如本例實體表sdf00504在資料庫Carveout後對應dbo，dbo是系統預設的架構名。如果你的本地裝有多個資料庫執行個體，就可以通過類似【dbo】來區分。

        添加連結伺服器後，若需修改其屬性：

  • 【配置連結伺服器屬性】： sp_serveroption為遠端伺服器RDS和連結伺服器LDS設定伺服器選項
  • 【文法】：  sp_serveroption [@server =] 'server',[@optname =] 'option_name',[@optvalue =] 'option_value'

        這樣，就可以直接來查詢遠端伺服器上資料啦！

        添加連結伺服器後，若需删除連結伺服器：

-- sp_droplinkedsrvlogin '删除連結伺服器登入名映射' [,預删除的登入的映射]
exec sp_droplinkedsrvlogin '81.69.222.207' ,NULL 
  -- 如果為 NULL，那麼将會删除由 sp_addlinkedserver 建立的預設映射 [第二個參數]
--exec sp_dropserver '81.69.222.207' --[删除遠端伺服器81.69.222.207以及所有相關的遠端登入。]
  -- ：千萬小心用：從本地 Microsoft® SQL Server™ 上的已知遠端和連結伺服器清單中删除伺服器
exec  sp_helpserver
    -- 檢視伺服器狀态的幫助資訊，包含：name,network_name,status,id,collation_name,connect_timeout,query_timeout
    -- :即：伺服器本地名[及其遠端的連結伺服器名]，網絡名，rpc協定[資料通路及使用遠端整理],伺服器的id号,整理名,連接配接逾時,查詢逾時

1.3、連結伺服器的理論與總結

sp_addlinkedserver

        建立一個連結的伺服器，使其允許對分布式的、針對 OLE DB 資料源的異類查詢進行通路。在使用 sp_addlinkedserver 建立連結的伺服器之後，此伺服器就可以執行分布式查詢。如果連結伺服器定義為 Microsoft® SQL Server™，則可執行遠端存儲過程。

                文法：

sp_addlinkedserver [ @server = ] 'server'

[ , [ @srvproduct = ] 'product_name' ]

[ , [ @provider = ] 'provider_name' ]

[ , [ @datasrc = ] 'data_source' ]

[ , [ @location = ] 'location' ]

[ , [ @provstr = ] 'provider_string' ]

[ , [ @catalog = ] 'catalog' ]

                參數：

[ @server = ] 'server'

要建立的連結伺服器的本地名稱，server 的資料類型為 sysname，沒有預設設定。

如果有多個 SQL Server 執行個體，server 可以為 servername\instancename。此連結的伺服器可能會被引用為下面示例的資料源：

SELECT *FROM    [servername\instancename.]pubs.dbo.authors.

如果未指定 data_source，則伺服器為該執行個體的實際名稱。

[ @srvproduct = ] 'product_name'

要添加為連結伺服器的 OLE DB 資料源的産品名稱。product_name 的資料類型為 nvarchar(128)，預設設定為 NULL。如果是 SQL Server，則不需要指定 provider_name、data_source、location、provider_string 以及目錄。

[ @provider = ] 'provider_name'

與此資料源相對應的 OLE DB 提供程式的唯一程式辨別符 (PROGID)。provider_name 對于安裝在目前計算機上指定的 OLE DB 提供程式必須是唯一的。provider_name 的資料類型為nvarchar(128)，預設設定為 NULL。OLE DB 提供程式應該用給定的 PROGID 在系統資料庫中注冊。

[ @datasrc = ] 'data_source'

由 OLE DB 提供程式解釋的資料源名稱。data_source 的資料類型為 nvarchar(4000)，預設設定為 NULL。data_source 被當作 DBPROP_INIT_DATASOURCE 屬性傳遞以便初始化 OLE DB 提供程式。

當連結的伺服器針對于 SQL Server OLE DB 提供程式建立時，可以按照 servername\instancename 的形式指定 data_source，它可以用來連接配接到運作于特定計算機上的 SQL Server 的特定執行個體上。servername 是運作 SQL Server 的計算機名稱，instancename 是使用者将被連接配接到的特定 SQL Server 執行個體的名稱。

[ @location = ] 'location'

OLE DB 提供程式所解釋的資料庫的位置。location 的資料類型為 nvarchar(4000)，預設設定為 NULL。location 作為 DBPROP_INIT_LOCATION 屬性傳遞以便初始化 OLE DB 提供程式。

[ @provstr = ] 'provider_string'

OLE DB 提供程式特定的連接配接字元串，它可辨別唯一的資料源。provider_string 的資料類型為 nvarchar(4000)，預設設定為 NULL。Provstr 作為 DBPROP_INIT_PROVIDERSTRING 屬性傳遞以便初始化 OLE DB 提供程式。

當針對 Server OLE DB 提供程式提供了連結伺服器後，可将 SERVER 關鍵字用作 SERVER=servername\instancename 來指定執行個體，以指定特定的 SQL Server 執行個體。servername 是 SQL Server 在其上運作的計算機名稱，instancename 是使用者連接配接到的特定的 SQL Server 執行個體名稱。

[ @catalog = ] 'catalog'

建立 OLE DB 提供程式的連接配接時所使用的目錄。catalog 的資料類型為sysname，預設設定為 NULL。catalog 作為 DBPROP_INIT_CATALOG 屬性傳遞以便初始化 OLE DB 提供程式。

                本系統存儲過程的傳回代碼值：

0（成功）或 1（失敗）

                結果集：

如果沒有指定參數，則 sp_addlinkedserver 傳回此消息：

Procedure 'sp_addlinkedserver' expects parameter '@server', which was not supplied.

使用适當 OLE DB 提供程式和參數的 sp_addlinkedserver 傳回此消息：

Server added.

                說明：

下表顯示為可通過 OLE DB 通路的資料源設定連結伺服器的方法。對于給定的資料源，可以使用多種方法為其設定連結伺服器，下表中可能有不止一行适用于一種資料源類型。下表也顯示了用于設定連結伺服器的 sp_addlinkedserver 參數值。

遠端 OLE DB 資料源

OLE DB

提供程式

 product_name provider_name data_source location provider_string catalog
SQL Server 用于 SQL Server 的 Microsoft OLE DB 提供程式 SQL Server (1)（預設值） - - - - -
SQL Server 用于 SQL Server 的 Microsoft OLE DB 提供程式 SQL Server SQLOLEDB SQL Server 的網絡名稱（用于預設執行個體） - - 資料庫名稱（可選）
SQL Server 用于 SQL Server 的 Microsoft OLE DB 提供程式 - SQLOLEDB 伺服器名\執行個體名（對于特定執行個體） - - 資料庫名稱（可選）
Oracle 用于 Oracle 的 Microsoft OLE DB 提供程式 任何 (2) MSDAORA 用于 Oracle 資料庫的 SQL*Net 别名 - - -

Access/

Jet

 用于 Jet 的 Microsoft OLE DB 提供程式 任何 Microsoft.Jet.OLEDB.4.0 Jet 資料庫檔案的完整路徑名 - - -
ODBC 資料源 用于 ODBC 的 Microsoft OLE DB 提供程式 任何 MSDASQL ODBC 資料源的系統 DSN - - -
ODBC 資料源 用于 ODBC 的 Microsoft OLE DB 提供程式 任何 MSDASQL - - ODBC 連接配接字元串 -
檔案系統 用于索引服務的 Microsoft OLE DB 提供程式 任何 MSIDXS 索引服務目錄名稱 - - -
Microsoft Excel 電子表格 用于 Jet 的 Microsoft OLE DB 提供程式 任何 Microsoft.Jet.OLEDB.4.0 Excel 檔案的完整路徑名 - Excel 5.0 -
IBM DB2 資料庫 用于 DB2 的Microsoft OLE DB 提供程式 任何 DB2OLEDB - - 請參見用于 DB2 文檔的 Microsoft OLE DB 提供程式 DB2 資料庫的目錄名

(1 ) 這種設定連結伺服器的方式強制連結伺服器的名稱與遠端 SQL Server 的網絡名稱相同。使用 server 指定伺服器。

(2 ) "任何"指産品名稱可以任意。

data_source、location、provider_string 和 catalog 參數辨別連結伺服器指向的資料庫。如果任一參數為 NULL 值，則不設定相應的 OLE DB 初始化屬性。

說明  若要在 SQL Server 6.x 版上使用 SQL Server 2000 版的 Microsoft OLE DB 提供程式，請在 6.x 版 SQL Server 上運作 \Microsoft SQL Server\Install\Instcat.sql 腳本。此腳本對于在 SQL Server 6.x 伺服器上運作分布式查詢是基本的。

在群集環境中，當指定指向 OLE DB 資料源的檔案名時，應使用通用命名規則 (UNC) 名稱或共享驅動器指定位置。

                權限：

執行許可權限預設授予 sysadmin 和 setupadmin 固定伺服器角色的成員。

                案例：

                                A. 使用用于 SQL Server 的 Microsoft OLE DB 提供程式

                                                使用用于 SQL Server 的 OLE DB 建立連結伺服器

下面的示例建立一台名為 SEATTLESales 的連結伺服器，該伺服器使用用于 SQL Server 的 Microsoft OLE DB 提供程式。

USE master
GO
EXEC sp_addlinkedserver 
    'SEATTLESales',
    N'SQL Server'
GO

                                                在 SQL Server 的執行個體上建立連結伺服器

此示例在 SQL Server 的執行個體上建立一台名為 S1_instance1 的連結伺服器，該伺服器使用 SQL Server 的 Microsoft OLE DB 提供程式。

EXEC    sp_addlinkedserver    @server='S1_instance1', @srvproduct='',
                                @provider='SQLOLEDB', @datasrc='S1\instance1'

                                B. 使用用于 Jet 的 Microsoft OLE DB 提供程式

此示例建立一台名為 SEATTLE Mktg 的連結伺服器。

說明  本示例假設已經安裝 Microsoft Access 和示例 Northwind 資料庫，且 Northwind 資料庫駐留在 C:\Msoffice\Access\Samples。

USE master
GO
-- 使用命名參數集（named parameters）:
EXEC sp_addlinkedserver 
   @server = 'SEATTLE Mktg', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO
-- 若不使用命名參數集：
USE master
GO
EXEC sp_addlinkedserver 
   'SEATTLE Mktg', 
   'OLE DB Provider for Jet',
   'Microsoft.Jet.OLEDB.4.0', 
   'C:\MSOffice\Access\Samples\Northwind.mdb'
GO

                                C. 使用用于 Oracle 的 Microsoft OLE DB 提供程式

此示例建立一台名為 LONDON Mktg 的連結伺服器，該伺服器使用用于 Oracle 的 Microsoft OLE DB 提供程式，并且假設此 Oracle 資料庫的 SQL*Net 别名為 MyServer。

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
   @server = 'LONDON Mktg',
   @srvproduct = 'Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'MyServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver 
   'LONDON Mktg', 
   'Oracle', 
   'MSDAORA',
   'MyServer'
GO

                                D. 将 data_source 參數與用于 ODBC 的 Microsoft OLE DB 提供程式一起使用

此示例建立一台名為 SEATTLE Payroll 的連結伺服器，該伺服器使用用于 ODBC 的 Microsoft OLE DB 提供程式和 data_source 參數。

說明  在執行 sp_addlinkedserver 之前，必須在伺服器上将指定的 ODBC 資料源名稱定義為系統 DSN。

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver 
   @server = 'SEATTLE Payroll', 
   @provider = 'MSDASQL', 
   @datasrc = 'LocalServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver 
   'SEATTLE Payroll', 
   '', 
   'MSDASQL',
   'LocalServer'
GO

                                E. 将 provider_string 參數與用于 ODBC 的 Microsoft OLE DB 提供程式一起使用

此示例建立一台名為 LONDON Payroll 的連結伺服器，該伺服器使用用于 ODBC 的 Microsoft OLE DB 提供程式和 provider_string 參數。

說明  有關 ODBC 連接配接字元串的更多資訊，應當搜尋 SQLDriverConnect 。

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver 
   @server = 'LONDON Payroll', 
   @provider = 'MSDASQL',
   @provstr = 'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver 
   'LONDON Payroll', 
   '', 
   'MSDASQL',
   NULL,
   NULL,
   'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'
GO

                                F. 在 Excel 電子表格上使用用于 Jet 的 Microsoft OLE DB 提供程式

若要建立使用用于 Jet 的 Microsoft OLE DB 提供程式以通路 Excel 電子表格的連結伺服器定義，請首先在 Excel 中建立一個命名的範圍以指定要在 Excel 工作表中選擇的行和列。然後，可将此範圍的名稱引用為分布式查詢中的表名稱。

EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\MyData\DistExcl.xls',
   NULL,
   'Excel 5.0'
GO

為了通路 Excel 電子表格中的資料，請将某個範圍内的單元與某個名稱相關聯。通過将範圍的名稱用作表名稱，可以通路指定的已命名範圍。下列查詢利用前面設定的連結伺服器，可通路稱為 SalesData 的命名範圍。

SELECT *
FROM EXCEL...SalesData
GO

                                G. 使用用于檢索服務的 Microsoft OLE DB 提供程式

此示例建立一台連結伺服器，并且使用 OPENQUERY 從為檢索服務啟用的連結伺服器和檔案系統中檢索資訊。

EXEC sp_addlinkedserver FileSystem,
   'Index Server',
   'MSIDXS',
   'Web'
GO
USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'yEmployees')
   DROP TABLE yEmployees
GO
CREATE TABLE yEmployees
 (
  id       int         NOT NULL,
  lname    varchar(30) NOT NULL,
  fname    varchar(30) NOT NULL,
  salary   money,
  hiredate datetime
 )
GO
INSERT yEmployees VALUES
 ( 
  10,
  'Fuller',
  'Andrew',
  $60000,
  '9/12/98'
 )
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'DistribFiles')
   DROP VIEW DistribFiles
GO
CREATE VIEW DistribFiles 
 AS
 SELECT *
 FROM OPENQUERY(FileSystem,
                 'SELECT Directory, 
                    FileName,
                    DocAuthor,
                    Size,
                    Create,
                    Write
                  FROM SCOPE('' "c:\My Documents" '')
                  WHERE CONTAINS(''Distributed'') > 0 
                    AND FileName LIKE ''%.doc%'' ')
 WHERE DATEPART(yy, Write) = 1998
GO
SELECT * 
FROM DistribFiles
GO
SELECT Directory,
  FileName, 
  DocAuthor, 
  hiredate
FROM DistribFiles D, yEmployees E
WHERE D.DocAuthor = E.FName + ' ' + E.LName
GO

                                H. 使用用于 Jet 的 Microsoft OLE DB 提供程式通路文本檔案

此示例建立一台直接通路文本檔案的連結伺服器，而沒有将這些檔案連結為 Access .mdb 檔案中的表。提供程式是 Microsoft.Jet.OLEDB.4.0，提供程式字元串為"Text"。

資料源是包含文本檔案的目錄的完整路徑名。schema.ini 檔案（描述文本檔案的結構）必須與此文本檔案存在于相同的目錄中。有關建立 schema.ini 檔案的更多資訊，請參見 Jet 資料庫引擎文檔。

--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 
   'Microsoft.Jet.OLEDB.4.0',
   'c:\data\distqry',
   NULL,
   'Text'
GO

--Set up login mappings
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL
GO

--List the tables in the linked server
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--using a 4-part name 
SELECT * 
FROM txtsrv...[file1#txt]

                                I. 使用用于 DB2 的 Microsoft OLE DB 提供程式

下面的示例建立一台名為 DB2 的連結伺服器，該伺服器使用用于 DB2 的 Microsoft OLE DB 提供程式。

EXEC sp_addlinkedserver
   @server='DB2',
   @srvproduct='Microsoft OLE DB Provider for DB2',
   @catalog='DB2',
   @provider='DB2OLEDB',
   @provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=XYZ;Network Port=50000;Package Collection=admin;Default Schema=admin;'

二、分布式資料查詢

2.1、開放資料源

                2.1.1、OpenDataSource函數

                                2.1.1.1、使用ACE.OLEDB資料引擎

-- ACE.OLEDB若用于Excel導入導出，适用于所有Excel版本，但需要使用不同的擴充屬性參數： 

-- ACE.OLEDB若用于Excel導入導出，适用于所有Excel版本，但需要使用不同的擴充屬性參數：

--Excel2003以上版本：
insert into OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\開發包\飼料分批次進銷存-存貨計價和條碼\A1.xlsx;Extended Properties=Excel 12.0 Xml;Persist Security Info=False'
)...[A1$](公司編碼,行種子,産品編碼,銷量下限,銷量上限,現場折扣,其它扣率,備注)
select com_id,seeds_id,item_id,sd_oq_LowerLimit,sd_oq_UpperLimit,send_sum,send_sum_other,c_memo 
from Siliao_jxc_BarcodeStock.dbo.Ctl03001_DiscountTemp
go

--Excel2003及其以下：
insert into OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\開發包\飼料分批次進銷存-存貨計價和條碼\A1.xls;Extended Properties=Excel 8.0;Persist Security Info=False'
)...[A1$](公司編碼,行種子,産品編碼,銷量下限,銷量上限,現場折扣,其它扣率,備注)
select com_id,seeds_id,item_id,sd_oq_LowerLimit,sd_oq_UpperLimit,send_sum,send_sum_other,c_memo 
from Siliao_jxc_BarcodeStock.dbo.Ctl03001_DiscountTemp
go

insert into OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\開發包\飼料分批次進銷存-存貨計價和條碼\Book1.xlsx;Extended Properties=Excel 12.0 Xml;Persist Security Info=False'
)...[Sheet1$](公司編碼,行種子,産品編碼,銷量下限,銷量上限,現場折扣,其它扣率,備注)
select com_id,seeds_id,item_id,sd_oq_LowerLimit,sd_oq_UpperLimit,send_sum,send_sum_other,c_memo 
from Siliao_jxc_BarcodeStock.dbo.Ctl03001_DiscountTemp
go

SELECT *
FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0',
    'Data Source=D:\開發包\飼料分批次進銷存-存貨計價和條碼\A1.xlsx;Extended Properties=Excel 12.0 Xml;Persist Security Info=False'
)...[A1$] 
go

SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',--SQLOLEDB
         'Data Source=119.29.5.138;User ID=sa;Password='
         ).Carveout.dbo.View_sdd02020  --View_sdd02020  sdd02021
go
SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',--SQLOLEDB :慢、效率低
         'Data Source=81.69.222.207;User ID=sa;Password='
         ).Carveout.dbo.View_sdd02020  --View_sdd02020  sdd02021
go

                                2.1.1.2、使用Jet.OLEDB資料引擎

        -- Jet.OLEDB若用于Excel導入導出，隻适用于Excel 2003及其以下版本：  

-- Jet.OLEDB若用于Excel導入導出，隻适用于Excel 2003及其以下版本：

SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="D:\開發包\飼料分批次進銷存-存貨計價和條碼\A1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0'
)...[A1$] 
go

2.2、開放XML

                2.2.1、使用 OPENXML 編寫 XML

OPENXML 是在記憶體中的 XML 文檔上提供行集的 Transact-SQL 關鍵字。OPENXML 是與表或視圖相似的行集提供程式。OPENXML 通過提供 XML 文檔内部表示法的行集視圖，允許通路 XML 資料，就好象它是關系行集一樣。行集中的記錄可以存儲在資料庫表中（類似于表和視圖所提供的行集）。

無論行集提供程式（如表、視圖或 OPENROWSET）可以在何處作為源出現，都可以在 SELECT 和 SELECT INTO 語句中使用 OPENXML。有關 OPENXML 文法的資訊，請參見 OPENXML。

若要使用 OPENXML 編寫對 XML 文檔的查詢，首先必須調用 sp_xml_preparedocument，以分析 XML 文檔并傳回準備使用的已分析文檔的句柄。已分析文檔是 XML 文檔中各種節點（元素、特性、文本、注釋等等）的樹型表示法。文檔句柄傳遞到 OPENXML，然後 OPENXML 根據傳遞給它的參數提供文檔的行集視圖。

必須通過調用 sp_xml_removedocument 系統存儲過程釋放記憶體，以将 XML 文檔的内部表示法從記憶體中删除。

下圖說明了此過程。

說明  若要了解 OPENXML，需要熟悉 XPath 查詢并了解 XML。有關 Microsoft® SQL Server™ 2000 XPath 支援的更多資訊，請參見使用 XPath 查詢。

OPENXML 參數

OPENXML 的參數包括：

  • XML 文檔句柄 (idoc)
  • 辨別要映射到行的節點的 XPath 表達式 (rowpattern)
  • 對要生成的行集的描述
  • 行集列和 XML 節點之間的映射

XML 文檔句柄 (idoc)

sp_xml_preparedocument存儲過程傳回此文檔句柄。

XPath 表達式辨別将要處理的節點 (rowpattern)

指定為 rowpattern 的 XPath 表達式辨別 XML 文檔中的節點集。rowpattern 辨別的每個節點對應于 OPENXML 所生成的行集中的一行。

XPath 表達式辨別的節點可以是 XML 文檔中的任何 XML 節點（元素、特性、處理說明等）。如果 rowpattern 辨別 XML 文檔中的元素集，則所辨別的每個元素節點在行集中都占一行。例如，如果 rowpattern 以特性結束，則将為 rowpattern 標明的每個特性節點建立一行。

描述将要生成的行集

必須向 OPENXML 提供行集架構以生成行集。可以使用可選的 WITH 子句指定行集架構。下列選項可用于指定行集架構：

  • 在 WITH 子句中指定完整的架構。

    在指定行集架構時，可指定列名和它們的資料類型，以及它們到 XML 文檔的映射。

    可以指定列模式（使用 SchemaDeclaration 中的 ColPattern 參數）。指定的列模式用于将行集列映射到 rowpattern 所辨別的 XML 節點并确定映射類型。

    如果沒有為列指定 ColPattern，則行集列根據 flags 參數所指定的映射而映射到具有相同名稱的 XML 節點。但是如果在 WITH 子句中将 ColPattern 指定為架構描述的一部分，則重寫用 flags 參數指定的映射。

  • 在 WITH 子句中指定現有表名。

    可以很容易地指定現有的表名，而 OPENXML 可以使用該表的架構生成行集。

  • 不指定 WITH 子句。

    在這種情況下，OPENXML 以所謂的邊緣表格式傳回行集。稱其為邊緣表是因為采用這種表格式時，已分析的 XML 文檔樹中的每個邊緣分别映射到行集中的一行。

    邊緣表在單個表中表示 XML 文檔的細密結構（例如，元素/特性名、文檔層次結構、命名空間、處理說明等）。邊緣表格式使您得以獲得無法通過元屬性表現的附加資訊。有關元屬性的更多資訊，請參見在 OPENXML 中指定元屬性。

    邊緣表提供的附加資訊使您得以存儲/查詢元素/特性的資料類型、節點類型（元素節點、特性節點或值節點），存儲/查詢有關 XML 文檔結構的資訊，并有可能生成 XML 文檔管理系統。

    邊緣表使您得以編寫存儲過程，以将 XML 文檔視為 BLOB 輸入、生成邊緣表，然後在最精細的級别上析取并分析文檔（查找文檔層次結構、元素/特性名、命名空間、處理說明等）。

    當映射到其它關系格式沒有意義且 ntext 字段沒有提供足夠的結構資訊時，邊緣表還可以用作 XML 文檔的存儲格式。

    無論何時隻要想以其它方式使用 XML 文法分析器檢查 XML 文檔，都可以使用邊緣表獲得相同的資訊。

    下表描述邊緣表的結構。

    列名 資料類型 描述
    id Bigint

    是文檔節點的唯一 ID。

    根元素的 ID 值為 0。保留負 ID 值。
    parentid Bigint

    辨別節點的父節點。此 ID 所辨別的父節點不一定是父元素，而是取決于此 ID 所辨別節點的子節點的 NodeType。例如，如果節點是文本節點，則其父節點可能是特性節點。

    如果節點位于 XML 文檔的頂層，則其 ParentID 為 NULL。
    nodetype Int

    辨別節點類型。是對應于 XML DOM 節點類型編号的整數（有關節點資訊，請參見 DOM）。

    某些節點類型為：

    1 = 元素節點

    2 = 特性節點

    3 = 文本節點
    localname nvarchar 給出元素或特性的本地名稱。如果 DOM 對象沒有名稱則為 NULL。
    prefix nvarchar 是節點名稱的命名空間字首。
    namespaceuri nvarchar 是節點的命名空間 URI。如果值是 NULL，則命名空間不存在。
    datatype nvarchar 是元素或特性行的實際資料類型，否則是 NULL。從内嵌 DTD 中或從内嵌架構中推斷資料類型。
    prev Bigint 是前一個兄弟元素的 XML ID。如果前面沒有兄弟元素則為 NULL。
    text Ntext 包含文本格式的特性值或元素内容（如果邊緣表項不需要值則為 NULL）。

行集列和 XML 節點之間的映射

在 OPENXML 語句中，可以選擇指定行集列和 rowpattern 所辨別的 XML 節點之間的映射類型（attribute-centric、element-centric）。此資訊用于 XML 節點和行集列的互相轉換。

有兩種方法指定映射（可以同時指定兩者）：

  • 使用 flags 參數。

    由 flags 參數所指定的映射采用名稱對應，即 XML 節點映射到同名的對應行集列。

  • 使用 ColPattern 參數。

    ColPattern 是 XPath 表達式，被指定為 WITH 子句中的 SchemaDeclaration 的一部分。在 ColPattern 中指定的映射重寫 flags 參數所指定的映射。

    ColPattern 可用于指定映射的特殊性質（當映射為 attribute-centric 和 element-centric 時），以重寫或增強 flags 所指定的預設映射。

    在下列情況下指定 ColPattern：

    • 行集中的列名不同于它所映射到的元素/特性名。在這種情況下，ColPattern 用于識别行集列映射到的 XML 元素/特性名。
    • 希望将元屬性特性映射到列。在這種情況下，ColPattern 用于辨別行集列映射到的元屬性。有關使用元屬性的更多資訊，請參見在 OPENXML 中指定元屬性。

flags 和 ColPattern 參數都是可選的。如果沒有指定映射，則預設情況下使用 attribute-centric 映射（flags 參數的預設值）。

Attribute-centric（以特性為中心的）映射

如果将 OPENXML 中的 flags 參數設定為特性，則特性基于名稱對應映射到行集中的列。名稱對應表示每個給定名稱的 XML 特性都以相同的名稱存儲在行集中的列内。

如果列名不同于它所映射到的特性名，則必須指定 ColPattern。

如果 XML 特性具有命名空間限定符，則行集中的列名也必須有此限定符。

Element-centric（以元素為中心的）映射

将 OPENXML 中的 flags 參數設定為 2 (XML_ELEMENTS) 将指定 element-centric 映射。除了下列差異外，它與 attribute-centric 映射相似：

  • 除非指定列級模式，否則映射的名稱對應（例如，映射到同名 XML 元素的列）選擇不複雜的子元素。在檢索的情況下，如果子元素是複雜的（包含深一層的子元素），則将列設定為 NULL。忽視子元素的特性值。
  • 具有相同名稱的多個子元素将以檢索順序互相重寫。在名稱相同的情況下，聯接父元素将追加子元素。

                                2.2.1.1、使用sp_xml_preparedocument 

sp_xml_preparedocument
讀取作為輸入提供的可擴充标記語言 (XML) 文本，然後使用 MSXML 文法分析器 (Msxml2.dll) 對其進行分析，并提供分析後的文檔供您使用。分析後的文檔是 XML 文檔中各節點（元素、特性、文本、注釋等）的樹型表示法。 

sp_xml_preparedocument 傳回一個句柄，可用于通路 XML 文檔的新建立的内部表示方式。該句柄在連接配接到 Microsoft® SQL Server™ 2000 期間保持有效，直到重置連接配接或執行 sp_xml_removedocument 使句柄無效為止。



說明  分析過的文檔存儲在 SQL Server 2000 的内部高速緩存中。MSXML 文法分析器使用 SQL Server 可用總記憶體的八分之一。若要避免記憶體不足，請運作 sp_xml_removedocument 以釋放記憶體。


文法
sp_xml_preparedocument hdoc OUTPUT
[, xmltext]
[, xpath_namespaces]

參數
hdoc

是新建立的文檔的句柄。hdoc 的資料類型為 integer。

[xmltext]

是原 XML 文檔。MSXML 文法分析器分析該 XML 文檔。xmltext 是 text 類型（char、nchar、varchar、nvarchar、text 或 ntext）的參數。預設值是 NULL，在這種情況下，将建立空 XML 文檔的内部表示法。

[xpath_namespaces]

指定 OPENXML 的行和列 XPath 表達式中所使用的命名空間聲明。預設值是 <root xmlns:mp="urn:schemas-microsoft-com:xml-metaprop">。
xpath_namespaces 通過符合文法規則的 XML 文檔的方式，為在 OPENXML 的 Xpath 表達式中使用的字首提供命名空間 URI。xpath_namespaces 聲明字首必須用于引用命名空間 urn:schemas-microsoft-com:xml-metaprop，該命名空間提供有關分析後的 XML 元素的中繼資料。盡管可以使用此方法為元屬性命名空間重新定義命名空間字首，但此命名空間不會丢失。此字首 mp 對 urn:schemas-microsoft-com:xml-metaprop 仍有效，即使 xpath_namespaces 不包含此類聲明。xpath_namespaces 是 text 類型（char、nchar、varchar、nvarchar、text 或 ntext）的參數。 

傳回代碼值
0（成功）或 >0（失敗）

權限
執行權限預設授予 public 角色。

示例
A. 為符合文法規則的 XML 文檔準備内部表示方式
下例傳回作為輸入提供的新建立的 XML 文檔内部表示法的句柄。在對 sp_xml_preparedocument 的調用中，使用了預設命名空間字首映射。

DECLARE @hdoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
-- Remove the internal representation.
exec sp_xml_removedocument @hdoc

B. 為帶 DTD 的符合文法規則的 XML 文檔準備内部表示方式
下例傳回作為輸入提供的新建立的 XML 文檔内部表示法的句柄。存儲過程根據文檔中包含的 DTD 來驗證裝載的文檔。在對 sp_xml_preparedocument 的調用中，使用了預設命名空間字首映射。

DECLARE @hdoc int
DECLARE @doc varchar(2000)
SET @doc = '
<?xml version="1.0" encoding="UTF-8" ?> 
<!DOCTYPE root 
[<!ELEMENT root (Customers)*>
<!ELEMENT Customers EMPTY>
<!ATTLIST Customers CustomerID CDATA #IMPLIED ContactName CDATA #IMPLIED>]>
<root>
<Customers CustomerID="ALFKI" ContactName="Maria Anders"/>
</root>'

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

C. 指定命名空間 URI
下例傳回作為輸入提供的新建立的 XML 文檔内部表示法的句柄。在對 sp_xml_preparedocument 的調用中，保留了元屬性命名空間映射的 mp 字首，并将 xyz 映射字首添加到了命名空間 urn:MyNamespace。

DECLARE @hdoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" 
           OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" 
           OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc, '<root xmlns:xyz="run:MyNamespace"/>'

                                2.2.1.2、使用sp_xml_removedocument 

sp_xml_removedocument
删除文檔句柄指定的 XML 文檔的内部表示方式并使該文檔句柄無效。


說明  分析過的文檔儲存在 Microsoft® SQL Server™ 2000 的内部高速緩存中。MSXML 文法分析器使用 SQL Server 可用總記憶體的八分之一。若要避免記憶體不足，請運作 sp_xml_removedocument 以釋放記憶體。

文法
sp_xml_removedocument hdoc

參數
hdoc

是新建立的文檔的句柄。無效句柄傳回錯誤。hdoc 的資料類型為 integer。

傳回代碼值
0（成功）或 >0（失敗）

權限
執行權限預設授予 public 角色。

示例
A. 删除 XML 文檔
下例删除 XML 文檔的内部表示法。該文檔的句柄作為輸入提供。

EXEC sp_xml_removedocument @hdoc

                2.2.2、使用 OPENXML 

OPENXML
OPENXML 通過 XML 文檔提供行集視圖。由于OPENXML 是行集提供程式，是以可在會出現行集提供程式（如表、視圖或 OPENROWSET 函數）的 Transact-SQL 語句中使用 OPENXML。

文法
OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]]) 
[WITH (SchemaDeclaration | TableName)]

參數
idoc

是 XML 文檔的内部表式法的文檔句柄。通過調用 sp_xml_preparedocument 建立 XML 文檔的内部表式法。

rowpattern

是 XPath 模式，用來辨別要作為行處理的節點（這些節點在 XML 文檔中，該文檔的句柄由 idoc 參數傳遞）。

flags

表示應在 XML 資料和關系行集間使用映射以及應如何填充溢出列。flag 為可選輸入參數，可以是下列值之一。
位元組值 描述
預設為以特性為中心的映射。
1

使用以特性為中心的映射。

在某些情況下，可以将它與 XML_ELEMENTS 組合使用。使用時首先應用以特性為中心的映射，然後對于所有仍未處理的列應用以元素為中心的映射。
2

使用以元素為中心的映射。

在某些情況下，可以将它與 XML_ATTRIBUTES 組合使用。使用時先應用以特性為中心的映射，然後對于所有仍未處理的列應用以元素為中心的映射。
8

可與 XML_ATTRIBUTES 或 XML_ELEMENTS 組合使用（邏輯 OR）。

在檢索的上下文中，該标志指明不應将已消耗的資料複制到溢出屬性 @mp:xmltext。

 
SchemaDeclaration

是窗體的架構定義：
ColName ColType [ColPattern | MetaProperty][, ColName ColType [ColPattern | MetaProperty]...] 

ColName 
是行集中的列名。

ColType 
是行集中列的 SQL 資料類型。如果列類型不同于特性的基礎 XML 資料類型，那麼将發生類型壓制。如果列的類型是 TIMESTAMP，則當從 OPENXML 行集中進行選擇時，将忽略 XML 文檔中現有的值并傳回自動填充值。

ColPattern 
是可選的通用 XPath 模式，它說明應如何将 XML 節點映射到列。如果沒有指定 ColPattern，那麼發生預設的映射（由 flags 指定的以特性為中心或以元素為中心的映射）。 
指定為 ColPattern 的 XPath 模式用于指定特殊的映射性質（如果發生以特性為中心和以元素為中心的映射），這些特殊的映射性質可以重寫或增強由标志所指定的預設映射。 

指定為 ColPattern 的通用 XPath 模式也支援元屬性。 


MetaProperty 
是由 OPENXML 提供的元屬性之一。如果指定元屬性，則該列包含元屬性提供的資訊。這些元屬性使您得以析取 XML 節點的資訊（如相對位置、命名空間資訊），以提供比文本化表示法更詳細的資訊。 
TableName

如果具有期望架構的表已經存在且不要求列模式，則為給定的表名（而不是 SchemaDeclaration）。

通過使用 SchemaDeclaration 或者指定一個現有的 TableName，WITH 子句提供一種行集格式（根據需要還提供其它映射資訊）。如果沒有指定可選的 WITH 子句，那麼以 edge table 格式傳回結果。邊緣表在單個表中表示 XML 文檔的細密結構（例如，元素/特性名、文檔層次結構、命名空間、PI 等）。

下表描述邊緣表的結構：

下表描述邊緣表的結構：

列名 資料類型 描述
id bigint

是文檔節點的唯一 ID。

根元素的 ID 值為 0。保留負 ID 值。
parentid bigint

辨別節點的父節點。此 ID 所辨別的父節點不一定是父元素，而是取決于此 ID 所辨別節點的子節點的 NodeType。例如，如果節點是文本節點，則其父節點可能是特性節點。

如果節點位于 XML 文檔的頂層，則其 ParentID 為 NULL。
nodetype int

辨別節點類型。是對應于 XML DOM 節點類型編号的整數（有關節點資訊，請參見 DOM）。

三種節點類型是：

1 = 元素節點

2 = 特性節點

3 = 文本節點
localname nvarchar 給出元素或特性的本地名稱。如果 DOM 對象沒有名稱則為 NULL。
prefix nvarchar 是節點名稱的命名空間字首。
namespaceuri nvarchar 是節點的命名空間 URI。如果值是 NULL，則命名空間不存在。
datatype nvarchar 是元素或特性行的實際資料類型，否則是 NULL。從内嵌 DTD 中或從内嵌架構中推斷資料類型。
prev bigint 是前一個兄弟元素的 XML ID。如果前面沒有兄弟元素則為 NULL。
text ntext 包含文本格式的特性值或元素内容（如果邊緣表項不需要值則為 NULL）。

                2.2.3、示例

                                A.使用帶有 OPENXML 的 SELECT 語句。

下面的示例使用 sp_xml_preparedocument 建立 XML 圖像的内部表示。然後對 XML 文檔的内部表示法執行使用 OPENXML 行集提供程式的 SELECT 語句。

flag 值設定為 1，表示以特性為中心的映射。是以，XML 特性映射到行集中的列。指定為 /ROOT/Customers 的 rowpattern 辨別要處理的 <Customers> 節點。

沒有指定可選的 colpattern（列模式），因為列名和 XML 特性名稱比對。

OPENXML 行集提供程式建立了一個雙列行集（CustomerID 和 ContactName），SELECT 語句從該行集中檢索必要的列（在本例中檢索所有的列）。

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

下面是結果集：

CustomerID ContactName          
---------- -------------------- 
VINET      Paul Henriot
LILAS      Carlos Gonzlez
如果将 flags 設定為 2（表示以元素為中心的映射）并執行相同的 SELECT 語句，由于 <Customers> 元素沒有任何子元素，則對于 XML 文檔中兩個 Customer 的 CustomerID 和 ContactName 的值都作為 NULL 傳回。

下面是結果集：

CustomerID ContactName
---------- -----------
NULL       NULL
NULL       NULL

                                B. 為列和 XML 特性之間的映射指定 ColPattern

下面的查詢從 XML 文檔傳回客戶 ID、訂單日期、産品 ID 和數量等特性。rowpattern 辨別 <OrderDetail> 元素。ProductID 和 Quantity 是 <OrderDetails> 元素的特性。而 CustomerID 和 OrderDate 是父元素 (<Orders>) 的特性。

指定可選的 ColPattern，表示：

  • 行集中的 OrderID、CustomerID 和 OrderDate 列映射到 XML 文檔中 rowpattern 所辨別節點的父節點的特性。
  • 行集中的 ProdID 列映射到 ProductID 特性，而行集中的 Qty 列映射到 rowpattern 所辨別節點的 Quantity 特性。

盡管以元素為中心的映射由 flag 參數指定，但 ColPattern 中指定的映射重寫該映射。

declare @idoc int
declare @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">
   <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')

結果如下：

OrderID CustomerID           OrderDate                 ProdID    Qty

------------------------------------------------------------------------

10248      VINET       1996-07-04 00:00:00.000   11      12
10248      VINET       1996-07-04 00:00:00.000   42      10
10283      LILAS       1996-08-16 00:00:00.000   72      3

                                C. 獲得邊緣表格式的結果

在下例中，在 OPENXML 語句中未指定 WITH 子句。是以，OPENXML 所生成的行集具有邊緣表格式。SELECT 語句傳回邊緣表中的所有列。

下例中的示例 XML 文檔由 <Customer>、<Order> 和 <Order_0020_Details> 元素組成。

首先調用 sp_xml_preparedocument 以獲得文檔句柄。此文檔句柄傳遞到 OPENXML。

在 OPENXML 語句中

  • rowpattern (/ROOT/Customer) 辨別要處理的 <Customer> 節點。
  • 未提供 WITH 子句。是以 OPENXML 以邊緣表格式傳回行集。

最後 SELECT 語句檢索邊緣表中的所有列。

declare @idoc int
declare @doc varchar(1000)
set @doc ='
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
   <Orders CustomerID="VINET" EmployeeID="5" OrderDate=
           "1996-07-04T00:00:00">
      <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
      <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
   </Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
           "1996-08-16T00:00:00">
      <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
   </Orders>
</Customers>
</ROOT>'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT statement using OPENXML rowset provider
SELECT    *
FROM       OPENXML (@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @idoc

傳回結果：邊緣表。

三、依賴性-運作“連結伺服器”和“分布式查詢”有依賴性嗎？

3.1、“連結伺服器”和“分布式查詢”互相之間并無直接的依賴性

                3.1.1、“連結伺服器”和“分布式查詢”，各自獨立，兩者之間無必須的依賴性

                3.1.2、“分布式查詢”當涉及到資料源無法直接通路時：才需要先建立和調用“連結伺服器”

3.2、“連結伺服器”自身的依賴性

                3.2.1、MSDTC

                                3.2.1.1、MSDTC  : Distributed Transaction Coordinator （微軟分布式傳輸協調器）

                                3.2.1.2、MSDTC指向C:\Windows\System32\msdtc.exe

delphi使用MS Sql Server資料庫的分布式查詢delphi使用MS Sql Server資料庫的分布式查詢
delphi使用MS Sql Server資料庫的分布式查詢delphi使用MS Sql Server資料庫的分布式查詢
delphi使用MS Sql Server資料庫的分布式查詢delphi使用MS Sql Server資料庫的分布式查詢
delphi使用MS Sql Server資料庫的分布式查詢delphi使用MS Sql Server資料庫的分布式查詢
delphi使用MS Sql Server資料庫的分布式查詢delphi使用MS Sql Server資料庫的分布式查詢
delphi使用MS Sql Server資料庫的分布式查詢delphi使用MS Sql Server資料庫的分布式查詢
delphi使用MS Sql Server資料庫的分布式查詢delphi使用MS Sql Server資料庫的分布式查詢

         如上圖，這下你應該知曉了。經常遇到提示你，類似：“iSAM”無效或不存在的真正的關鍵所在了吧？

                                         3.2.1.3、MSDTC的易懂解釋：

         https://baike.baidu.com/item/msdtc/445435?fr=aladdin 

         https://support.microsoft.com/zh-cn/Search/results?query=MSDTC

                                         3.2.1.4、如何在 Windows Server 2012/2012R2 中配置 MSDTC，令其使用特定端口： 

         https://zhidao.baidu.com/question/617491654505541932.html

                                         3.2.1.5、啟停MSDTC         

         開始->運作->cmd啟動指令行（其實在Delphi等進階開發工具中，都可以直接用代碼來調用的，不僅僅隻限于下述這些指令行）：

         直接啟動服務： net start msdtc

         如果啟動失敗的話按下列步驟執行:

                在指令行輸入services.msc  ，

                打開服務答管理器：找到Distributed Transaction Coordinator服務，并設定為手動，再啟動服務。

         再不行的話在指令行中輸入：

                  net stop msdtc

                  msdtc -uninstall

                  msdtc -install

                  net start msdtc

                                         3.2.1.6、MSDTC配置      

         Windows 7,Windows Server 2008 MSDTC配置：

                  DComCnfg.exe  (指令行執行元件服務) :

delphi使用MS Sql Server資料庫的分布式查詢delphi使用MS Sql Server資料庫的分布式查詢

         Windows XP和Windows Server 2003下MSDTC的配置：

delphi使用MS Sql Server資料庫的分布式查詢delphi使用MS Sql Server資料庫的分布式查詢

          唉，從事軟體開發工作，不懂作業系統，那是硬傷呀，隻能跟在開發工具屁股後面“冥思苦想”，殊不知直接調用作業系統如此簡單，無論MSWindows、Android還是iOS。

3.3、“分布式查詢”的依賴性

                3.3.1、OpenDataSource函數

                3.3.2、OpenRowSet函數

                3.3.3、OpenQuery函數

                3.3.4、OpenXML函數

                3.3.5、如果在對上述函數引用的内部參數中，需要指向無法直接通路的資料源或資料庫對象，則需要連結伺服器來配合調用。

delphi使用MS Sql Server資料庫的分布式查詢delphi使用MS Sql Server資料庫的分布式查詢
delphi使用MS Sql Server資料庫的分布式查詢delphi使用MS Sql Server資料庫的分布式查詢
delphi使用MS Sql Server資料庫的分布式查詢delphi使用MS Sql Server資料庫的分布式查詢

                 3.3.6、如果你的應用依賴的Sql Server在用戶端一體，對安全性有特别要求，請使用高版本的MS Sql Server，它們和作業系統一起都内置了強密碼的安全政策。

         哪怕你是作業系統的超級管理者賬戶登入和Sql Server的管理者，也無權複制、查閱建立者設定Sql Server的使用者密碼，除非你删除此使用者，而删除此使用者，App也就不能再使用了。

本部落格相關：

        1、《delphi導入導出Excel與Sql Server》

       2、《delphi中TEdgeBrowser控件用法之一》

喜歡的話，就在下面點個贊、收藏就好了，友善看下次的分享：

資料庫 delphi ms sql server 分布式查詢 openxml OpenDataSource
上一篇: C# 把DataTable資料寫入Excel檔案的方法
下一篇: Day01-JavaWeb【Tomcat伺服器&amp;Servlet入門】相同的軟體環境

繼續閱讀